2 Replies Latest reply on Mar 26, 2012 3:07 PM by DaWood

    How to display running total by parts number in other layouts?

    DaWood

      Title

      How to display running total by parts number in other layouts?

      Post

      Hi,

      I'm totally a newb in this and I have just started using filemaker for two weeks now. I tried searching in the forum to see if there's any anwsers, but I can't find any, and I hope someone can help me out.

      I want to create a parts/inventory DB where it shows the current stock level of each parts via using two tables. I have developed one layout/table for showing records of the types of part we have (ID by parts number), and another table for inventory control using the check registry concept. The parts layout/table will be the GUI and the inventory table will be the "programming." The goal I'm trying to acheive is showing the running total in the parts layout. Here's the field names and function for each layout/table:

      Parts Layout/Table

      PartsNo = Text

      PartsName = Text

      ::sBal = summary 

      Inventory Layout/Table

      PartsNo = Text

      PartsName = Text

      InQty = Number

      OutQty = Number

      cBal = InQty - OutQty

      sBal = summary of cBal (sorted by PartsNo., Date)

      DateEntered = Date (automatic, on creation)

      I have sorted the inventory table by parts number so I can get a running total of each parts number. Then I added the "::sBal" field in the parts layout to display the running total from the inventory table. The problem I'm having is that I keep getting the running total of the first record (after sorting). For example, I have three inventory record entries of PartNo 1, with InQty of 10, OutQty of 2 and 4, respectively, to get a running total of 4. I want to show in the parts layout that PartNo 1 now has 4 in stock. But the field keeps displaying 10. At this point, this would only work if you don't plan on keeping track of inventory history, but you would sacrifice the visual on supply/demand cycle. My initial thought was to sort decending by sBal to get the running total at the top, but it doesn't give me that option. Tried 'Reordered by summary field' but that only applies if you are showing all records of different parts. There could be a set of scripts that may give me that option, but I'm too much of a newb at that level. 

        • 1. Re: How to display running total by parts number in other layouts?
          philmodjunk

          I recognize this approach... Wink

          What relationship do you have between these two tables?

          Is it: Parts::Parts No = Inventory::Parts No?

          You probably need to define a second summary field will all the same options as sBal EXCEPT the running balance details and then use it on your Parts layout.

          You could also define a calculation field in Parts as: Sum ( Inventory::cBal ) and you'd get the same total.

          In some cases especially when you get large numbers of records in Inventory, a slow network such as a WAN or are using an iPad or iPhone as the client of the hosted database, you may want to define a number field in Parts with a script trigger controlled script on the in and out fields in Inventory that updates the number field with the new balance each time the value in either the in or out field is changed. OnObjectSave is a reasonable option for this. This allows you to pop up a list of Parts records and not have to wait for balances on each part number to update for each and every record in the Parts table.

          • 2. Re: How to display running total by parts number in other layouts?
            DaWood

            Hi Phil,

            Thanks! I didn't realize the sum function can work this way. 

            I'll try your latter me approach.