Jump to content
TJ53

Sort Records by Field - don't "keep records in sorted order"

Recommended Posts

TJ53    3

Is there any way to not keep records in sorted order when using the script step "Sort Records by Field"?

Share this post


Link to post
Share on other sites
comment    1,371

What exactly do you mean by "not keep records in sorted order"?

Share this post


Link to post
Share on other sites
TJ53    3

In the "Sort Records" script step, there is a checkbox to "keep records in sorted order", that is checked by default, but it can be unchecked (left bottom in the dialog window).

But the "Sort Records by Field" script step doesn't have that checkbox so it always keeps the sort order. This is confusing when the user is in form view, because when the user edits the field used for the current found set sorting, then the position of the record relative to the found set changes. 

Share this post


Link to post
Share on other sites
comment    1,371

I am not sure what difference the position of the record relative to the found set makes when you're in form view.

In any case, I don't think there's a way to prevent re-sorting on commit other than having a script running or paused:
http://www.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Fsorting-records.html%23

Or use If / Else If / Else to select from multiple Sort Records [] steps.

Share this post


Link to post
Share on other sites
TJ53    3

Thank you for your reply. Regarding this:

Quote

I am not sure what difference the position of the record relative to the found set makes when you're in form view.

Let´s say that I have an invoices list sorted by status: "not paid" / "paid". So when "keeping the sort order", the not paid invoices are grouped first, and then there is the group of paid invoices. Now, if you change the status of an invoice from "not paid" to "paid", then that invoice will change its position to be in the "paid" group. If the user is in list view, he can see what's happening (the record changing its position in the found set), but if the user is in form view, it's confusing ... especially if the user navigates to next or previous record after changing the status of an invoice.

In the "Sort Records" script step this is just a matter of unchecking the "keep records in sorted order" checkbox.

But in the "Sort Records by Field" there is no such checkbox, so it's always keeping the sort order after committing the record.

The reason why I would like to use "Sort Records by Field" is to be able to dynamically sort records by just passing a script parameter with the name of the field (object name), so I don't need to hard code several "if - else " statements. So the script is something like this:

(first: name the sortable fields in the layout)

- set variable: $field_object_name = get(scriptparameter)

- go to Object ($field_object_name)

- Sort Records by Field

The problem is, I don't want the found set to keep records in sorted order, but it seems there is no way to disable this with the Sort Records by Field script step ... unless somebody has come up with an alternative way of doing this!

 

Share this post


Link to post
Share on other sites
LaRetta    477

I see the issue, thanks for explaining it further.  The only suggestion I have is to:

Set Variable [ $priorRecordNumber ; Get ( RecordNumber ) - 1 ]
... put your existing steps here
Go To Record/Request/Page [ by calculation ; $priorRecordNumber ]

This would put you at the last 'not paid' record before you changed the next one.  Would that help?  I would also suggest making the suggestion to FMI. :-)

On second thought, you could drop the -1 and should land on the 'next' record in sorted order.

Share this post


Link to post
Share on other sites
TJ53    3

Thank you for your suggestion LaRetta, unfortunately that wouldn't work for this case since the user would be confused to see that the record "disappears" after committed (it would land in a different record).

I think one of the biggest limitations of FM scripting is not being able to perform a sort based on criteria passed by a parameter. I thought I had that solved by using Sort Records by Field after going to the object (named field) based on the name passed as script parameter... and just noticed the limitation of not being able to "not keep the sort order". 

Share this post


Link to post
Share on other sites
LaRetta    477
50 minutes ago, TJ53 said:

I think one of the biggest limitations of FM scripting is not being able to perform a sort based on criteria passed by a parameter.

I agree.  It appears that you are hard-coding the field name in the object name?  That makes your solution vulnerable if field names ever change (or table occurrence names change (?).  Since you are going to the field anyway, are you instead setting the script parameter with:  Get ( ActiveFieldName) and skip the hard-code.

Another option ... why not find unpaid records first?  In that way, the record set would not be sorted and the record would not disappear when changed to Paid.  Anyway, I wish you well with your project!

  • Like 1

Share this post


Link to post
Share on other sites
TJ53    3

Thank you Laretta. Please see attached example of what I'm doing. The idea is to try to workaround the scripted sorting limitation of FM by naming fields in the layout and passing the name as parameter.

Actually, if FM had a script step "Go to Field By Name", the parameter passed could just be GetFieldName ( field_for_sorting ), and having to name the objects in the layout could be avoided. This is another limitation of FM scripting ... we have "Set field By Name", "Go to Object (object name as calculation)", but we don´t have  "Go to Field By Name" ...

Anyway, please let me know what you think about how I'm doing this ... I just want to avoid a bunch of "if - else" statements ... any suggestion is welcome, thanks!

Sort_by_field_object_name.fmp12

Share this post


Link to post
Share on other sites
BruceR    152

Sometimes it's better to just recognize and accept current limitations, proceed to do a few minutes work, and get a clear, scripted solution.

Which provides the result you need and provides a script that you can adapt, monitor in debugger, print, review, etc.

See attached mod to your file.

Sort_by_field_object_name_MODBFR.fmp12

  • Like 1

Share this post


Link to post
Share on other sites
TJ53    3

Thank you very much Bruce for your modifications, it looks good and crystal clear. I think it's a good philosophy you mention, I´ll definitely take it into account. I guess I often try to minimise the number of lines of codes and make my scripts modular, but I understand your point that this sometimes this goes against readability and easier debugging, I'll definitely look into that!

By the way, I've been using your virtual list technique and I would like to take this opportunity to thank you for such great contribution to the community. I guess this belongs to a different topic but ... would your technique benefit if adapted to be implemented using the new FM16 JSON functions?

Share this post


Link to post
Share on other sites
BruceR    152

You're welcome. Short answer re JSON; would adapt well. Have not pursued it yet. Yes, probably better to start a different thread, some examples might get submitted.

Share this post


Link to post
Share on other sites
TJ53    3

Sounds good! looking forward to that.

Share this post


Link to post
Share on other sites
BruceR    152

Thought you were saying you would start the thread! So - we'll see.

Share this post


Link to post
Share on other sites
LaRetta    477

TJ53, could you please update your profile?  It helps us when we respond to your questions.  Thank you!  :-)

