3 Replies Latest reply on Dec 1, 2015 3:23 PM by Extensitech

    Script for Copying Data to another Table

    njudson6

      Hello All,

       

      I am trying to find a cleaner way to Copy data from one table to another.

       

      I have two tables. "Quotes" and "In House Jobs"

      Each table has the same exact fields and I want to be able to "Convert" a Quote into an In House Job.

      I currently am achieving this by running a script off a button by simply going back and forth to each table and copying and pasting.

       

      Is there a cleaner script I can write that would do this?

       

      Thanks in advance for your help!

        • 1. Re: Script for Copying Data to another Table
          Extensitech

          Three different ways come to mind (all using scripts, of course):

           

          1. Isolate the record in Quotes, then import it into Jobs, mapping appropriately
          2. Assuming there's some relationship between the Job and the Quote it came from, make these fields in Jobs auto-enter a looked up value from the quote. That way, when you enter the foreign key in quotes, all those fields will fill in on their own.
          3. Again, assuming some relationship, go to Jobs, create the new job, then do a series of set field steps, where you "Set Field [ Jobs::fieldA ; Jobs_Quotes::fieldA ]"

           

          There are clearly a number of steps involved in each of these, but I don't know how much you already know, so I don't want to go into excruciating detail if this is enough, or if you understand most of what I'm talking about. If one of these approaches seems like it'll do the job for you (see what I did there?), then let me know if there are specific questions I can answer for you.

           

          Chris Cain

          Extensitech

          • 2. Re: Script for Copying Data to another Table
            wimdecorte

            Anytime you find yourself having two tables with pretty much the same data, you have to question the architecture.  Sounds like you could use just one table and a flag field that is set to either "quote" or "in house job"?

             

            As to moving data around: copying and pasting is not a good way to do it (it destroys whatever the user already on the clipboard) and in general you can save time by collecting the data once and then moving only once to the target to set the data.

            Data can be collected in global fields, variables.  Or if you have a lot of data to collect you can also export and import.

            • 3. Re: Script for Copying Data to another Table
              Extensitech

              wimdecorte wrote:

               

              Anytime you find yourself having two tables with pretty much the same data, you have to question the architecture.  Sounds like you could use just one table and a flag field that is set to either "quote" or "in house job"?

              Excellent points, all. (Of course. It's wimdecorte )

               

              I immediately associated this with movements I end up doing between things like quotes, sales orders, shipments, purchase orders, receipts, and so forth. Since the relationships between these aren't 1:1 (they often end up being 1:M or even M:M), a status field won't do it. But you're right, of course; if it's one quote becoming (or not becoming) one and only one job, the second table is superfluous.

               

              Chris Cain

              Extensitech