5 Replies Latest reply on Aug 6, 2017 7:16 PM by alexp

    need help with formula. new to FMP


      Hi all, I have recently begin to use fmp. I am currently using FMP to develop an inventory system.


      I am trying to use a portal to display the total data from said item and use that to - off when a item has been used.

      problem I am encountering is the portal is only displaying values from ID 1 even when I use the summary calculation.


      I will have x2 ID say 1 and 4 and it will display the total summary count on that layout. but when I view the layout with the portal the only data shown is that of ID 1.


      I have a delivery layout, usage layout and a storage layout I'm trying to summarize with the delivery layout and - off the usage layout. while displaying both counts in the storage layout using portals.


      if any has any advice or able to talk me through the process would be greatly appreciated.

        • 1. Re: need help with formula. new to FMP

          Before I hand out useless advice based on my own assumptions about how your database is organized, let me ask if you're using something like this as your table structure:


          Inventory Tables.jpeg


          In this structure, InSeq1 is the primary key for your Inventory table, ProSeq1 for the Products table, and BeSeq1 for the Beings table (the place where you store the name and contact information for your suppliers and customers). InOrOut can take on either of 2 values, "In" for products received from a supplier or "Out" for products sold to a customer. Unit Price would be auto-entered at the time an Inventory record was created, and Value would be calculated as Quantity * Unit Price.


          So, for any given product recorded in the Inventory table (say "blue widgets"), you'd have a bunch of "In" records that would drive the summary total for blue widgets up and a bunch of "Out" records that would draw it down again. Stock of blue widgets on hand would be simply the sum of all the "In"s minus the sum of all the "Out"s that shared the ProSeqI code for blue widgets.


          Is this the sort of model you're using? (Bear in mind that this is something I cobbled together for screenshot purposes in less than 15 minutes, so it leaves out a whole lot of detail and sophistication, but is it the same general idea?)

          • 2. Re: need help with formula. new to FMP

            basically, similar but I think I have over complicated it the process.

            so the concept I have been using is
            -Link Delivery, Storage, Preforming in a ID relationship

            -Data is entered into (Delivery table) E401 and then Summarized in SummE401, I then use a global storage option in E401Calc. I use same process for (table prefoaming)
            -I then use (Storage Table) I set E401 to a calculation field that is as follows

            -(E401 Storage Calculation) Delivery::E401Calc-Prefoaming::E401 Copy


            I am fairly certain I have over complicated things but it does work for what I need at this point.
            any advice to revaluate my process and convert it to a more simple solution will be appreciated.

            • 3. Re: need help with formula. new to FMP

              It appears to me you have set up the relationship incorrectly. Each table should have its own uniqueID field, which you have in all three tables shown—which should always be a unique value. However, to create a relationship from one table to records in another table—in your example, from Delivery to Storage—you need to have an additional foreign key field. So i this example, you would have field called, say, fk_deliveryID, into which would be entered the ID value from the Delivery table to which a Storage record is related. By matching as you have the primary ID in each table you will only ever get one record to match, whereas if you use Delivery::ID = Storage::fk_deliveryID, you will get as many storage matches as relate to the foreign key. This is because fk_deliveryID does not need to be unique.

              Looking at your list of field names it appears there are other issues too, but the above addresses only the fundamental one of getting relationships working.

              1 of 1 people found this helpful
              • 4. Re: need help with formula. new to FMP

                Okay that makes more sense, I will give it a try and see how it works out.
                all advice at this point for me is good advice.

                • 5. Re: need help with formula. new to FMP

                  also note I'm trying to create live feed of data into storage that doesn't require the event of creating a new record.