Multiple Nested Case

[Ron Cates]

Wondering if I could get a little help with this one. I have a portal sorting solution that uses calculated sort fields. That works fine. I also have an indicator on each column header to indicate the current sort and direction. I have just added the ability to shift click a second column header to sort by a second column. Again this all works fine. Where I am stuck is unravelling the nested case statement in the indicator field to be able to indicate the second sort order. Currently I have

Case (

xsort_key = "ticket_status";

Case(xsort_order="0" ; "▲";

Case (xsort_key = "ticket_status";

Case(xsort_order="1" ; "▼";""))))

What I would like to add is another series of case statements for the second set of sort fields which on thier own would be:

Case (

xsort2_key = "ticket_status";

Case(xsort2_order="0" ; "▲";

Case (xsort2_key = "ticket_status";

Case(xsort2_order="1" ; "▼";""))))

I'm hoping to combine the two sets of case statements and in the second set I would also like to change the arrow red. The result will be a black arrow indicating the first sort column and a red arrow indicating the second sort column.

Appreciate any help

Thanks

Ron

[comment]

Currently I have

Case (

xsort_key = "ticket_status";

Case(xsort_order="0" ; "▲";

Case (xsort_key = "ticket_status";

Case(xsort_order="1" ; "▼";""))))

I believe this could be written more simply as =

Case (

xsort_key = "ticket_status";

Choose ( xsort_order ; "▲"; "▼" )

)

What I would like to add is another series of case statements for the second set of sort fields ...

Try =

Case (

xsort_key = "ticket_status";

Choose ( xsort_order ; "▲"; "▼" ) ;



xsort2_key = "ticket_status";

TextColor ( Choose ( xsort2_order ; "▲"; "▼" ) ; 16711680 )

)

[Ron Cates]

Very cool Comment. Thank You. I kept working on it and came up with the following

Case (xsort_key = "ticket_status" and xsort_order="0";"▲";

Case (xsort_key = "ticket_status" and xsort_order="1";"▼";

Case (xsort2_key = "ticket_status" and xsort2_order="0";TextColor ( "▲" ; RGB ( 255 ; 0 ; 0 ) );

Case (xsort2_key = "ticket_status" and xsort2_order="1";TextColor ( "▼" ; RGB ( 255 ; 0 ; 0 ) );

""))))

I've never used the Choose () function. I'm going to look that up.

Edit; Just tested yours and of course it works brilliantly.

Thanks Again

[comment]

I kept working on it and came up with the following

Case (xsort_key = "ticket_status" and xsort_order="0";"▲";

Case (xsort_key = "ticket_status" and xsort_order="1";"▼";

Case (xsort2_key = "ticket_status" and xsort2_order="0";TextColor ( "▲" ; RGB ( 255 ; 0 ; 0 ) );

Case (xsort2_key = "ticket_status" and xsort2_order="1";TextColor ( "▼" ; RGB ( 255 ; 0 ; 0 ) );

""))))

There's no need to use multiple instances of Case() - you could have done the same thing as:

Case (

xsort_key = "ticket_status" and xsort_order = 0 ; "▲" ;

xsort_key = "ticket_status" and xsort_order = 1 ; "▼" ;

xsort2_key = "ticket_status" and xsort2_order = 0 ; TextColor ( "▲" ; RGB ( 255 ; 0 ; 0 ) ) ;

xsort2_key = "ticket_status" and xsort2_order = 1 ; TextColor ( "▼" ; RGB ( 255 ; 0 ; 0 ) )

)

However, it is more efficient to check each condition only once.

[Ron Cates]

Nice. This has been a great learning experience. Ultimately I did go with your solution. The choose () function is very nice :)

[Vaughan]

I have a portal sorting solution that uses calculated sort fields. That works fine. I also have an indicator on each column header to indicate the current sort and direction.

No need to create fields for this, do it all with conditional formatting. That is, put the sort arrows directly into the layout and use conditional formatting to hide them when not required.

[Ron Cates]

True. Conditional formatting would have worked as well. The fields are made and everything is in place now so no need to go back at this point. However, we are slowly updating to FMP 11 after which I will be looking forward to converting to global variables instead of fields for this.