4 Replies Latest reply on Jun 3, 2011 3:02 PM by Malibux14x

    Copying and Pasting info from one database into another using a script and a button

    Malibux14x

      Title

      Copying and Pasting info from one database into another using a script and a button

      Post

      Hi there.

      i have two databases (enter_data and Purchase Orders).  i want to click a button in the enter_data database and have it copy the info in one field (style Name) then go to the other data base (Purchase Orders) and paste it in the next field (Product description), hit return to go into the next field and then return to original database (enter_data).

      ive tried doing this using a script but i feel that i have the relationships wrong or something.

      anyone have any idea how to do this easily?

      thanks for your help

      (im including a screenshot of script i started)

      relationships.JPG

        • 1. Re: Copying and Pasting info from one database into another using a script and a button
          philmodjunk

          It's best not to use copy and paste at all for this purpose. There's really only a very limited list of "jobs" for which copy and paste are the best/only option and this isn't one of them.

          Use this script:

          Freeze Window
          Set Variable [$Name ; Value: enter_date::Style Name]
          Go To Layout [Purchase Orders::Product Description]
          #And you have a problem here, How do you know that the right record in Purchase Orders is the current record here?
          Set Field [Purchase Orders::Product Description; $Name]
          Go To Layout [Original Layout]

          (No need for exit script here.)

          Judging by the field and table names, I suspect that this scripted approach isn't your best option at all. Normally, one selects a product for a purchase order by an ID number (as names may not be unique and often change over time.) and makes this selection in a portal of related Line Items records on the Purchase Orders layout so that you can have a an itemized list of different items on the same purchase order.

          That approach requires this table structure:

          Purchase Orders---<LineItems>-----Products

          • 2. Re: Copying and Pasting info from one database into another using a script and a button
            Malibux14x

            Hi PhilMod.  thanks for the help.

            i would use the product ID however there are multiple colors in a style so i use the style name plus the color after it.  so the style name is the best use here.

            i tired to script you mentioned.  tottally makes sense.  however it isnt working.  im not sure if the relationship between the two databases are correct.  ill attach two new photos here to show waht i ahve with your script and the relationship i made.

            regarding your question on how do i know which record to set the cariable in; it shuld be selected.

            what i want to do is, create a new PO.  ill start entering info, and then if someone gives me a style number i need to look up, i go to the product database (enter_data) and search that style number, then i want to be able to press a button and it automatically caopies and pastes it into the next field in the PO (PurchaseOrders) and then add a tab at the end to go to the nnext one.  any ideas?

            • 3. Re: Copying and Pasting info from one database into another using a script and a button
              philmodjunk

              "i would use the product ID however there are multiple colors in a style so i use the style name plus the color after it.  so the style name is the best use here."

              I'd still use the productID rather than the name. Product ID should be unique to every product record and should not be the catalog number from an outside source. You can include such a catalog number--just don't use it as the key in your relationships. There are a number of options that enable you to get this to work including enhanced value list methods that let you search by Product Name, but enter the Product ID and also conditional value lists that would enable you to select a color first and then the value list only lists items of that specified color.

              "i tried the script you mentioned."

              As written, the script would have to be run from enter_data layout with the correct record as the current record. It enters the data into the same field every time and it should do so. I think you need to change your table structure here.

              You should have these tables with these relationships:

              PurchaseOrders----<LineItems>----Products

              PurchaseOrders::PurchaseOrderID = LineItems::PurchaseOrderID
              Products::ProductID = LineItems::ProductID

              Take a look at this invoicing demo created by Comment. Invoicing systems use the same structure, just rename Invoices as PurchaseOrders.

              http://fmforums.com/forum/showpost.php?post/309136/

              • 4. Re: Copying and Pasting info from one database into another using a script and a button
                Malibux14x

                im still confused.  i edited the relationship to match what you suggested and not i cannot erase any records.  i removed the relationship and now i can.

                im not sure what i want can be done.  so i guess ill stick to the short cut keys on the keyboard for now.

                thanks for your help.