7 Replies Latest reply on Jun 12, 2012 9:19 AM by philmodjunk

    Updating or creating join table records as needed

    PeterThorton

      Title

      Updating or creating join table records as needed

      Your post

      Hello,

      my question for today involves join tables, but in a way I have not used them before. My situation:

      The company I work for is a distributor, so it has shipments going in and out at all times.

      I have a table called Product, which has an InStock field, that keeps the current stock amount for each individual product. When a shipment comes in, InStock value is increased for the relevant products. Likewise, when a shipment leaves the company, InStock values are decreased.

      However as it turns out, the company has multiple warehouses. Other than keeping track of the total amount in stock, I need to keep track of how much stock is in which warehouse. I've made a simple data structure for this (pictured)

      It is basically a simple join table, that joins Product with Warehouse and specifies how much stock of the given product is in the given warehouse. When a shipment comes, the user will specify which warehouse it will be stored at. Likewise, when a shipment is about to leave the company, the user will specify which warehouse it will be taken from. Should be easy enough.

      I've worked with join tables before, I had the classic Product >---< OrderItem >---< Order situation. But this is a bit different. With orders, when a new order came, I just created the OrderItems, set their amount and price values, and then left them lying in the database so that reports could be printed based on them.

      However, when a certain amount of a product arrives in a certain warehouse, I can't just create a new StockInWarehouse record. What if there already is one? In that case, I should just increase the amount value for that record. Otherwise I would end up with multiple entries, each stating something different about the actual amount of stock in a given warehouse.

      I can't quite figure out how to write a script that would do this. Any advice would be appreciated.

      warehouse_ds.png

        • 1. Re: Updating or creating join table records as needed
          philmodjunk

          Note: You've posted your question in the FM Server portion of the forum instead of the FM Pro section. (see tabs at top of screen).

          Posts in Fm PRo Forum get a lot more views so you are more likely to get help when you post a question of this type over there.

          You might search this forum for threads with the phrase "Inventory Ledger" for a look at how to set up a "ledger" system where you can enter a new record each time product is received and each time product is removed. The result works just like a bookkeeping ledger with "in" and "out" fields instead of "debit" and "credit" fields. To compute Qty in stock for a given product, to total up all the "in" amounts and subtract all the "out" amounts using summary fields and/or the Sum function. A script can also refer to these same fields to update a number field for Qty in stock in your product table--which can produce a more responsive layout when reviewing a list of products and their current inventory levels.

          Here's the fields you might use in your join table to turn it into a ledger:

          TransDate (Date)
          In            (Number)
          Out          (Number)
          ProductID
          TransDescription (text--optional but allows you to document the type of inventory change)
          cBal         (Calculation: In - Out )
          srBalance (Summary: Running Total of cBal, restart totals when grouped by ProductID)
          sBalance  (Summary: total of cBal.)

          • 2. Re: Updating or creating join table records as needed
            PeterThorton

            Whoops, sorry about the wrong forum.

            • 3. Re: Updating or creating join table records as needed
              philmodjunk

              It's an easy mistake to make given the interface design provided by RightNow. (And one that ModMan--our forum moderator--has requested that they improve...)

              • 4. Re: Updating or creating join table records as needed
                PeterThorton

                Let's say I accepted your proposition and will go with the inventory ledger option. I will, however, need to add a WarehouseID to the ledger table (the core problem I was trying to solve with this question is how to handle multiple warehouses) So when a shipment comes to a specific warehouse (it always comes to one specific warehouse), all the ledger entries will have this warehouse's ID. So when I need the amount of certain product in certain warehouse, I'll just sum up all the balances for the given ProductID and WarehouseID. I could easily write a script that does it, but I have a feeling this can also be done with a summary field. I don't quite understand how the summary fields work yet, especially the grouping part. Could you give me a hint as to how these summary fields should be set up? Thanks.

                • 5. Re: Updating or creating join table records as needed
                  philmodjunk

                  I will, however, need to add a WarehouseID to the ledger table

                  Yes, you will. I accidentally omitted that field in my example.

                  When you sort records on a field such as a ProductID or a WareHouseID, you group the records by that common value so you can set up a sort order that groups the records first by ProductID and then again by WarehouseID or you can reverse the fields and sort by WarehouseID and then by ProductID.

                  Either way, you can use a summary field to get the total product for a given ID in a given warehouse as well as using the same field to get a total on hand for a given product from all your warehouses combined.

                  You can add a sub summary layout part to a list view layout, specify "ProductID" as the "when sorted by" field and if you sort your records by productID, your sBalance field will give the total product for each group of records with the same ProductID. Put the same field in a sub summary part "when sorted by" Warehouse ID and include that field in your sort order, you can get a total of records present in a given warehouse.

                  And if you remove the body layout part--just keeping the sub summary parts, you can get a report with one line for each productID grouped by warehouse with subtotals in each line.

                  Here's a tutorial on summary reports you can play with if you are interested: Creating Filemaker Pro summary reports--Tutorial

                  • 6. Re: Updating or creating join table records as needed
                    PeterThorton

                    Thanks, I'm not yet entirely sure how to set the summary fields to be sorted by two different fields, (there seems to only be one field you can sort by in the summary field options) but I think I'll figure it out. But you write about using these summary fields in listview based reports. Is there any way to put them in portals?

                    For example, I have a Product Management layout, and I want a portal on it that shows which warehouses the product is stored in and in which quantities.

                    Likewise, on my Warehouse Management layout, if I select a layout, I want to see a list of all products that are stored in it and their quantities (it's gonna be a long list, I know)

                    • 7. Re: Updating or creating join table records as needed
                      philmodjunk

                      In a summary report like I have described, you do not use the running total options. That's why I describe two summary fields--one with those options--for use in a ledger style layout and one that does not have them--for use in a summary report.

                      You can set up different sub summary layout parts and use the same summary field to get different subtotals. Take a look at the summary report tutorial as it illustrates this and how, by playing games with the sort order, you can actually get different reports from the same layout.

                      Such a summary report can't be displayed in a portal, but you can get the results you want if you define relationships that will support what you need. When you refer to a summary field in a related table, you get a value based on all related records instead of a group in a found set like you do from the summary report layout. Aggregate functions such as Sum () and Count () can also be used to get the same results.

                      You could put a button on your products layout that pops up the summary report in a new window or just takes you to that layout. WIth the right layout design and scripting, the report can list records only for that product and you can get one row for each warehouse with the totals in each.

                      You can also add relationships to your basic set up like this:

                      Product---<StockInWareHouse>-----Warehouse
                           |
                           --<AllWareHouses----<StockByWareHouse

                      Product::anyField X AllWareHouses::anyField

                      AllWareHouses::WareHouseID = StockByWareHouse::WareHouseID AND
                      AllWareHouses::gProductID = StockByWareHouse::ProductID

                      AllwareHouses and StockByWareHouse are added occurrences of WareHouse and StockInWareHouse respectifvely.

                      gProductID is a field defined in WareHouse with global storage specified.

                      Add an OnRecordLoad script trigger to your product layout with this script:

                      Set Field [WareHouse::gProductID ; Product::ProductID]
                      Commit Record[]

                      With those relationships, you can put a portal to AllWareHouses on your Product layout with the non-running total summary field from StockByWareHouse plus a field from AllWareHouses to identify the WareHouse and you'll get your portal with counts for each warehouse.