1 Reply Latest reply on Dec 19, 2014 7:22 AM by philmodjunk

    omit record with subsummary showing 0



      omit record with subsummary showing 0


      Hi I am currently building a warehouse inventory system. I have a table named "stock account", it functions like below:

      Itemcode                  Quantity                    Location

      item123                    100                           A

      item321                     150                          B

      item123                    -100                          A


      So I have a report showing how many stock are in a particular location, I used summary field of sum of quantity and sort the layout by location > itemcode with (1) subsummary part when sorted by location, (2) subsummary part when sorted by itemcode.

      Here is my problem, so like the example above, "item123" has been going into and out of Location A, resulting the location having 0 amount of "item123", currently in the subsummary field it will show that location A has 0 amount of item123. I would like the whole subsummary to not show at all, since I am having alot of item going in and out of place everyday, these 0 number will cause alot of confusion to my co workers.

      Currently I tried using a calculated field to dectect if GetSummary(QuantitySummary;Itemcode)=0, and omit those record, but some how it is not working as intended.

      Would appreciate any tips or pointer into this issue.

      Thanks in advance and sorry for my bad english


        • 1. Re: omit record with subsummary showing 0

          The fields that hold the criteria for which you are searching are empty of any value until the records are found, sorted and displayed in browse or preview mode.

          So you can't specify search criteria in summary field or a calculation field using GetSummary.

          You'll either need to use a different method for calculating the balance, such as Sum from a parent table of inventory items, or a looping script that loops through your records and omits all records from a given sub summary based group of records when the value returned by your GetSummary calculation is zero.