Heirarchical column sorting

[fishkake]

Me again!

I'm trying to create a view which displays categories, firstly by company name, and then by the type of entity. This is for a Vendors database, where I have the entry types "Vendor Head Office", "Vendor Sales Office" and "Distributor".

My problem is that I want only one level of categorization for the head office, but two levels for the others, to get a view like this:

>CompanyName
Head Office
>Vendor Sales Offices
SalesOffice1
SalesOffice2
>Distributors
Distributor1
Distributor2
>CompanyName
Head Office
>Vendor Sales Offices
SalesOffice1
SalesOffice2
SalesOffice3
>Distributors
...

I have accomplished this multi-level categorization by setting a column with the following formula:

@If(AccountType="Vendor Sales Office":"Distributor"; fdCompanyHeadOffice+"\\"+AccountType;CompanyName)

The "\\" creates a new category, which is perfect. My problem is that we need the top category (the company name) to be sorted alphabetically, but in the heirarchy "Vendor Sales Offices" should appear ABOVE "Distributors", which goes against the alphabet constraint!

I have tried all manner of hidden columns etc, but they won't work because this column is created by the formula, and is not programmable. By CREATING this column seperately, I lose the two-level heirarchy!

This is a pain, and I don't know if its even possible to do what I'm asking, but any help would be appreciated. Sorry for the long post!

 

[pmonett]

I would suggest a first hidden column sorting by company name.
Then a 2nd hidden column sorting by type of entry.
The 3rd column is to be displayed, and should categorize the docs by company name.
Then, in your 4th column, you can categorize documents by their type.
You'll get a category for the Head Office, but that shouldn't really bother anyone - besides, you don't have any choice in the matter ;-).

So, first column by Company Name, second column with a formula like :

@if(AccountType="Head Office";1;AccountType="Vendor Sales Offices";2;3)

The 3rd column sorts by Company Name - categorized this time, and the 4th column categorizes by Account Type.

Once you have got this working, you can try categorizing the first column and removing the hidden attribute, and deleting the 3rd column. It may still work.

Pascal.

 

[fishkake]

You'll get a category for the Head Office, but that shouldn't really bother anyone - besides, you don't have any choice in the matter "

This was my concern - we can't have a seperate category for the head office (management have a fear of too many mouse-clicks) - thats why we used the "//" method to create a new level!

Is there a way to use the "//" string to create a HIDDEN column? If I could do that then I think I could work out the rest. Thanks Pascal - I think you've answered about 10 of my questins on here!!

 

[pmonett]

Sorry, but no dice. You cannot have a column not categorizing an element.
As for the "not too many mouse clicks", I would think that people will be clicking a lot more on vendor stuff than head office stuff - but I'm not a manager ;-).

As for hiding stuff, you cannot do that in a view outside of column properties.

Good luck !

Pascal.

 

[fishkake]

Yeah, I thought what I wanted was impossible, but its nice to have confirmation not to waste my time trying!!

I have actually accomplished what I wanted now, although its not ideal - I wrote a formula that would replace the account type "Vendor Sales Office" with " Vendor Sales Office" (notice the space). Now the alphabetical sorting puts it in the correct order.

And as for the management decision - I'm a trained monkey, and I do what I'm told. If management don't know what they want, its not my problem!!

 

[pmonett]

Tell your management that there is one solution : they can sign up for Premium Partnership and lobby Lotus to get that implemented.

Who knows ? Maybe in five or ten years it could happen ;-).

Have fun !

Pascal.