Looking for a more elegant way of scripting this

[Rich S]

Greets, all:

Attached is a script I cobbled together that works as it should, but I'm just wondering if there's a more elegant way of coding it: There are three text fields (Sort1__lxt, Sort2__lxt, and Sort3__lxt) where users can choose from a value list (with three values: Entered By, Date, and Student Name) the order they want the values to appear in a sub-summary report, e.g., Entered By as the first level, Date as the second level, and Student Name as the third level.

I have a number of these same-style reports so what I'm doing is duplicating the same script and replacing the fieldnames for each one, but I'm thinking that there's gotta be a better way of coding it so that the three, separate, If/End If blocks of code can be done with one and some global variables, maybe? Any ideas?

Cheers,

Rich

Script.pdf

Edited February 26, 2020 by WF7A
Grammar police

[comment]

First, I would not use Replace Field Contents for this, because (1)  it doesn't allow each user to have their own sort order and (2) it will fail if some records are locked by another user and (3) it modifies records needlessly.

Now, you only have 3 fields they can select for sorting, which gives 6 possible permutations. I would simply prepare 6 Sort Records steps and select the correct one using If and Else If -  for example:

If [ SomeTable::gSort1 = "Entered By" and SomeTable::gSort2 = "Date" and SomeTable::gSort3 = "Name" ]

  Sort Records [ Specified Sort Order: 
    YourTable:::EnteredBy; ascending 
    YourTable:::Date; ascending
    YourTable:::Name; ascending
    [ Restore; No dialog ]
 
Else If [ SomeTable::gSort1 = "Entered By" and SomeTable::gSort2 = "Name" and SomeTable::gSort3 = "Date" ]

  Sort Records [ Specified Sort Order: 
    YourTable:::EnteredBy; ascending 
    YourTable:::Name; ascending
    YourTable:::Date; ascending
    [ Restore; No dialog ]
 
Else If [ ...

 

Another option is to sort by 3 unstored calculation fields that select their values according to what the user selected - but that might take some time. 

 

[Rich S]

Thanks--sage advice, there.

The sorting works great with your suggestion but wouldn't I have to create six layouts, then, for the different permutations? I probably should've included the screenshot below of the ACTION_ITEM table--as you can see, there are (the) three Sort fields; they're used in the report layout's sub-summary sections shown above. As such, I still have to push data into the Sort fields with data from ACTION_ITEM::ActionItem__lxt, ACTION_ITEM::Date__lxd, and STUDENT::_kflt_StudentID, no?

Edited February 26, 2020 by WF7A

[comment]

42 minutes ago, WF7A said:

wouldn't I have to create six layouts, then, for the different permutations?

If you want to show 3 sub-summary parts in the same order as selected by the user, then yes. Or use the unstored calculation fields alternative.

I should add that there is no good solution for dynamic sorting. I saw an opportunity here to use hard-coded sorts simply because the number of permutations is so small. For anything more complicated you will need to move to a more complicated method and deal with problems of different data types and performance.

 

[Rich S]

Good to know, thanks.