8 Replies Latest reply on Jun 12, 2012 2:39 PM by timwhisenant

    Copy Data from Table to Table

    MattLeach

      I'm working on an existing soultion that does quoting and invoicing in FileMaker 11.

       

      The way it is currently setup there are two almost identical tables, Invoices and Quotes. Each table also has their own line item portal.

       

      What im being asked to do i setup some kind of way to take a quote and turn it into an invoice. I've already tried to discuss this poor data structure as they are basically duplicating the data but it appears im not going to be able to change their minds on that.

       

      The only things that have come to mind are:

      1. Although not practical, setting variables and creating new record based on variables.

      2. Export the Quote locally and import into Invoices.

       

      What would be the correct, practical way to accomplish this? (Other than the obvious which would be changing data structure lol)

       

      Thanks,

      Matt

        • 1. Re: Copy Data from Table to Table
          beverly

          Matt, I ran into this years & years ago. It's NOT duplicate data (as was explained to me by the client). It's two entirely different animals. A quote is a quote, and an invoice may or may not even have the same items as the original quote (substitutions, changes, omissions, etc.) after all the work is done.

           

          Both of your methods are sound and I've used them. Do what works for you.

           

          Beverly

          • 2. Re: Copy Data from Table to Table
            timwhisenant

            Matt,

             

            To add on to Beverly’s advice. I too have tried both and there seems to be no speed difference between them.

             

            Also, avoid the idea of making the line item table do double duty, I did this only to have as much effort in workarounds as the trouble of having to script the transfer of pertinent info between the two. No time or efforts saved, with this brilliant idea. Being an accountant also, I still had to learn this the hard way.

             

             

             

            Tim

            1 of 1 people found this helpful
            • 3. Re: Copy Data from Table to Table
              pmconaway

              Why would you need to export it locally? I've manully selected the import filemenu item and then selected the current FMP file and then selected from the dialog box which table I wanted to import from. (I'm already in a layout of the table that I want to import into.) I haven't tried this in script steps but I don't see why it wouldn't be possible. Just a thought though.

               

              Paul

              • 4. Re: Copy Data from Table to Table
                beverly

                pm, I've gone the 'export-then-import route when I needed to be sure I have the correct found set. That's just be being paranoid, probably. But I know it's correct and it works...

                 

                Beverly

                • 5. Re: Copy Data from Table to Table
                  timwhisenant

                  IMHO, export is an unnecessary step when moving data from table to table. The found set is the critical thing to get right. Import will work from the found set, without the overhead of an external file created by export.

                   

                   

                   

                  Tim

                  • 6. Re: Copy Data from Table to Table
                    user17416

                    Hi Tim, I think there is more people out there trying to get this right than we know and I'm one of them.. Tel me you're talking about cimporting from table to table, what happens to data in a portal ?

                    Can it be moved as well ?

                    • 7. Re: Copy Data from Table to Table
                      timwhisenant

                      Hi Albie,

                      Yes I was referring to imports from table to table. Be careful not to defeat the purpose of a relational database in storing the same data in more than one place, Normal form Three. That is unless you have a very good reason for doing so. Archiving records would be such a case. Otherwise, create relationships and show related data thru the relationship or for point-in-time data use lookups.

                       

                      Data in a portal must be imported separately, it does not go along for the ride. It can be moved using scripting ( using script loops).

                       

                      HTH,

                      Tim

                      • 8. Re: Copy Data from Table to Table
                        timwhisenant

                        After all, data in a portal lives in a separate table, correct.

                         

                        If you seek to duplicate a record with it's portal records, do a search for duplicating parent records with their children.

                         

                        Tim