1 2 Previous Next 15 Replies Latest reply on Mar 6, 2013 11:15 AM by philmodjunk

    Script to Copy or duplicate data from Portal Row

    Alejosor

      Title

      Script to Copy or duplicate data from Portal Row

      Post

           Hello:

           I have a table call "Job orders" and other called "Quotes". The two tables take data from "Clients" (from a table called "Clients Billing") and "Products" (from a table called "line items")

           Line items is used on a portal row.

           I need to create a script that could copy or duplicate some field from "Quotes" into "Job Orders"

           Right now, I can select a "quote" and create a new "job Order". All the Information from "Clients" is copied properly but the data that is in the portal row is not copied.  

           Please see below the relations and the script. I know that i have to create a loop but not sure how.

           Any help will be great.

      Relations.jpg

        • 1. Re: Script to Copy or duplicate data from Portal Row
          philmodjunk

               But what fields into which portal? A portal based on which table occurrence?

               And this script is performed from what layout?

               And for which Job Order?

               The relevant relationships appear to be:

               Quotes------<customers Billing>-------Job Orders-----<Line Items>------Related Products

               Can you explain the match fields used in the Quotes-----<Customers Billing Relationship and why they are set up the way that they are? I see an extra match field there that appears to represent this relationship:

               Quotes::QuoteID = Customers Billing::QuoteID AND
          Quotes::Staff 2 ID = Customers Billing::QuoteID

               I can't follow the logic for the pair of match fields shown in bold face here.

               This is a many to many relationship with customers Billing serving as the join table. In theory, any given Quotes record could be linked to any number of Job Orders records and any given Job Orders record could be linked to any number of quotes so simply identifying the "right" quote record and the "right" Job Orders record is the first challenge here.

          • 2. Re: Script to Copy or duplicate data from Portal Row
            Alejosor

                 Hi, I'm sorry about the confusion.

                 1. Table called Quotes. This table takes information from "clients" and from "Products". The information taken from clients is saved in another table called "Customers Billing" so if I decide to delete a client from the client table, wont; desapear in Quotes. The information related to products is saved in "line Items 3 an occurrence of "Line Items".

                  

                 2. Table called Job Orders. This table takes information from "clients" and from "Products". The information taken from clients is saved in another table called "Customers Billing" so if I decide to delete a client from the client table, wont; desapear in Job Orders. The information related to products is saved in "line Items". This table is also related to invoices. If a job order is done, then we have to issue an invoice. This relation works fine.

                 What I want is that if a quote is approved I can be able to create a new "job order" with the information from "Quotes".

                 The information for Quotes::Staff 2 ID = Customers Billing::QuoteID is used for other purpose (with a different table)

                  

                  

            • 3. Re: Script to Copy or duplicate data from Portal Row
              Alejosor

                   Where it says Description appears the portal row

              • 4. Re: Script to Copy or duplicate data from Portal Row
                Alejosor

                     And If I use the "Select Quote" Button in "job Orders" layout, I should be able to select one of the approved "quotes"

                • 5. Re: Script to Copy or duplicate data from Portal Row
                  philmodjunk
                       

                            The information for Quotes::Staff 2 ID = Customers Billing::QuoteID is used for other purpose (with a different table)

                       Which does not change the fact that this makes no sense to me. You apparently have two fields in quotes::quoteID and Staff 2 ID that must store the exact same information or the record will not link to a record in Customers Billing. This can complicate the function of your database since QuoteID is apparently an auto-entered serial number but Staff 2 ID is not. I can imagine setting up an auto-enter calculation to copy over the value of QuoteID but can't imagine a purpose for that.

                       The problem here is that you have no direct link between Quotes and Job Orders. The only link is the Customers Billing record that is shared between them. That may cause issues for you. Do you have multiple Job Orders for a given quote? Do you have multiple quotes for a given Job Order? If the answer to either of those questions is no, then you may wish to consider a change in your relationships.

                       Working from the relationships that you have, this script, if performed from a layout based on Quotes, will create a new Job Orders record linked to the same Customer Billing record:

                       Freeze Window
                       Go to Layout [Job Orders (Job Orders)]
                       New Record/Request
                       Set Variable [$JobID ; Job Orders::Job Order ID]
                       Go to Layout [Original Layout ]
                       Set FIeld [Customer Billing::Job Order ID ; $JobID]

                  • 6. Re: Script to Copy or duplicate data from Portal Row
                    Alejosor

                         Hi, I Just Spoted that the relation is Quotes::Quotes ID = Customers Billing::QuoteID 

                    Not "Quotes::Staff 2 ID = Customers Billing::QuoteID" I use this relation to link the staff to the quotes.

                    Quote ID is a serial number that appears when I craeate a new record.

                          

                    How ca I perform the script based on the job Orders? First I need to send a quote and the if the quote is approved, I need to create a Job Order.

                          

                    Thanks for you patience.

                    • 7. Re: Script to Copy or duplicate data from Portal Row
                      Alejosor

                           I have Just one Quote, Then I need to create just one Job Order Based in that Quote and at last an Invoice based on the Job Order. The 3 of them got a different ID

                      • 8. Re: Script to Copy or duplicate data from Portal Row
                        Alejosor

                             My problems is that I can save all the information in Quotes or Job Orders, But when I try to transfer the information from quotes to Job orders, all the information that is the the portal row is not appearing. All the information related to client name or address apprears perfect.

                        • 9. Re: Script to Copy or duplicate data from Portal Row
                          philmodjunk
                               

                                    Hi, I Just Spoted that the relation is Quotes::Quotes ID = Customers Billing::QuoteID 

                               

                          Not "Quotes::Staff 2 ID = Customers Billing::QuoteID"

                               From what I can tell from your original screen shot, you do not have either of these relationships. What you have appears to be:

                          Quotes::Quotes ID = Customers Billing::QuoteID AND
                          Quotes::Staff 2 ID = Customers Billing::QuoteID

                               This does not link staff to a given quote and could easily result in significant problems in how your database functions. Double click the relationship line between these two table occurrence boxes. I predict that you will see the above relationship that matches by both pairs of fields.

                               

                                    I have Just one Quote, Then I need to create just one Job Order Based in that Quote and at last an Invoice based on the Job Order. The 3 of them got a different ID

                               Then the following relationship would make more sense:

                               Customers Billing-----<Quotes-----Job Orders------<LineItems

                               Quotes::QuoteID = Job Orders = Job Orders::QuoteID

                               WIth "allow creation" enabled for both Job Orders and LineItems in the above relationships, you can put a portal to LineItems in quotes and entering the first record in that portal will automatically create the needed Job Orders record without any scripting at all and the LineItems will automatically be aceesiible from either the Quotes or the Job Orders layouts.

                               With your existing relationships, your script will need the following modification of my original script:

                               Freeze Window
                               Go to Layout [Job Orders (Job Orders)]
                               New Record/Request
                               Set Variable [$JobID ; Job Orders::Job Order ID]
                               Go to Layout [Original Layout ]
                               Set FIeld [Customer Billing::Job Order ID ; $JobID]
                               Replace Field Contents [no dialog; Line Items 3::Job Order ID ; $JobID ]

                               This last step updates the Line items records currently linked to Quotes so that they have the needed Job Order ID to link to the same Job Order. If you then add a new line item from the quotes layout, that new line item will not be linked to the Job Order record. To handle that issue, use the looked up value auto-enter field option on Line Items::Job Order ID to copy the value of Job Order ID from Job Orders.

                          • 10. Re: Script to Copy or duplicate data from Portal Row
                            philmodjunk

                                 A final thought: Since you have one and only one Job Order for any given quote, you can further simplify by merging the fields from Quotes and Job Orders into a single table.

                            • 11. Re: Script to Copy or duplicate data from Portal Row
                              Alejosor

                                   Thanks for your help.

                                   I just realised that the image I sent you was edited in order not to make a big mess but didn't notice that I left a piece of a line suggesting that is a relation between Quotes::Staff 2 ID = Customers Billing::QuoteID

                               This relation doesn't exist.

                                   Thanks again and I will try your script.

                              • 12. Re: Script to Copy or duplicate data from Portal Row
                                philmodjunk

                                     You may also want to consider the methods for organizing your table occurrences that we usually refer to as Anchor Buoy. This method can greatly reduce the "spider webbing" and make relationship graphs much easier to work with.

                                • 13. Re: Script to Copy or duplicate data from Portal Row
                                  Alejosor

                                       Thanks Once again. I will organise the structure and merge Job Orders and Quotes into one table.

                                  • 14. Re: Script to Copy or duplicate data from Portal Row
                                    Alejosor

                                         But If a merge Job Orders and Quotes, every time that I create a new quote, automatically will create a new job order and vice versa. The serial numbers for quotes iare different to the ones for Job Orders. Any way to avoid this happening?

                                    1 2 Previous Next