4 Replies Latest reply on Aug 29, 2013 4:06 PM by DranLang

    SHOW ALL VALUES WITHOUT REPEATITION

    DranLang

      Title

      SHOW ALL VALUES WITHOUT REPEATITION

      Post

           Hi good morning to everyone!  Just a question about displaying the values from a table.  I have a table in place which contains ID# (Field), Item Description (Field), Price (Field) and Qty (Field), I want to create a report which will display all the Item Description with its corresponding Price and Qty. Everyday of the week I create this report, so there are Item Description that will appear on some days and won't appear on some days for 6 days.  I want to display all of the Item Descrition with its Price and Quantity without having to see the same Item Description which means if there are any the same Item Description their Qty will be added all together to avoid having to see the same item displayed repeatedly.  How can I do this?

           Thank you everyone!

        • 1. Re: SHOW ALL VALUES WITHOUT REPEATITION
          philmodjunk

               What you describe is called a summary report. It requires adding both a summary field to your table and a sub summary layout part to your layout.

               Given the changes to your layout that this requires, you may need to create a new layout or start from a duplicated layout to keep your oriignal layout unchanged.

               Define a field of type Summary that computes the total of Qty in Manage | Database | Fields for your table.

               Enter layout mode and double click the "body" part label to open Part setup...

               Change the body layout part into a sub summary part "when sorted by..." and select either your ID or Description field as the field.

               Put the new summary field on your layout in place of Qty.

               Sort your records by the field you select for the sub summary layout part's "When sorted by" field and you'll get one row of data for each item with a total quantity for that specific item in the summary field.

               You can first perform a find, then sort your records to get a report for different dates or ranges of dates.

               Note that your layout will be empty unless your records are sorted correctly by the break field you specfied for the sub summary part.

               To learn more about summary reports, see:Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: SHOW ALL VALUES WITHOUT REPEATITION
            DranLang

                 Thank you Phil!  I forgot to mention that the Item Description, Price and Qty are in a portal, so  when did the steps it computes correctly for the Qty but I still see Item Description displayed repeatedly but the math is correct.  Is there anything wrong in what I did or shall I do away with the portal?

            • 3. Re: SHOW ALL VALUES WITHOUT REPEATITION
              philmodjunk

                   Yes, you can't put a sub summary layout part into a portal, this is done with a list view layout.

                   One option is to replace the portal with a single calculation field (It can still have a portal scroll bar and columns of information, but now all in one field.) this requires the new FIleMaker 12 function ExecuteSQL: FMP 12 Tip: Summary Recaps (Portal Subtotals)

                   The other option is to setup a portal to a new/different table where you only have one record for each item ID (Or description) that then links in a relationship to the table we have been discussing here. That gives you one row for each item and the relationship can make possible getting a total Qty. A portal filter or additional match fields can be used with such a portal to selectively link to different groups of records in your tables.

              • 4. Re: SHOW ALL VALUES WITHOUT REPEATITION
                DranLang

                     Thank you so much Phil!