3 Replies Latest reply on Apr 20, 2011 3:02 AM by LaRetta_1

    Filemaker equivelent of SQL Select Distinct



      Filemaker equivelent of SQL Select Distinct


      The solution I need can be asked with the problem from this scenario:

      I have a table with sales, and in that table there is a PART field. Sales for a given day have hundreds of records, but under one hundred parts. What is the fastest or best way to produce a list of the parts that are in the given found set.

      Up until now I have used a script that sorts by part and loops through them adding to a return separated list every new value it encounters in the part field. I can then take that list and place it in a separate table of field.

      In SQL this is very easy, I would do: Select distinct Part from Sales.


        • 1. Re: Filemaker equivelent of SQL Select Distinct

          The quickest and easiest is to create a Report layout and let the Report Wizard do it for you.  If you want only a list of part name/numbers themselves you can uncheck 'include sub-totals or grand totals'. 

          You will have to select the part to be in the body and then in the next screen, organize by part and you MUST sort by part.  After the report is done, you can delete the body.  Then run the script.  You can later create a summary field which counts the number of sales per part.  Place the summary field in whatever part you want to count so if you place it in the leading part based upon Part then it will count the parts.

          What do you want to do with your list?  Knowing how these unique results will be used will truly determine how best to achieve the results.

          • 2. Re: Filemaker equivelent of SQL Select Distinct

            You are right, it is hard to give a solution without knowing what the results will be for.

            This is for a summary table that is kept to speed reports up. So I need a list of values that I can use in a script to create one record per distinct part in that sales table so in the end in the summary table I will have one record per distinct part sold that day.

            • 3. Re: Filemaker equivelent of SQL Select Distinct

              Summary tables are difficult to keep in perfect synch.  All it takes is one power failure or glitch to throw the counts off.  But your need is quite legitimate.  Are you wanting to save PartID, quantity and date?

              A 'fairly safe' way to generate such a table is to use 'Allow Creation'.  Create a table called summaries (or whatever) with PartID, Quantity and Date.  Join it to your LineItems table as:

              LineItems::PartID = Summaries::PartID
              LineItems::Date = Summaries::Date

              Below in relational dialog, check 'allow creation of related' on the summaries side.  If you are SURE that nobody is allowed to delete a lineitem from an Invoice the next day (or few days later before it is shipped and posted) you can skip this next part but I wouldn't.

              Create a flag field (number) in your LineItems table called flg_sum.  Then the script would look like this (and back up first):

              Go to Layout [ layout based upon your lineitems ]
              Enter Find Mode [ uncheck pause ]
              Set Field [ LineItems::CreateDate ; < Get ( CurrentDate )  ]
              Set Field [ flg_sum ; "="  ]
              Perform Find [ ]
              If [ not Get ( FoundCount ) ]
              Show Custom Dialog [ "No records to process." ]
              Go to Layout [ original layout ]
              Halt Script
              End If
              Go to Record/Request/Page [ First ] ... not required after performing a find but I can't help but overly protect here
              Set Field [ Summaries::Quantity ; Summaries::Quantity + LineItems::Quantity ]
              Set Field [ LineItems::flg_sum ; 1 ]
              Go to Record/Request/Page [ Next ; exit after last ]

              By always checking prior, you will catch any which might have been missed because of a glitch.  It will eventually take longer but since the fields are indexed, it should not be noticable.  If it becomes a bother, you can limit your find to just prior day as Get ( CurrentDate ) - 1.

              What this does:  If a new PartID/Date does not exist in summaries table, it will create it and insert the PartID and Date automatically.  If the PartID/Date does exist, it will add the count to the existing summaires record.