1 2 Previous Next 26 Replies Latest reply on Sep 15, 2015 7:49 AM by erolst

    Restrict Found Set to 50 Records with Multiple Sorting

    m.swanston

      To get around an issue I'm having with a specific layout, I'd like some help with restricting the found set to 50 records, but with some sorting complexities thrown in. The issue appears when the a specific layout contains more than 50 records (only happens when 51 or more records shown), each record having a pop-over button associated with it for editing the selected record.

      I have previously posted about it, but it seems to be both specific to this tool and only happens in WebDirect (both IE and Chrome), and FileMaker kindly tried to replicate but could not.

      Anyway, the data seen in the layout is a mix of two text, one numerical, one date field and a popover button. Currently the data is displayed in a specific order (Finalised Date, Descending - applied by a script when the layout is accessed by a Button click) so that users see the latest finalised records at the top and any un-finalised records (ie without a Finalised Date) at the bottom. This works fine with < 51 records but once we get to 50, then it will need to be changed, so would rather start the thinking sooner than later.

      What I'd like to get the layout to display is the latest 50 records BUT to include those that have not been finalised. I can't get my head around how to sort this data to include the un-finalised records and limit the found set to 50. In my head it was easy - sort by Finalised Date Ascending, restrict to 50 and then re-sort by Finalised Date Descending but of course this means it will display the older records at the top, not the newer ones.

      I hope my ramblings make sense! Please ask if you need any clarification.

      Thanks in advance

      Martin

       

        • 1. Re: Restrict Found Set to 50 Records with Multiple Sorting
          erolst

          m.swanston wrote:

          In my head it was easy - sort by Finalised Date Ascending, restrict to 50 and then re-sort by Finalised Date Descending but of course this means it will display the older records at the top, not the newer ones.

           

          Shouldn't that be: “restrict to the last 50”? i.e. …

           

          Show all Records

          Sort [ date ; ascending ]

          Go to Record/Request [ first ]

          Omit Multiple Records [ Get ( FoundCount ) - 50 ]

          Sort [ date ; descending ]

          • 2. Re: Restrict Found Set to 50 Records with Multiple Sorting
            m.swanston

            Yes, it looks like that's exactly what I'm after. My only issue is that when adding a new one, the new item drops off the bottom, so will have to re-apply the filter on save I guess? Many thanks, that was easier than it seemed in my head

            Actually, it's not working for subsequent requests. I've just reloaded the layout and the new one is now visible but what was the last item on the list before the addition has now gone. I've repeated the process and it's doe the same for another addition ie I had two unfinalised at the bottom, i now still have two, but the new two. I need all un-finalised to remain in place.

            Martin

            • 3. Re: Restrict Found Set to 50 Records with Multiple Sorting
              erolst

              m.swanston wrote:

               

              Yes, it looks like that's exactly what I'm after. My only issue is that when adding a new one, the new item drops off the bottom, so will have to re-apply the filter on save I guess?

               

              When you add a new record, you have 51 – which I gather is the state of affairs you want to avoid?

               

              So you would want to re-apply that script.

               

              (Not sure why it wouldn't work on subsequent applications, though.)

              • 4. Re: Restrict Found Set to 50 Records with Multiple Sorting
                m.swanston

                Yes, that's correct, I need to only ever see 50, but those 50 to include all the un-finalised records. I've tried it several times and still getting the incorrect number of un-finalised records - it's decreasing by 1 each time. I've double checked the script and it follows your process:

                Show all Records

                Sort [ date ; ascending ]

                Go to Record/Request [ first ]

                Omit Multiple Records [ Get ( FoundCount ) - 50 ]

                Sort [ date ; descending ]

                 

                I have some questions about the omit records now I look at it - that appears to be counting the number of records and deducting 50, so when there is 51, it returns 1 - if it's 1 from the bottom, that will be the newest finalised record, which isn't right? Or am I misunderstanding this? And what happens when there is < 50? Does that script step get ignored if the answer is < 0?

                Thanks again

                Martin

                • 5. Re: Restrict Found Set to 50 Records with Multiple Sorting
                  erolst

                  m.swanston wrote:

                  I have some questions about the omit records now I look at it - that appears to be counting the number of records and deducting 50, so when there is 51, it returns 1 - if it's 1 from the bottom, that will be the newest finalised record, which isn't right? Or am I misunderstanding this?

                   

                  Probably … if you have n records and want to keep the final 50, you need to go to the first record and omit (starting from there) the difference between n and 50; the result isn't what is kept, is what's being discarded.

                   

                  Using that logic, you will either have all finalized records, or 50, if their count is greater.

                   

                  m.swanston wrote:

                  And what happens when there is < 50? Does that script step get ignored if the answer is < 0?

                   

                  Yes; you cannot omit a negative number of records.

                   

                  On the other hand, you could/should probably check in the script for the found count and only proceed if so required, to avoid unnecessary processing.

                  • 6. Re: Restrict Found Set to 50 Records with Multiple Sorting
                    Magnus Fransson

                    Here are my attempt on a solution to the problem.

                    However... My version of FileMaker are in swedish so it may be hard to interpret.

                     

                    FM_script.png

                     

                    Best regards Magnus Fransson.

                    • 7. Re: Restrict Found Set to 50 Records with Multiple Sorting
                      m.swanston

                      Thanks Magnus...I may need to translate some of the code but think, from your comments it's pretty clear what is going on.

                      I'll look at adding this and see if I can get it working, I will post back on my findings.

                      Thanks

                      Martin

                      • 8. Re: Restrict Found Set to 50 Records with Multiple Sorting
                        m.swanston

                        Hi Magnus

                        I have some questions for you, as am working through creating a script based on your suggestions above.

                        I realised that this didn't cater for when there were <= 50 in total, so added an If block before the found that checked if the found count was <= 50 and if so, sort the records by Finalised Date in descending order, which would simply give me what I have now, i.e. all the finalised records at the top, latest first and the un-finalised records at the bottom.

                         

                        I'm thinking about the results as I create the script, and wanted to clarify what is happening.

                         

                        So after my new If, I perform a find of records where Finalised Date is blank and store the number of records found.

                        Then, if the number of un-finalised records exceeds 50, display the top 50 un-finalised records and nothing else:

                        • Sort to see the records in Creation_Timestamp order (latest first);
                        • Only display top 50 records

                        Not sure why you'd sort again here? - there are 50 records without a finalised date so happy to display in timestamp/descending order.

                         

                        But when it comes to the Else, I'm a bit confused in places. I need to show both finalised and un-finalised together, but not being that proficient on FileMaker yet, I am not sure I get what is happening with the code. This is what I understand it's doing:

                        The found set of unfinalised records is still displayed, so here we switch that around to show only the finalised records;

                        • Show the Finalised records (as these were omitted from previous find);
                        • Sort the records by the Date Finalised, descending order?
                        • Display the records required to make up 50 in total;
                        • Show the un-finalised records (do these get appended to the Finalised records?)
                        • Sort the records by the Date Finalised, descending order?

                        How does the final found set get restricted to 50?

                        Here is my script, in English. I'd appreciate a review to make sure I've correctly translated/understood what you were doing.

                        FileMaker.JPG

                        Many thanks for you help

                        Martin

                        • 9. Re: Restrict Found Set to 50 Records with Multiple Sorting
                          erolst

                          May I interrupt by saying that you're making this way too complicated?

                           

                          If your priority is to show all un-finalized (or at least 50):

                           

                          Set Variable [ $allowedCount; Value:50 ]

                          Show All Records

                          If [ Get ( FoundCount ) > $allowedCount ]

                            Sort Records [ ascending by dateFinalized ]

                            #ascending = un-finalized first

                            Go to Record/Request [ $displayCount + 1 // OMR includes current ]

                            Omit Multiple Records [ Get ( FoundCount ) - $allowedCount ]

                          End If

                          Sort Records [ descending by dateFinalized ]

                          #descending = finalized first

                          Go to Record/Request [ first ]

                          • 10. Re: Restrict Found Set to 50 Records with Multiple Sorting
                            Magnus Fransson

                            m.swanston wrote:

                             

                            Hi Magnus

                            I have some questions for you, as am working through creating a script based on your suggestions above.

                            I realised that this didn't cater for when there were <= 50 in total, so added an If block before the found that checked if the found count was <= 50 and if so, sort the records by Finalised Date in descending order, which would simply give me what I have now, i.e. all the finalised records at the top, latest first and the un-finalised records at the bottom.

                             

                            I'm thinking about the results as I create the script, and wanted to clarify what is happening.

                             

                            So after my new If, I perform a find of records where Finalised Date is blank and store the number of records found.

                            Then, if the number of un-finalised records exceeds 50, display the top 50 un-finalised records and nothing else:

                            • Sort to see the records in Creation_Timestamp order (latest first);
                            • Only display top 50 records

                            Not sure why you'd sort again here? - there are 50 records without a finalised date so happy to display in timestamp/descending order.

                             

                            But when it comes to the Else, I'm a bit confused in places. I need to show both finalised and un-finalised together, but not being that proficient on FileMaker yet, I am not sure I get what is happening with the code. This is what I understand it's doing:

                            The found set of unfinalised records is still displayed, so here we switch that around to show only the finalised records;

                            • Show the Finalised records (as these were omitted from previous find);
                            • Sort the records by the Date Finalised, descending order?
                            • Display the records required to make up 50 in total;
                            • Show the un-finalised records (do these get appended to the Finalised records?)
                            • Sort the records by the Date Finalised, descending order?

                            How does the final found set get restricted to 50?

                            Here is my script, in English. I'd appreciate a review to make sure I've correctly translated/understood what you were doing.

                            FileMaker.JPG

                            Many thanks for you help

                            Martin

                             

                            Hi Martin,

                             

                            First, let me add two rows of code that i missed i my previous post.

                            - Right before every "Omit records" (but after the sort) there should be a "Go to record [First]" script step. In your cod that is (from the end) between lines 27 and 29, and between lines 18 and 20. This is so that the right records should be omitted.

                             

                            Now to the explanation. I am using omitted records in an unusual way, which may trick your understanding of the code. What I do is to use omitted records as a temporary storage. The procedure is:

                             

                            1. After a find we have, say 30 un-finalised records.

                            2. We switch those, in to temporary storage using "show omitted". (now 30 un-finalised records are in "storage".

                            3. Through "omit records" we send the calculated difference (50-30=20) finalised records to the temporary storage. (There are now, 30 un-finalised records + 20 finalised records = totally 50 records, in the temporary storage.)

                            4. Now we restore those to the foreground.

                             

                            Done.

                            Except for sorting that is what's happening.

                             

                            I hope you get it. Best regards Magnus Fransson.

                            • 11. Re: Restrict Found Set to 50 Records with Multiple Sorting
                              m.swanston

                              OK, so the priority is to show ALL un-finalised records AND however many finalised records to make up the difference, so there will never display more than 50 records at any time (at least until we can find a resolution to the issue with the layout). The records must display in descending order so the finalised records show first (latest first) followed by the un-finalised records.

                              In your sample, what does $displayCount refer to?

                              Thanks

                              Martin

                              • 12. Re: Restrict Found Set to 50 Records with Multiple Sorting
                                m.swanston

                                Thanks Magnus, that's useful.

                                Regards

                                Martin

                                • 13. Re: Restrict Found Set to 50 Records with Multiple Sorting
                                  erolst

                                  m.swanston wrote:

                                  OK, so the priority is to show ALL un-finalised records AND however many finalised records to make up the difference … The records must display in descending order so the finalised records show first (latest first) followed by the un-finalised records

                                   

                                  That's exactly what my script does; it's basically the same as the one I suggested some posts ago, though I mixed up your priority there:


                                  • if found set > allowed, sort by un-finalized/finalized and take the first 50

                                  • (re-)sort by finalized(if any)/un-finalized

                                  m.swanston wrote:

                                  In your sample, what does $displayCount refer to?

                                  That's the number of records you want to keep.

                                  • 14. Re: Restrict Found Set to 50 Records with Multiple Sorting
                                    m.swanston

                                    So $displayCount is meant to be $allowedCount (to match the variable declared)?

                                    But (and I may be wrong here, but need to clarify a little niggle in my understanding), if you sort by Finalised Date in ascending order first, then take the first 50, you will omit the latest finalised records (as they will be at the bottom).

                                    The final found set should contain the latest finalised records (so omitting the oldest finalised records) and all un-finalised records.

                                    Thanks again

                                    Martin

                                    1 2 Previous Next