8 Replies Latest reply on Jan 20, 2014 11:14 AM by PaulWirth

    Get only unique values of a field

    PaulWirth

      Title

      Get only unique values of a field

      Post

           I'm still new to FileMaker and I'm sure there's an easy way to do this:

            

           I have data imported from a bank statement in which more than one transaction can share a common ID. What I want to do is total the transactions for each ID and make an entry into a table I have prepared for exporting as a .csv file.

           So for:

           Amount | ID

           $100     | A1

           $150     | A1

           $100     | A2

           $250     | A3

           $50       | A3

            

           I'd like to be able to create a table view that shows:

           $250     | A1

           $200     | A2

           $300     | A3

            

           Any help? Thanks in advance

        • 1. Re: Get only unique values of a field
          PaulWirth

               Sorry, that was a typo with the A2 line. The final output should be:

                

               I'd like to be able to create a table view that shows:

               $250     | A1

               $100     | A2

               $300     | A3

          • 2. Re: Get only unique values of a field
            asdadsddsdds

                 Im currently facing the same issue, i tried omitting records, but that messed up my sub-summary sum,any suggestions are very welcome.

            • 3. Re: Get only unique values of a field
              philmodjunk

                   Here are two options, you'll need to consider all possible ramifications before selecting one as the best method:

                   Option 1: Import all your data into a table. Construct a list view layout with no body part but a sub-summary layout part "when sorted by" your second field. Place a summary field in this sub summary layout part to compute the sub total for each unique value of the second field. Sort your records by that field. When you export this data, you can use the "group by" option to get one row of data for each unique value.

                   Option 2: Define two tables. Use one table to import all your data as you would with option 1 above, but then define a second table with just the second field and a calculation field. For the 2nd, category field, specify validation fields options of "unique values", "Validate always". Import your data a second time into this table. The unique values validation will automatically omit duplicates. Define a relationship between the two tables using the category fields as match fields. Define the calculation field in this second table as Sum ( FirstTable::AmountField). Export your data from this second table.

              • 4. Re: Get only unique values of a field
                PaulWirth

                     Hi Phil,

                     Thanks for your reply. I'm trying option 1, but I don't have it right.

                     I have a list view layout using the data from the transactions table. It has a subsummary part (when sorted by transaction id) with two fields: one for the transaction amount, one for the transaction id.  The transactions table is sorted by transaction id.

                     The subsummary layout does show only a single instance of each transaction id. But it isn't summing the transaction amounts for those id's (A1 should show $250, in the example). Instead of the sum, it shows only one corresponding transaction amount (A1 shows $100).

                     Is my description enough for you to tell me what I'm missing?
                      
                     Paul
                • 5. Re: Get only unique values of a field
                  PaulWirth

                       I spoke too soon. I missed the part about creating a summary field. Works like a charm now. Thank you!

                  • 6. Re: Get only unique values of a field
                    PaulWirth

                         Actually, it looks like the "group by" option in exporting is all I need. I can do away with subsummary part and summary field, and simply export the list while grouping by transaction id and my .csv shows only the total amount for each unique transaction id.

                         Which makes me curious: what does the subsummary option offer that simply exporting with "group by" doesn't?

                    • 7. Re: Get only unique values of a field
                      philmodjunk

                           You don't need the sub summary layout part, but you do need the summary field or you won't get a total of the values that make up each group of records. The only reason to add the sub summary part is to be able to see what your excel data will look like before you export it.

                      • 8. Re: Get only unique values of a field
                        PaulWirth

                             Thanks again, Phil.

                             I wound up going with your Option 2. I appreciate your help.