5 Replies Latest reply on May 10, 2011 1:16 PM by philmodjunk

    Produce List of UniqueIDs in found set.

    RestaurantCharlie

      Title

      Produce List of UniqueIDs in found set.

      Post

      I'm looking for a function that can do the equivalent of SQL's Select Distinct.

      For example, I have a table with SalesLineItems. If I want to produce a summary table for sales by product for a given week. In SQL I would start from selecting the distinct Part IDs where date range is between A and B and then joining the sum of qty. I might want to store the return separated list as a variable of global field and then work from there.

      This is only one of the things I want to use it for, and while there might be a better way to achieve this in filemaker (input appreciated), I really want to know how to produce the equivalent of Select Distinct.

        • 1. Re: Produce List of UniqueIDs in found set.
          philmodjunk

          Keep in mind that the "context" can make a huge difference here. I can only respond to the specific example you've given. It won't generalize to all situations:

          There are two fairly simple ways to do this type of report:

          In Sales LineItems, create a summary report with a sub summary part "sorted by" the PartID field.
          Remove the body layout part and just use the sub summary parts.

          Your report will have one row for each unqiue partID--provided at least one item of that ID was sold in the time interval specified in your find that you used to find the records for this report.

          Build the report in a different table, such as your products table where you already have one record for each unique partID. With the right relationships, you can reference and compute totals from the lineItems for a specified range of dates to get your totals for each item in the products table.

          Your report will have one row for each unique partID with a total of zero showing if that item was not sold for the specified date interval.

          • 2. Re: Produce List of UniqueIDs in found set.
            RestaurantCharlie

            So there is no way to escape a script to produce the list?

            I was hoping there was a function that could produce it.

            If there isn't, with the mentioned example, then probably relate the saleslinesitems to product table with a global field on the product table for the initial date range and another for the final date range, and use a go to related records and can work the script from there...

            • 3. Re: Produce List of UniqueIDs in found set.
              philmodjunk

              I don't describe any script at all here.

              To find a range of records, you can enter find mode, then enter 4/1/2011...4/30/2011 to find all records from the first of April to the last day of April. You can do this manually or in a script.

              • 4. Re: Produce List of UniqueIDs in found set.
                RestaurantCharlie

                I found a Custom Function that can produce the list!!!!!!!!!!!

                I'm one happy camper!

                The link is http://www.briandunning.com/cf/1093

                The function is called GetFoundSet.

                Just in case the link breaks sometime, here is the function:

                /* GetFoundSet ( FieldName )

                NOTES:
                1) Fieldname MUST be in quotes
                2) For related fields, use entire field path (i.e. "TO::fieldname")
                3) For local fields (current found set), use field name only (i.e. "fieldname")

                Author: Howard Schlossberg, FM Professional Solutions
                */


                Case (

                IsEmpty ( $$RecCount ) ; Let ([
                   $$RecCount = 1; 
                   $$TotalCount = Case(PatternCount(fieldname; "::"); Count(Evaluate(fieldname)); Get(FoundCount));
                   result = GetNthRecord ( Evaluate(fieldname) ; $$RecCount )];
                Result & "¶" & GetFoundSet(FieldName)) ;

                $$RecCount >= $$TotalCount ; Let ( $$RecCount = "" ; "") ;

                Let ( [
                $$RecCount = $$RecCount + 1;
                result = GetNthRecord ( Evaluate(fieldname) ; $$RecCount )
                ];

                Result & "¶" & GetFoundSet(FieldName) )
                )

                • 5. Re: Produce List of UniqueIDs in found set.
                  philmodjunk

                  As long as you need a list of values rather than a set of records (two different things here), it should do the trick.

                  And where it instructs you to put the field name in quotes, I'd use the function GetFieldName to pass the field name to the function. Not only does that avoid possible typos in entering the name of the field, but ensures that the expression will still evaluate correctly should I change the field's name sometime in the future.