0

Sort related records by specific field

I have added a new formula field to a table.  The forumla needs to get related records from another table, sort those records based on a specific numeric field, then select the last record based on that sort order.  I'm trying to use the "order by" keyword to do this, but it seems this only works when using "select".  Here's the code I have:

let relatedClips := ('ref Shots'.'ref Submission Clips') order by 'SV Number';
last(relatedClips)

Is there a way to achieve this without using "select"?  I have been finding that formulas with "select" are much slower to calculate, so I've been trying to rely on the relationship directly, rather than "select"ing the records.

8 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    select is required to filter and sort using script.

    • Post Hoc Limited
    • Peter_Amies
    • 4 yrs ago
    • Reported - view

    Ah.  That's frustrating.

    Am I right when I'm seeing that "Select" is a lot slower than using the record relationships to populate a view or make calculations?

    • Sean
    • 4 yrs ago
    • Reported - view

    Yes, it will slow performance if you have many records, especially in the browser version. Will this formula generate a unique value for each record in that table?

    • Post Hoc Limited
    • Peter_Amies
    • 4 yrs ago
    • Reported - view

    It should do.  The database is tracking Visual Effects production for TV.  The "Submission Clips" table which I'm querying holds records of a huge number of video clips being sent to us for review.  Each shot in the TV show will have multiple versions sent for review, and I need  my formula to find the name of the version that was approved.

    So I need to:

    1 - Select records from the "Submission Clips" table
    2 - Filter the selected records to only include those with a "Name" matching the record my forumla is part of
    3 - Order the selected records based on a numeric field in the "Submission Clips" table
    4 - Select the last record in the array generated by the previous 3 steps.

    Unless, of course, there's an easier way to do this.  :-)

    I'd hoped to just use the table relationship for this, but I can't figure out how to write the formula to select a specific record from the related set.

    • Sean
    • 4 yrs ago
    • Reported - view

    You could sort on 'SV Number' in descending order in the Table Reference view and so it would be the first record.

    • Post Hoc Limited
    • Peter_Amies
    • 4 yrs ago
    • Reported - view

    Hi Sean.  Can you clarify that?  What I guess you're saying is that as long as the main view of that form is sorted by "SV Number", any array of records generated by a formula in a related table would be sorted in that same order?

    And if, as admin, I change the default sort order of that table, any formulas related to it will update?

    • Sean
    • 4 yrs ago
    • Reported - view

    In your original post you said you need to get related records from another table. I assumed you have a table reference and in your parent table form you have a reference view of your child table which would be 'Submission Clips'. Following that assumption you use that resource which is already available without creating a new formula and possibly slowing the performance of your database. In the reference view table set the sort order of 'SV Number' to descending which would make the latest record to be added first. I believe that was how you wanted your formula to work.

     

    Another option would be to add a "Yes / No" field to the 'Submission Clips' table and mark a record "Yes" if it is approved. You could filter on that field and the performance should be better in a formula.

    • Post Hoc Limited
    • Peter_Amies
    • 4 yrs ago
    • Reported - view

    Great.  Thanks!  I'll give it a go.

Login to reply

Content aside

  • 4 yrs agoLast active
  • 8Replies
  • 2525Views