Indirection with sorting?

[Søren Dyhr]

In another thread have I stumbled over something which I probably could pull off via Applescript, but it's lack of being cross-platform'ish makes me wonder if I'm forgetting something?

I could not say if the scale of records would allow me to transfere a copy the field in question to a specific sort field - in which the sorting then is performed in? 

 

BTW the first part of this script, is greatly inspired by this thread: 

https://community.claris.com/en/s/question/0D53w000057xdMHCAY/can-set-field-by-name-be-used-with-repeating-fields

Thanks for this to Michael (Comment) and Alex Zueiv ...!

TestofUX.fmp12

Edited June 16, 2024 by Søren Dyhr

[comment]

The only method I know of to sort records by a field that's not hard-coded into the script step is to loop among the fields until:

Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName ) = Get ( ScriptParameter )

then do Sort Records by Field [] without specifying a field.

 

6 hours ago, Søren Dyhr said:

transfere a copy the field in question to a specific sort field - in which the sorting then is performed in? 

If you mean transfer the contents of the selected field, that can work only in very limited circumstances. You cannot use the same field to sort text, numbers, dates or times correctly. There are some adjustments that can be made, but the more prefect you make it, the slower it becomes  and - at least in my experience - it becomes unusable very quickly.

 

 

[comment]

1 hour ago, comment said:

The only method I know of to sort records by a field that's not hard-coded into the script step

I should add that this refers to FM's native sort.

You could use SQL to sort the record IDs by any field, and then sort the records themselves by a calculation field that finds the position of the record's ID in the sorted list.

Since the SQL query is text, it's easy to inject a dynamically supplied field name into it and have SQL automatically sort by it, using the correct type (note that the default SQL sort of text fields is case-sensitive).

Attached is a minimalistic demo of the method. 

Keep in mind that while this affects only the current found set, the method starts by sorting all records in the table. Possibly the initial sort could be performed on the server, and the result passed back to the client to perform the actual sort of the found records only.

 

DynamicSortBySQL.fmp12

Edited June 16, 2024 by comment

[Søren Dyhr]

1 hour ago, comment said:

Keep in mind that while this affects only the current found set, the method starts by sorting all records in the table. Possibly the initial sort could be performed on the server, and the result passed back to the client to perform the actual sort of the found records only.

Thats great, and exactly what I was looking for ... but could you expand a little on the use of ORDER BY \"«selectedField»\"" ... why the chevrons?

--sd

[comment]

3 minutes ago, Søren Dyhr said:

why the chevrons?

It's a placeholder. The chevrons make it convenient for the Substitute() function to find it. 

I find it more readable and easier to maintain to do, for example:

Let (
template = "set myVar to «amount»"
; 
Substitute ( template ; "«amount»" ; MyTable::Amountfield )
)

 than:

"set myVar to " & MyTable::Amountfield

 

[LaRetta]

Sweeeeeeet!!

[comment]

To make it a bit more robust, you can change the SQL query to use ROWID instead of the ObjectID field, and make the calculation field =

Position ( ¶ & $$sortedIDs & ¶ ; ¶ & Get (RecordID) & ¶ ; 1 ; 1 )

Then you don't have to worry about the ID field being renamed or clashing with a reserved SQL word.

(Note that good practice would still require the table name to be calculated and quoted, to protect against both eventualities - something I did not bother to do in the above demo.)

 

[Søren Dyhr]

18 hours ago, comment said:

then do Sort Records by Field [] without specifying a field.

I went with this one, by utilizing Bruce's custom function: https://www.briandunning.com/cf/1167 

However there might still be something in need of tightening?

--sd

 

Edited June 17, 2024 by Søren Dyhr

[comment]

2 hours ago, Søren Dyhr said:

utilizing Bruce's custom function: https://www.briandunning.com/cf/1167 

I don't see why it's necessary. You can simply pass GetFieldName ( ... ) in the script parameter and compare it to the current active field as shown above.

[Søren Dyhr]

30 minutes ago, comment said:

I don't see why it's necessary. You can simply pass GetFieldName ( ... ) in the script parameter and compare it to the current active field as shown above.

Just because the script parameters where given a number in the template ... that's why!

....but you're right the scriptparameter could be more "saying" 

--sd

[comment]

18 hours ago, Søren Dyhr said:

the script parameters where given a number

Even so, what you're doing seems wrong to me (if I read it correctly). 

You are calculating the position of the current field's name in FieldNames() and comparing this to the script parameter.  But the contents of the list can change as you add or remove fields on the layout, while the script parameter remains hard-coded.

 

[Søren Dyhr]

I have been giving it some thoughts, and would probably reverse the logic, and make the buttons scriptparamter be a singeled out from the list instead:

Getvalue(Fieldnames() ....

But unfortunately the scriptparamter is here used for other matters as well, flicking the icon in the each button.

--sd

Edited June 18, 2024 by Søren Dyhr

[comment]

1 hour ago, Søren Dyhr said:

Getvalue(Fieldnames() ....

But which value would you get??

 

1 hour ago, Søren Dyhr said:

unfortunately the scriptparamter is here used for other matters as well,

I see no problem passing multiple values, using either a return-separated list or JSON.

 

[Søren Dyhr]

10 hours ago, comment said:

I see no problem passing multiple values, using either a return-separated list or JSON.

Neither do I 🤪

--sd