4 Replies Latest reply on Mar 7, 2012 1:16 PM by timwhisenant

    Newbie - How would you copy contents of one table to antoher in same filemaker file?

    cgallardo

      Hi all,

      I am trying to create a purchase order system.

      One table has the product names, another has the product family (one family can have many products as members).

      Since the products that can be ordered may change over time (either their description or not be sold anymore), I have a separate table for the POs called Orders and another called LineItems.

       

      I have a layout that is attached to the Orders table, then inside this layout I have a portal linked up to the LineItems table. The user opens up the New Purchase order layout and enters the items (the portal has drop down boxes so they can select the product), then enters qty in stock and how many to order. Then they click save and they are done.

       

      What I would like to do is have the portal listing all possible items upon load and then the user can enter how many there are in stock and how many to order. This is instead of them having to type or browse to the product in the drop down box. Initially I was going to just use autocomplete but this feature does not work on Filemaker Go.

       

      So, my question is how can I fill in the portal with all of the products from TABLE_A so they appear in the portal that is linked up to TABLE_B. I tried setting up a script to import records to TABLEB but is giving me errors, it says that there are less items on the source and then that imported so many items with 0 errors, but i can't see anything there.

       

      I do not want to use TABLEA for the ordering of items because I need to keep a history of last purchases, if an item is deleted because it is no longer sold at the store then it would dissappear from history, correct? Hence, by having 2 independent (no relationships) tables TABLEB keeps the history of items.

       

       

      Thanks

        • 1. Re: Newbie - How would you copy contents of one table to antoher in same filemaker file?
          gcatnine

          It not totally clear to me what you want to achieve.

          Anyhow, you can have two portals in the same layout: one that references TableA and the other that references TableB.

          What the user select a product in PortalA, you have a script that copies all the field from TableA to TableB and the record will be present in PortalB.

          BTW, I do not expect that the user enters the stock quantity, normally this is a system data and the user can order up to this quantity.

          • 2. Re: Newbie - How would you copy contents of one table to antoher in same filemaker file?
            LyndsayHowarth

            There are many questions here...

             

            Why are you deleting Products no longer being sold? Shouldn't you just be changing their status? Popups should be showing Product values for only Active records and not Deleted (not physically) lines.

             

            An Order is just an Invoice by a different Status. A Quote is also an Order and Invoice with a different status. This status can be used to filter (by relationships) the records being included in each context.

             

            Your TABLE data should really be in a single table. Data can be looked up or auto-enter calculations and remain intact. While you might choose a product from a popup, the data entered as a result can be locked in place. I really don't understand the value of B except as a Backup / redundancy.

             

            If you really want to do this... I would do it with an export to a temporary file followed by an import.

             

            The error you are getting when you attempt to import directly sounds like you are trying to import into a table using without the "add records" radio selected so you are trying to import eg 10 records from A into Table B to replace 20 records.

             

            - Lyndsay

            • 3. Re: Newbie - How would you copy contents of one table to antoher in same filemaker file?
              cgallardo

              Thanks gcatnine,

              The people who have asked to set this up do not want it interfaced with their stock management or accounting software. They count their stock at the end of each day, and how many they need for the next day. This piece of paper get faxed to the guy who goes to the market and does the buying (filemaker go will be used to replace the piece of paper). Hence there is no way I could get the in stock numbers automatically

              • 4. Re: Newbie - How would you copy contents of one table to antoher in same filemaker file?
                timwhisenant

                cgallardo,

                 

                so what you really need is a master shopping list that dwindles down to the items with a needed quantity. Right?

                 

                Tim