11 Replies Latest reply on Jan 7, 2015 2:23 PM by jbante

    Sorting in a Virtual List

    connections

      I have created a report based on a virtual list (thank you to those who have posted articles on the technique!) but I am getting a strange sort order on one column. It happens in table view and in the report. Attached is the table view showing Column11 sorted in ascending order but not sorted correctly. What could cause this?

       

      ExecuteSQL (

      "Select

         EST.EstimateID, EST.Estimate, EST.EstimateName,

         ED.Room, ED.RoomDescript, ED.SystemID, ED.System, ED.RoomDim_eval as Area,

         SYS.SystemName,

         SD.SystemID, SD.MaterialCode, SD.MaterialCoverage, SD.MaterialPrice, SD.MaterialProduction, SD.MaterialJobClass,

         M.MaterialName,

         UD.JobClass, UD.PayRate, UD.Benefits,

       

         ED.RoomDim_eval / SD.MaterialCoverage as Quantity,

         Quantity * SD.MaterialPrice as Cost,

         Quantity / SD.MaterialProduction as Hours,

         Hours * (UD.Payrate + UD.Benefits) as Labor

        

      From EstimateDetail ED

      Join SystemDetail SD on ED.SystemID = SD.SystemID

      Join System SYS on ED.SystemID = SYS.SystemID

      Join Material M on SD.MaterialCode = M.MaterialCode

      Join Estimate EST on EST.EstimateID = ED.EstimateID

      Join UnionDetail UD on EST.UnionID = UD.UnionID and SD.MaterialJobClass = UD.JobClass

       

      Where ED.EstimateID = ?

      ";

      "~" ; "" ;

      $EstimateID )

       

      Thank you,

      Brian

      FMP A 12.0v5 on Mac

        • 1. Re: Sorting in a Virtual List
          jbante
          Virtual lists usually don't respect the sort order applied to the actual FileMaker records, since the records usually pull data from a row of the list based on the record's position in the found set, which will change when you try to sort. Sort the data stored in your virtual list variable(s), not the actual FileMaker records.
          • 2. Re: Sorting in a Virtual List
            connections

            Thanks for the quick reply. I've attached an image of the table definition. Since the columns are pulling data based on an index (idx) in their respective row, it seems the record position would be irrelevant. Also, for the reports, I'll need to sort and break on sorted columns. If I sort the virtual table before running the report and then sort for the report, wouldn't I lose the pre-sorted order? Is using an index column not the correct, or a reliable, way to implement the list?

             

            Thanks,

            Brian

            • 3. Re: Sorting in a Virtual List
              mark_scott

              I know there are a number of flavors of virtual list. The flavor that Todd Geist used in his master-detail module over at Modular FileMaker does respect the found set's sort order. I've been using this approach (with a "ListOf" summary field to gather IDs coupled with Daniel Wood's suggestion re using FileMaker's internal record ID (as an unstored calc) for best performance in hosted solutions). I must say that the virtualization part of it screams! When tested on a LAN (no guarantees here re WAN performance, which I haven't tested), I can virtualize a new, or newly re-sorted, found set (of a ridiculously large, for a portal, "stress-test" size of 10,000 records!) in a matter of milliseconds. Even 100,000 records screams!

               

              I know this isn't directly relevant to Brian's ExecuteSQL-based approach, but just thought I'd throw that out there as an example of a VL that does respect found-set sort order.

               

              Mark

              • 4. Re: Sorting in a Virtual List
                taylorsharpe

                This isn't an answer, but I sure wish FileMaker SELECTs could be stored in a temporary table like in many other SQL environments that you could subsequently do more manipulation such as various sort orders or additional selects.  It would make issues like this moot.  Just a wish list for maybe the future!

                • 5. Re: Sorting in a Virtual List
                  user19752

                  It seems your field is defined as "Calculation result is : Number".

                  Change it to "Text" will resolve this, but this means virtual list is not general-purpose if you want to sort it, need every field types match to SQL result.

                  • 6. Re: Sorting in a Virtual List
                    pjreagan

                    Have you already tried indexing the calculations?  They're showing as unstored in that screenshot.  In my test just now I could get a calculation to store when it's set to display a virtual list value using the GetValue function.

                    • 7. Re: Sorting in a Virtual List
                      connections

                      You are correct. Changing the calculation result to text instantly solved the issue. How did you see that? I didn't notice any indicator on the attachments.

                       

                      Thanks again,

                      Brian

                      • 8. Re: Sorting in a Virtual List
                        connections

                        I hadn't tried storing or indexing the calculated fields. From what I think I understand about virtual lists, leaving them unstored means I don't have to clear fields or delete records after I'm finished with the data. The answer appeared to be correcting the Calculation Result in the column definition.

                         

                        Brian

                        • 9. Re: Sorting in a Virtual List
                          user19752

                          Field type issue is general in sorting.

                          Your first attachment shows it as first 8 records have number value (290) but last 4 don't.

                          So I tested it myself before post that number field is sorted as "null is first" but "having text is last".

                           

                          Cheers

                          • 10. Re: Sorting in a Virtual List
                            pjreagan

                            Yes, indexing does present some refresh issues when used with virtual lists.  The lists themselves of course are just global script variables.  Good catch user19!

                            • 11. Re: Sorting in a Virtual List
                              jbante

                              For sub-summary reports with virtual lists based on position, the approach is to sort the virtual list contents first, then "sort" the found set by the same criteria to get the summary parts to appear. Since the sorts are the same, the parts appear in the correct places.