9 Replies Latest reply on Feb 5, 2010 12:32 PM by philmodjunk

    Summary field Total of restricted by subset of records

    liquidcooled

      Title

      Summary field Total of restricted by subset of records

      Post

      Hello,

      I have a multiple location inventory data base that I created. I need to generate reports that show how much inventory was in each location on a specific date in the past. I'm using a Sub summary report which shows Location and then item totals in that location. The item totals are generated by a summary field that totals the running balance. The table that it is based off of keeps track of each items movements in and out in a separate field for items in and items out then I have an item balance that subtracts the in from the out and then a summary field that totals.

      How do I restrict this total to only total up to a certain date? 

        • 1. Re: Summary field Total of restricted by subset of records
          philmodjunk
            

          You can perform a find on the date field to omit records outside a given date range. You can enter date ranges as Date1...Date2 or with an inequality > Date. Just be sure your expressions refer to either a global date field, a variable with a date or a literal expression that evaluates to a date (Date (1, 1, 2010) not "1/1/210".

           

          However, since these are running totals, how can you make sure you have the correct totals if you exclude some of the records?

          • 2. Re: Summary field Total of restricted by subset of records
            liquidcooled
              

            Assuming my logic is correct it should be ok to exclude some of the records as long as it is only for a single date and not a date range. What I need to do it total up all the records to a certain date, and then omit all records after that date. 

             

            I'm a bit confused how to make this work in a sub summary report, because I don't and wouldn't have a date field in the sub summary report it only shows a summary of the location and then items in the location and finally for each item the summary field that shows the total of the items in that location. I think that I need something in between that finds all the records up to the date selected by the user and then uses the summary field to total all those records up to that date. Then create the sub summary report based on those records. I'm just not sure what the in between stage should be or how to create it.

            Thanks 

            • 3. Re: Summary field Total of restricted by subset of records
              philmodjunk
                

              I think you need a date field here. Not only does that enable you to do the exclusion by date that you want, but it can be used by you to sort your records correctly by date--which is a pretty typical requirement for this kind of report.

               

              Since any given entry in your table may effect the running balance, omitting any records from your found set may alter the totals reported by your running total summary field. The only way this would not be the case, would be if you omitted all records prior to and including an entry that produces a running total of zero.

               

              Consider these values:

               

              in    out    Run Bal

              1                1

              2                3

                      1        2

               

              If I omit the any one entry, I get a differnt value in my running balance field.

               

              Now look at these:

               

              in    out    Run Bal

              1                1

              2                3

                      3        0

              1       1        1

               

              I could omit the first three entries and get the same running balance as all those entries add up to zero. Any other combination of omitted records will give me a different balance.

              • 4. Re: Summary field Total of restricted by subset of records
                liquidcooled
                  

                I guess I didn't put that in there I do have a date field. Maybe some pictures will help explain where I'm coming from. I should also note that I'm very new to filemaker so there might be a very obvious fix that I didn't think of or know about.

                So here is my field list and an image of the report that I have now that shows at the top the location name Item name and then cItemBalance field. the report is sorted by Location then Item. 

                Thanks Tim

                 

                item_transaction fields 

                inventory by location 

                • 5. Re: Summary field Total of restricted by subset of records
                  philmodjunk
                    

                  The field names are very similar to some example posts that I have made in the past. I'm very familiar with this approach to managing inventory in Filemaker. The information in your last post doesn't alter my original comment that excluding records from your found set may produce an incorrect inventory balance for a given item.

                   

                  The only case where this won't happen is if all the excluded records have a net inventory change of zero for a given item or if you exclude all the items with a given ItemID. Neither of these is can be done simply by finding all records in a given range of dates.

                  • 6. Re: Summary field Total of restricted by subset of records
                    liquidcooled
                      

                    I think that It's not a date range that I'm looking for, but rather a slice in time. I want to see what was in the inventory on 1 particular day in the past. So there should be a way of creating a sub set of records that shows all the days leading up to the 1 day I want data for and then totaling all those records to that one day. It would be like going back in time for the running total to a previous date when the totals added up to that amount. I agree a date range would not work, but finding out what was in inventory on 1 day should be possible.

                     Right?

                    • 7. Re: Summary field Total of restricted by subset of records
                      philmodjunk
                        

                      Yeah! that we can do!

                       

                      Let's say we want to see inventory totals current on Jan 31, 2010.

                       

                      Enter find mode and use this criteria:

                       

                      < 1/31/2010

                       

                      if you perform the find by hand.

                       

                      using a script, you could enter the date in a global date field; gAsofDate (select global storage in field options), then click a button to...

                       

                      enter find mode []

                      Set Field [YourTable:: Date_Printed; "< " & gAsofDate ]

                      Set error capture [on]

                      Perform find []

                      sort [restore]

                      • 8. Re: Summary field Total of restricted by subset of records
                        liquidcooled
                          

                        This seems to be the correct answer but I'm having a problem with the script. It is entering the value, but for some reason not performing the find and then also not doing the search. If I hit the perform find button in the tool bar at the top it does the find and then I have to have it sort the records to show the right answer.

                         

                        I used your script idea exactly except made it no dialog for both the perform find and sort restore. 

                         

                        Any ideas on what I for got?

                        Thanks 

                        • 9. Re: Summary field Total of restricted by subset of records
                          philmodjunk
                            

                          Make sure you don't see [pause] somewhere in your script.

                           

                          The script should do everything without stopping in find mode.