5 Replies Latest reply on Jul 31, 2009 9:15 AM by Mattpsmith001

    Copying Data in fields from one layout to another



      Copying Data in fields from one layout to another


      I trying to set up a sales order and invoicing system and I want to go from my sales order to my invoice by clicking a button and have it enter the data that is already in the sales order. I set up a layout for the sales order and duplicated it for the invoice. I then added a few extra fields that I needed on the invoice. I have a portal set up so i can enter in items and pull it out of my inventory. I would like to be able to copy the items in the portal of the sales order and paste them into the portal of the invoice. I also need it set up so I can enter stuff in the sales order at a later time and it wont be reflected in the invoice. I know i need some kind of script but I have no idea where to even start with writing it. Please help me if you can


        • 1. Re: Copying Data in fields from one layout to another

          Howdy Matt,


          Thanks for the post, it is intriguing toward the heart of relational databases.  You can do it the way you describe...but why would you want to?


          Rather than answer your question as stated, let me point out that one of the great strengths, some might even say the whole point, of a relational database is to not need to do what you're trying to do.  The efficiency comes from not having to double/triple/quadruple enter data, even if the data copy is by a simple cut&paste.


          I would encourage you to rethink the structure and design of your Dbase so that the copy & paste from the "sales order" table to the "invoice" table is accomplished instead by relating the two tables appropriately and avoiding the replication of data.  Whenever you have to copy&paste within the Dbase, you'll want to think hard about why you have to, and search for the way that makes it unnecessary.


          I envision that each sales order has an invoice, and that each sales order line item (child table here) has the option of billable or not billable (radio or checkbox).  The invoice is then made up of those items on that particular sales order which are also billable.  Not knowing much more about your app., I'll leave it vague...but I trust you see my point.


          Hope this helps you make your Dbase more efficient and effective...

          • 2. Re: Copying Data in fields from one layout to another

            Hi Ninja,


            Thank you for your response. If I could do it using relationships I would definitely rather set it up like that. I have tried to set it up using relationships and it works fine except when i need to do a change order on the sales order. Basically I have my sales order which has the total amount of the sale and then I have an invoice that shows the deposit amount that the customer pays that day. In the line of work I am using this so the customer can add or remove items to the sales order so I need to be able to reflect that on the sales order without it changing the deposit invoice. Every time I try to set it up using relationships when I make a change to the sales order it makes a change on the Invoice.  


            I do not have any checkboxes on my layout. I am not sure how that would help. Maybe you could elaborate on that. Here is a quick rundown of how my database is set up.


             I have a contact table where I store all of my contacts and have many portals set up to display there sales orders, invoices, and work orders. From my contacts I have a button to create a sales order and that button pulls all of there contact info and puts it on the sales order. This is done through a relationship. It works great. My end goal is to go from the sales order to the deposit invoice without having to reenter anything in. I also need to be able to make a change on the sales order without it reflecting on the deposit invoice. I then go from a deposit invoice to a work order. And then from a work order to a purchase order. I have all of my relationships set up and everything works fine except the invoice part. 


            Any suggestions would be helpful. Thank you



            • 3. Re: Copying Data in fields from one layout to another

              Howdy Matt,


              At the end of the day, each has to decide what works and doesn't in their particular app.  That said, let me point you in a couple of directions and see which is most appropriate:


              A checkbox, radio button set or dropdown value list could tag the line item as to its various status options.  All of these methods should be based on a fixed value list.  I'm guessing that some status options might be:


              Covered under deposit invoice / not covered under deposit invoice (ie. field would be "covered" or blank)

              work order issued / not issued  ("WO issued" or blank)



              Now when you work out your deposit invoice, those items on it would be tagged with "covered", and your relationship would be dependant on both your current relationalID AND the deposit invoice "covered" field.  When you now add another line item, it won't affect your deposit invoice since the "covered" field is blank.  Essentially, you could use the relationship to filter out the related ID#'s to include only those that are "covered".  Stick a global in the deposit invoice table and set it to "covered" during your startup script.


              line itemID#      >-----<   depositinvoiceID#

              InvoiceCovered             globalInvoice Covered



              For what you originally asked, if it is indeed considered the most appropriate:

              Trap the values you want to copy into variables

              Set Variable[$variable1;Table1::Field1]

              Set Variable[$variable2;Table1::Field2] etc.


              Go to a layout based on the destination table,


              then paste away...

              New Record/Request

              SetField [Table2::Field1;$variable1] etc.


              You can see how redundant this would be, but if it works well...just watch your database perfomance and speed and size.  You can get away with some fairly plodding actions when the Dbase is small...they might come back and bite you when it gets larger (thus my original reply).


              I'm pressed for time today, but I wanted to make sure I gave at least some info before the weekend.  Others, feel free to expound, clarify, disagree...whatever.  Have a great weekend.

              • 4. Re: Copying Data in fields from one layout to another



                I like the first recommendation you gave me. I also worry about the copy and pasting approach as my database gets larger. I will give it a shot and let you know how it works. Thanks for the info.



                • 5. Re: Copying Data in fields from one layout to another



                  Just wanted to let you know your first suggestion works great. Its exactly what I wanted to do and I am not copying and pasting. Thank you for all of your help.