11 Replies Latest reply on Feb 14, 2013 8:47 PM by learner

    Copying contents of tables

    cgallardo

      Hi,

      I am still working on my first project.

       

      I have set up one table that contains a list of all products (AllProducts) and a couple of other tables, one has Order Numbers and another has LineItems. Relationships only exist between Orders and LineItems. AllProducts is standalone.

       

      Although the AllProducts table is supposed to have the ability for the user to add/ remove/rename items, when the user does change the items in AllProducts it should not alter past orders.

       

      What I would like to achieve is for everytime a a user wants to create a new order, to have a layout that displays all possible products and then the users juts adds the qty of each item.

       

      I tried setting up look ups but it didn't produce the desired result.

       

       

      Basically I need a way to copy the whole contents of the AllProducts table as a new order with Qty = 0 for each item. I am not concerned that my orders will have items with a zero qty.

       

      Any ideas?

        • 1. Re: Copying contents of tables
          learner

          I might not be understanding you correctly, but it sounds like something you could do with a drop-down on the input field, using a value list based on the field in All Products. Does that (a) make sense, and (b) meet your requirements?

          • 2. Re: Copying contents of tables
            cgallardo

            Hi Learner,

            Yes, I have done this with drop downs and pop up boxes and also with look ups but they do not do whta I am trying to achieve.

             

            I really need to have this so that it will show all possible products in a portal and then the user just fills in the quantites for each.

            • 3. Re: Copying contents of tables
              LyndsayHowarth

              I suggest that you create a script which creates an order then copies the order ID then imports the Products into the lineitems table and pastes then fills (using replace field contents) the Order ID into the imported records.

               

              HTH

               

              - Lyndsay

              • 4. Re: Copying contents of tables
                cgallardo

                Thanks Lyndsay,

                I already have a script that creates the order (for other reasons), so for the other steps are you suggesting something like this:

                 

                1. Store the Order ID into a variable

                2.Importing Products into the LineItems Table (this is where I am really stuck, this is to be accessed from an Ipad using Filemaker Go so I don't think i could export to a file and then import back again?)

                3.I think I understand the part of replacing the Order ID with the variable from step 1 and it makes sense, but how do you step through all the items when the number of items is dynamic.

                • 5. Re: Copying contents of tables
                  learner

                  You can do an import-without-doing-an-import by giving the products sequential numbers in their original table. Have your script do a found count of the original table, go to the LineItems table, and set up a loop in which each step creates a new record, populates the necessary linking field(s), and increments a sequencing field by one each time. Exit the loop when the number of records you have created equals the original found count. Have a relationship set up between the product database table and the line items table, so that part of the link is the sequential number. That way, each of the new records you have just created will relate to one record of the original table. Then replace the item name field in the line items field with names from the related products table.

                  Hope this makes sense. I've just done this recently for a survey system, where each respondent needed a fresh bank of questions installed, and where I couldn't use an import because the system was web-deployed. If my explanation was too confusing, I'll include the coding I used next time.

                  • 6. Re: Copying contents of tables
                    cgallardo

                    Hi Learner,

                    Your project sounds a lot like what I amtrying to accomplish

                    If it is not too much trouble are you able to post the script?

                    • 7. Re: Copying contents of tables
                      learner

                      I think this will do what you want. The only thing to remember is that it only works because "Item sequence" field in the "Products" table is populated with sequential numbers.

                       

                      Have fun!

                       

                      John

                       

                      Relationships.jpgScript.jpg

                      • 8. Re: Copying contents of tables
                        cgallardo

                        Thanks John,

                        I used your examples with a few variations and it gave me what I needed (basically to populate a portal with predefined values).

                        Instead of using replace field contents I used Set Field and had to within the loop return to the layout that had my products list.

                        Also inside the loop I had a Go To Next record every time it opened up the Products layout.

                        I didnt have to worry about setting sequential numbers for my products?

                         

                        I also added a check at the start where if the portal isalready populated  the script won't run (to save duplicating items in an order).

                        • 9. Re: Copying contents of tables
                          learner

                          I'm impressed. Did you say that this was your first project? Does it run too slowly going back to the Products layout each time it loops, or does it run OK?

                          • 10. Re: Copying contents of tables
                            cgallardo

                            Yes, it is my first project. I have been at it for a few months on and off.

                             

                            I added a Freeze Window step at the start of the script to prevent it from jumping between layouts until the script finished doing what it had to do. This way it goes fairly quickly.

                            One problem I realized I have now is that the first line in my portal is always blank. Any ideas?

                            • 11. Re: Copying contents of tables
                              learner

                              I'd need to see your script to work out why it is doing, that, but you should be able to fix it with:

                              go to portal row 1

                              delete portal rom (perform with no dialogue)