1 2 Previous Next 15 Replies Latest reply on Apr 29, 2016 1:42 PM by dsvail

    Making production plans in portal with products and amount

    Zerojojo

      You good people,

       

      I am sure the title is only confusing but I could not sum it up in one sentence.

       

      I am trying to use filemaker to make weekly production plans, so I want to be able to create a new production plan that sees all my products (and maybe filters them according to search criteria) and I want to enter an amount after each product. This sounds fairly simple, but I cannot figure out how to set up the amount so that I can have a different one for each product on one production plan as well as a different one for the same product on a different production plan.

       

      I am thinking that I need three tables, one for the production plans, one for the products and one for the amounts. Then I create a portal from the production plan to see all the products. But I can't figure out how to connect the amounts correctly.

       

      I'll attach a dummy for demonstration where I could set a different number for each product, but then the same number will be used for that product in all production plans.

       

      Any help is highly appreciated. I'm sure the answer is blatantly obvious to somebody smarter than me.

       

       

      Best regards,

      Johannes

        • 1. Re: Making production plans in portal with products and amount
          dsvail

          Johannes,  if I understand your issue correctly, you need a Join Table between the Production table and the Product table (as it is a many to many relationship between Products and Production ...ie. a production record can have many products and a product can be on many production records).

           

          Create new ProductionProduct table that has foreign key from both tables it will join.  the Amount field is in this table. 

           

          modified your Production sample file as an example.

          • 2. Re: Making production plans in portal with products and amount
            erolst

            I am thinking that I need three tables, one for the production plans, one for the products and one for the amounts.

             

            That is correct.

             

            You need a join table that combines a product and a plan, and lets you set an amount for that combination. Usually, this structure is illustrated like so:

             

            Plan --< ProductInPlan >-- Plan

             

            Every record in that join table states: this product (as referenced by a foreign key) has this amount (a field to fill in) in this plan (as referenced by another foreign key).

             

            Compare the constellation above with your file: you've had the right idea (add a dedicated table), but the implementation is wrong.

            • 3. Re: Making production plans in portal with products and amount
              erolst

              In case you're interested, here's a modified version of another sample file. It has not only the correct structure, but also a user interface to conveniently add products to plans (or remove them again).

              • 4. Re: Making production plans in portal with products and amount
                Zerojojo

                Thanks so much. You guys are really awesome. I am aware of the join table approach, but what I don't like about it here is that it forces me to select each Product and add it manually. I was looking for a way to see all my products and then just add a number to the ones I want to have in this week's plan. This way we can also display and sort according to our current amount in storage and other criteria before we decide on the amounts. Would there be a way to do that?

                • 5. Re: Making production plans in portal with products and amount
                  dsvail

                  Johannes,  I guess you could script it so that when you create a new Production Plan it automatically loops through the product table and creates all new Join Table records for all products ...with empty amounts. (how many products are we talking about?)  You could then enter the amounts later.   When you are done entering amounts, probably would be a good idea to loop through the related records in the join table deleting any that have the amount field empty.  Maybe have a status field in the ProductionPlan table, where changing the status of the weeks production plan form open to closed runs a script to clean up the join table.

                  • 6. Re: Making production plans in portal with products and amount
                    Zerojojo

                    Thanks, dsvail. I understand your approach. We have around 250 products and would mostly like to use this over internet connection to our server which doesn't sit behind the fastest connection. I would be a little worried about speed since we already see some performance dips with scripts that seem less intensive.

                    Is there really no simple and fast way to do this? After my first approach (the dummy) I was sure I am just missing a single step to get this to work.

                    • 7. Re: Making production plans in portal with products and amount
                      dsvail

                      could use Perform Script on Server

                      • 8. Re: Making production plans in portal with products and amount
                        Zerojojo

                        Thanks again. I am really bad with loop scripts. Would this require a Magic Key to create the related records? I have never really figured out how to loop through related records and store their information on another table without using Go to layout back and forth, which would gather all instead of only the related records and would probably result in a blink-fest.

                         

                        My apologies for simple questions. I am learning by doing. As a food chemist, this is quite a stretch from my regular problems, but we don't have anybody else to do this.

                         

                        Much appreciated,

                        Johannes

                        • 9. Re: Making production plans in portal with products and amount
                          alangodfrey

                          Why do you need a join table?  Do you not need just two tables, Products and Production Plan?  Search the Products list for all the products you want in this week's Plan.  When you've isolated the right list, click your 'Create Production Plan' button, ask the user for the Production Plan Week Number (or date, or whatever you use to identify 'A Production Plan'), and import those Products into your Plan, attaching the Week Number ('Date', whatever) to each imported record.

                          Your script then flicks over to the Production Plan; you don't even have to do a 'Find' for those records - Filemaker helpfully isolates the imported records automatically for you.  You can then enter the amounts manually.

                          You will always be able to isolate any particular Plan from that table.  There is no big network load with that - each is only an import of 250 records (max).

                          • 10. Re: Making production plans in portal with products and amount
                            erolst

                            <<I am really bad with loop scripts.>>

                             

                            So don't loop; when it comes to record creation, importing is faster anyway:

                             

                            • create a new Plan record

                            • store its ID

                            • go to a Products TO

                            • Show All

                            • go to the JoinTable

                            • from the Products TO of step 3, import Products::ID into JoinTable::ID_Product (primary into foreign)

                            • use “Replace Field Contents“ to set the foreign Plan key for the import set

                             

                            The last step shouldn't cause locking issues because no-one except you knows these records exist yet … ;-)

                             

                            Alternatively, you could:

                            • store the new Plan ID in a $$variable

                            • define an auto-enter calc for JoinTable:ID-Plan as $$ID_Plan // the $$var from step #1

                            • 11. Re: Making production plans in portal with products and amount
                              Zerojojo

                              Thanks! How do I import records via script from another table of the same database? The Import Script only allows me to import from another file or folder.

                              • 12. Re: Making production plans in portal with products and amount
                                Zerojojo

                                Ah, sheesh, I can just select the server as an import source. Got it!

                                • 13. Re: Making production plans in portal with products and amount
                                  dsvail

                                  Johannes,  here is your sample file modified. Think this is what you were thinking....something to play around with at least.  Has Portal showing all products with field for the amount that you can keep open until you want to actually push those products and their amounts to the ProductionPlan for the week.  Uses looping script to go through the products that actually have amounts and creates new record in the join table (setting the planID, productID, and the amount).  It then clears the amounts in the Product table ...sets all amounts for each product to 0. ...ready for your next plan.

                                  • 14. Re: Making production plans in portal with products and amount
                                    Zerojojo

                                    The help in this forum is really incredible. Thanks so much, again!

                                    1 2 Previous Next