Share this post


Link to post
Share on other sites
TJ53    3
10 hours ago, BruceR said:

Thought you were saying you would start the thread! So - we'll see.

I just did! looking forward to some replies ;)

 

10 hours ago, LaRetta said:

TJ53, could you please update your profile?  It helps us when we respond to your questions.  Thank you!  :-)

Done!

 

 

 

Share this post


Link to post
Share on other sites
Wim Decorte    446
21 hours ago, TJ53 said:

I guess I often try to minimise the number of lines of codes and make my scripts modular, but I understand your point that this sometimes this goes against readability and easier debugging, I'll definitely look into that!

"Premature Optimization is the root of all evil"...

Code efficiency and modularity is important, but not as important as solving the problem at hand.  It's a fine balance to strike.

Share this post


Link to post
Share on other sites
TJ53    3

Thank you for the tip! I'll definitely keep it in mind.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


  • Similar Content

    • By enquirerfm
      Based on a large DB I have created a summary report (I'm not interested in the detail) which extracts products according to certain criteria and groups them under sub-summaries. The product at the top represents the most sales, and the product at the bottom - the least. I wanted to obtain a ranking no eg No1 at the top and had thought to define a field which would accept this number by inputting the rank using Replace Field Contents with a serial number. However, it inserts incremental numbers through the whole file rather than just for the sub-summary. For example: say, I have a file of 2000 different hats in 50 different colours. A red hat is the most expensive at $100, the black hat the least - $50, say. But I sell only 10 of the red hat and 1000 of the black hats. So, in my summary at the top I have Black hats $50,000 (at my proposed No.1) and Red Hats below it at only $5,000 (proposed No. 2). eg
      Black hats $50,000
      Red hats $5,000
      How can I create and populate a field which I can add to this report which will indicate the following:
      Black hats $50,000 Rank 1
      Red hats $5,000 Rank 2
       
      ?
    • By Poruchan
      Hi,
      After the weekend races and all the results are imported into FM, I mark (now by hand) the “Key Races”. I’ll find each horse who either won or finished 2nd. As in this screen shot of Cherry Summer you can see he won his last race on Oct 3. (1)

      Then I mark the preceding race. (now I’m using a ‘K’, but I intend to change it to a number — different problem). The point is to show which race produced winners. The screenshot shows I have put a 'K' in Cherry Summer's race on may 31 and changed the 4 to a 5.

      Then I find matching records for RaceID (31may15tok5) on the record I  just tagged, and count the ‘K’s and enter the number in KRG. So before I updated this there were 4 'K's in the field KR and the field KRG showed 4s.

      It would be nice to have FM do this, but marking the previous race is my problem. It's not a terrible task now, but a while back I lost everything and it took me a month to get all the key races data back. I'd like to avoid that.
      Any suggestions?
      Thanks
    • By Daniel Wood
      When FileMaker sorts records it happens on the client machine. Consequently, sorting can be a very painful process for users over a slow connection to a database. In this article we show a very fast way in which records can be presented to the user in descending creation order, without sorting delays...
       
      Read the Full Article Here…
    • By Stu412
      Hi there
      I'm working on a task at the moment which involves sorting around 65000 records on a data table.  The table itself has many occurrences but I only need to sort on this particular occurrence, therefore my sort criteria (ClientID, Date) are based on this occurrence.
      The issue is that when I sort the data on my local PC, it takes maybe two seconds to sort, calculate and display.  For the task in mind, this is perfectly acceptable as I won't be doing it too often.  However, now this has been deployed on a server which is more than up to the task and exceeds the recommended spec, the sort takes several minutes with a countdown of records sorted slowly going from 65000 downwards.
      The network between the server and the client is again, up to speed and poses no issues in other areas.
      So, what's FM doing to make the sort so slow?
      The local sort is fine, so can I cache to a local client and sort from there?
      I don't get why the sort cannot utilise the speed of the server or where the bottleneck is occurring?
      Thanks in advance
    • By Justin Close
      I am running into an issue of some records not sorting the same way.  The user's UI has a portal viewing some related records; this portal is sorted by two fields.  However, it doesn't appear that the 2nd field is having the correct sorting affect.
      This same table, when viewed in Table view with the same found set, and then sorted by these two fields, does appear to correctly sort.
      The relationship for the portal isn't itself sorted, so I can't seem to discern where the issue is.  I have attached screen shots of the Table view (along with it's applied sorting options), and the portal (showing both the records as viewed, and then one with the sorting options).
      This is in FM11.
      Anyone have some suggestions?
      Thanks,
      Justin

×

Important Information

By using this site, you agree to our Terms of Use.