7 Replies Latest reply on May 22, 2015 10:25 AM by philmodjunk

    Counting number of unique values in a found set

    rtolliver

      Title

      Counting number of unique values in a found set

      Post

      I have 2 layouts (A,B) that each contain a single portals. In layout (A) there's an Order no. field and there's a LineItem portal. In the portal there's a (Go to related records) button that opens Layout (B).  Layout (B) also has it's own lineItems portal. 

      Layout (A) has Order information along with a list of the items ordered in the portal  

      Layout (B) has the individual Item information  and the items details in the portal

      I'd like to count the records of the found set displayed in Layout (B)'s portal.  I have a calculation field on Layout (B), it counts the unique fields in the table occurrence for the portal.  The problem I'm having is that while the calculation counts the unique fields, it counts every unique field for the order.

       

       

        • 1. Re: Counting number of unique values in a found set
          philmodjunk

          Important detail to keep in mind when communicating with others about FileMaker. The set of records shown in a portal are NOT a found set. A found set is the set of records currently shown on the layout, not the portal. This can cause confusion when you call your set of portal displayed records a "found set".

          I started to suggest something, but realized that I couldn't be sure that I was understanding your description here.

          You clearly have this relationship:

          Orders----<LineItems

          And it would appear that Layout A is based on Orders with a portal to line items. But on what table is Layout B based? Orders? LineItems? and if Layout B is based on LineItems, why would it have a portal to LineITems, the same table on which the layout is based? Or is there a third table here that you are also calling line items?????

          • 2. Re: Counting number of unique values in a found set
            rtolliver

            There is a third table occurrence that is related to Layout Bs portal.

            Relationships:

            Layout A: Orders-----orderID---<LineItems

            Layout B: LineItems-----(uniqueID)----<Confirmed

             

            • 3. Re: Counting number of unique values in a found set
              philmodjunk

              And on layout B, you want the count of related records in Confirmed from any given line item record?

              I just don't see yet how that gets you:

              ,,, it counts every unique field for the order.

              What method did you use to try and get this count?

              • 4. Re: Counting number of unique values in a found set
                rtolliver

                Layout A: No.13983, Vendor

                Layout A:Portal: Lumber Specie, Description, QTY, BdFt., Price - An order can have 1-10 different species (Records)

                After I place the order the vendor sends me back a confirmation of how the ordered quantity will be delivered. If order 10,000 sqft of Red Oak, the vendor might tell me that my 10,000 sqft order will be delivered in 8 crates, so as I am switching between layouts I wanted a quick reference count.

                Layout B: No.13983, Lumber Specie

                Layout B:Portal: Crate No., CrateBdFt. Notes 

                 

                • 5. Re: Counting number of unique values in a found set
                  philmodjunk

                  Sorry, but this does not really answer the question that I asked. I wanted to know what method you attempted to use that was returning such a count.

                  In your example with 8 crates, do you get one record in Confirmed, 8 records (1 for each crate) or some other number of records (such as two records for two shipments of 4 crates) ?

                  Exactly what are you counting and on what layout do you need to see it?

                  A count of line items? A count of confirmed for a given line item? or ???

                  • 6. Re: Counting number of unique values in a found set
                    rtolliver

                    In my example there are 8 records and I'm counting the "Crate" field.  The "crate" field is a calculation located in the Orders table, the calculation is "Count (Confirmed::Crate)"  

                    The 8 records that are displayed in Layout B's portal represent 1 specie (Red Oak) from a possible 10 records displayed in Layout A's portal.  

                    To keep the example simple let's say 5 species (Red Oak, Wht Oak, Ash, Popular, Birch) each a record displayed in Layout A's portal. When I select the specie (Go to related records) button that opens Layout B, my Crate count returns a total for all species in the original order.

                     

                    • 7. Re: Counting number of unique values in a found set
                      philmodjunk

                      But then why put this calculation in the Orders table? If you select the Oak line item and only want a count of 8, the eight confirmed records, why not set up the count as a field in Line Items?

                      Since that seems too obvious, what am I missing here?