7 Replies Latest reply on Sep 29, 2013 10:14 AM by philmodjunk

    Best way to copy data from one portal to another?

    TimAdams

      Title

      Best way to copy data from one portal to another?

      Post

           I've created an inventory system with files for Purchase Orders, Purchase Order Line Items, Receipts and Receipts Line Items.

           The Receipts layout has 2 portals -- one for Purchase Order Line Items and one for Receipts Line Items.

           I've related the PO # field to the Receipts file, so that when I enter the PO #, all the items from the detail (Purchase Order Line Items) display in Receipts.

           What I would like to do is to copy & paste via script all the line items info from the Purchase Order Line Items into new Receipts Line Items entries so that I don't have to manually add each line item to the Receipts Line Items detail. I've researched various methods involving SetField but I can only get the first record to correctly get copied over, so I'm stumped.

        • 1. Re: Best way to copy data from one portal to another?
          philmodjunk

               What problem do you solve by having a separate, apparently duplicate list of Purchase Order and Receipt Line Items? Could it work to have just the one set of records in one table instead of duplicating the data in a second? (There are good reasons for doing so, but if those reasons don't apply to you, then you can simplify your solution design by keeping them in just one common table.)

               

                    The Receipts layout has 2 portals -- one for Purchase Order Line Items and one for Receipts Line Items.

               I am guessing that this means that you have relationships similar to this:

               Products-----<ReceiptLineItems>------Receipts-------<PurchaseOrderLineItems>------Products 2

               Receipts::__pkReceiptID = ReceiptLineItems::_fkReceiptID
               Receipts::__pkReceiptID = PurchaseOrderLineItems::_fkReceiptID
               Products::__pkProductID = ReceiptLineItems::_fkProductID
               Products 2::__pkProductID = PurchaseOrderLineItems::_fkProductID

               Products and Products 2 are two Tutorial: What are Table Occurrences? with the same data source table.

               Your field and table occurrence names may differ but if the match field values match up the same, then this script (but with your names), should do the trick:

               If [ Not IsEmpty ( PurchaseOrderLineItems::_fkReceiptID ) // if there are PO line items in the portal ]
                  Set Variable[ $ReceiptID ; Value: Receipts::__pkReceiptID
                  Go To Related Record
                          [Show only related records; From table: PurchaseOrderLineItems ; Using layout: "PurchaseOrderLineItems" (PurchaseOrderLineItems) ]
                  Go to Record/Request/Page [First]
                  Loop
                      Set Variable [$ItemID ; Value: PurchaseOrderLineItems::_fkProductID ]
                      Go to Layout ["ReceiptLineItems" (ReceiptLineItems)]
                      New Record/Request
                      Set Field [ReceiptLineItems::_fkReceiptID ; $ReceiptID ]
                      Set Field [ReceiptLineItems::_fkProductID ; $ItemID ]
                      Go to Layout ["PurchaseOrderLineItems" (PurchaseOrderLineItems) ]--->must return to our list of PO line items before looping to next line item
                      Go to Record/Request/Page [Next ; Exit after last ]
                  End Loop
                  Go to Layout [Original Layout]

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Best way to copy data from one portal to another?
            TimAdams
             Thanks, Phil. The reason for the separate files is...that's the way I've always done it, which I'm sure is not the best way from a design perspective. I'm decently versed in Filemaker, but I just do not grasp the more advanced concepts. I would love to have one "Line Items" file that captures all the different activities I do -- ordering, receiving, and selling inventory -- so if you know of a good way to delineate the line items by the different activities, I would love to learn it. For instance, in my Invoices, I have an Invoice Line Items portal. I'm guessing that could be modified to use the master Line Items database with some sort of indication that it's a Sale vs. an Order or a Receipt. Whatever advice you can offer would be greatly appreciated.
            • 3. Re: Best way to copy data from one portal to another?
              philmodjunk

                   What I don't know is if you can actually get away with a single table or not. That depends on how you need to use the data that you are now copying from table to table. Where this can fail to be possible is if the Purchase orders don't always matched the received shipments. You get back ordered items and orders fulfilled indirectly, you send out a massive order and get back several individual shipments that, when combined, fulfill the original order with 20 of LineItem A arriving in one partial shipment and 5 arriving in the next to fulfill an order for (Hopefully) 25 or maybe you ordered 24 and you got an extra....

                   How you hand that reception process and what you need to see in the database can determine whether or not you need those separate tables. The smaller/simpler your actual business operations, the less likely you are to need separate line items tables.

              • 4. Re: Best way to copy data from one portal to another?
                TimAdams
                 I use the data to keep track of the quantity I'm ordering/receiving/selling items with also a few calculations for quantities this quarter/last quarter/YTD/all time so I can monitor what's selling and what's not. As for receiving inventory, for the most part I'll either receive the full quantity, zero, or a partial fill on my orders, with no backordering -- I would create a new PO if I want to order it again. As I said, I have a lot of "Line Items" files for the disparate tasks of ordering, receiving, and selling, so if I could just have one Line Items file and some way to differentiate the line items, that would be great. Thanks!
                • 5. Re: Best way to copy data from one portal to another?
                  philmodjunk

                       But HOW do you record that "full, zero or Partial" quantity in your LineItems data? Reception of a partial order often requires manipulating your line item data in ways that you don't want to show on the original order, just on the receiving layout and that in turn may argue for keeping your tables separate as they are.

                       

                            and some way to differentiate the line items

                       "Differentiate them" in what way? From what? They are already assigned to a Purchase Order and a different field can be used to link each item to a reception record. But it still comes down to how you manage the reception of a shipment that does not exactly match the original order...

                  • 6. Re: Best way to copy data from one portal to another?
                    TimAdams
                     I was thinking of one master Line Items file, with fields for Qty Ordered, Qty Received, Qty Sold, to describe the transaction type. First line item would be created from a Purchase Order, second line item would be created when order is received, and a third (and fourth, etc.) line item would be created when sold. As long as the Line Items file was related to the Purchase Orders, Receipts, and Invoices file via their respective PO # / Receipt / Invoice #s, wouldn't that work?
                    • 7. Re: Best way to copy data from one portal to another?
                      philmodjunk

                           And how would you use these fields when the quantity ordered and the quantity received are not the same? (Which can be due to human error or a shortage of available inventory to ship to you.)

                           And do you ever receive a "split shipment" where some of the product you ordered arrives in one shipment with the balance to follow in one or more shipments sent separately?