Subject: RE: Creative Notes Views?
You can only sort documents in a Notes view based on:Information in that document, or
Its hierarchical relationship with a parent document.
A parent-response relationship will not work with Agencies being responses to Companies because Agencies work with multiple companies.
A parent-response relationship will not help you between Agencies and Contacts because, even if there’s just one Agency per Contact, the same set of responses will display for each occurrence of the same document in the view. I.e. if Agency 1 appears listed under Company 1, and later under Company 2, the same set of responses will appear hierarchically below Agency 1 in both cases.
So you’re reduced to using information stored in the document, displayed without response relationships (the response relationships may exist but they wouldn’t be used in this view).
That means that each document must contain complete information about its position in the hierarchy – a contact must have a field value that lists its company and agency (if any), and an Agency must list all its Companies. Since you need varying levels of depth in a hierarchy, with documents at each level, your best bet is to use a category column that contains "" characters to create an “ad hoc” multilevel hierarchy.
For instance, the Company document would just use the company name as its category value.
An Agency document, assuming it contained a list of the companies they worked with in a field called Companies, might use this as its category value:
Companies + “\Agencies\” + AgencyName
If there are multiple Companies values, the agency will appear in the view multiple times, once under each company.
A contact record, which contains a value in the Company field and may or may not contain a value in the AgencyName field, might use this as its formula:
Company + @If(AgencyName = “”; “”; “\Agencies\” + AgencyName) + “\Contacts”
This might not exactly match what you’d like to see in the view; for instance, there are two lines for the agency: a category heading and a document row. You probably would prefer it to work more like a response document, where the same row can be clicked to get the document or expanded to see what comes under the document. Also, the indentation of the document rows doesn’t change based on the number of levels deep it is in categories.
If this just won’t work for you, you might consider making an artificial response hierarchy by inserting an extra record type that represents an agency’s relationship with a company. This could be a response to the company document, and contacts from that company belonging to that agency would be responses to it. Then use a view with responses displayed hierarchically, and you’re set to go. Of course, this will take some reasonably serious coding behind the scenes to establish and maintain this extra reference data – but you were going to have to do somehting like that anyway – what happens if the company name changes, after all? Also, form event code will be needed to switch the user to the Agency document when they open an AgencyCompany document.