3 Replies Latest reply on Sep 27, 2009 4:53 PM by davidel

    Problem with copy and paste records

    davidel

      Title

      Problem with copy and paste records

      Post

      Dear All,

       

            I have big problem with copy and paste records from one portal row to another portal row.

      I would like to know if anyone could help me solve this problem.  I am on small project to develop inventory system

      for my company.  The basic idea of this system is that there are supplier, customer and parts table

      and also Order table (purchasing from supplier),Invoice table (invoicing to our customer), and

      Inventory table (Inventory data such as turnover stock, product age, stock in hand).

       

                I want this system works where everytime we input parts data into the order table, it added the

      data to the Inventory table so that the stock in hand data will be increasing.

      Everytime I setup invoice for our customer (Invoice table), particular parts in inventory table will be deleted.  

       

                Inside the order table/layout, I have a portal row called orderline where everytime we receive

      parts, we input incoming parts data (Part number, ID number/serial number, Qty, Price) in this

      orderline (portal row in the Order table). By using script, this incoming parts (ID number, Qty, Price)

      is copied into the inventoryline (portal row in the inventory table/layout).  When I issued an invoice,

      ongoing parts data(Part number, ID number/serial number, Qty, Price) is input in the Invoice line

      (portal row in the Invoice table/layout) and at the same time the particular data inside the inventoryline

      is also deleted. Since price for the same product (same Part number) might be different due to Yen/Euro

      conversion at the time the product was purchased, so everytime we issued invoice, we have to make sure

      that the correct IDnumber/serial number is input in the invoiceline.

       

                Now, I have setup script to copy incoming parts in the orderline to the inventoryline, but

      it only copied the first row of the data. It did not copy the second row of the data in the orderline.

      I have attached the script below for your reference.  I really appreciate for your help and advice.

       

      Best Regards,

       

       

      ------------------------------------------------------------------------

      Input stock Qty script

       

      Set error capture on

      Enter browse mode

      Go to Layout (ELorder (ELorder))

      Go to field (Elorderline:partnumber)

      Go to portal row  (select first)

      Select All

      Copy () (Select)

      Go to Layout (ELInventory)

      Enter find mode ()

      Go to field (Elinventory:partnumber)

      Select all

      Paste () (select)

      Set Selection ()

       

      Enter browse mode

      Go to layout (Elorder)

      Go to field (ELorderline:ID Nr)

      Go to portal row (select first)

      Select all

      Copy ()  (select) 

      Go to layout (ELinventory)

      Go to field (Elinventoryline: ID Nr)

      Go to portal row (select first)

      Select all

      Paste ()(select)

       

      Go to layout (Elorder)

      Go to field (Elorderline: qty)

      Go to portal row (select first) 

      Select all

      Copy () (select)

      Go to Layout (Elinventory)

      Go to field (Elinventoryline : qty)

      Go to portal row (select first)

      Select all

      Paste () (select)

       

      Go to layout (ELorder)

      Go to field (ELorderline: price)

      Go to portal row (select first)

      Select all

      Copy () (select)

      Go to layout (Elinventory)

      Go to field (Elinventoryline: price)

      Go to portal row (select first) 

      Select all

      Paste ()  (Select)

       

      Loop

          Perform Script (input stock loop)

          Go to  Record/Request/Page (Next: Exit after last)

      End loop

       

      ----------------------------------------------------------------- 

      Input stock loop script 

       

      Set Error Capture (on)

      Enter browse mode

      Go to related record (from table Elorder using layout current layout)

      Go to field (ELorderline: product number)

      Go to portal row  (Select next)

      Select all

      Copy() ( Select)

      Go to Layout (ElInventory)

      Enter find mode ()

      Go to field (Elinventory:product number)

      Select all

      Paste () (Select)

      Set selection ()

       

      Enter browse mode

      Go to related record (from table Elorder using layout current layout)

      Go to field (Elorderline: ID Nr)

      Go to portal row (select next)

      Select all

      Copy () (Select)

      Go to related record (from table Elinventoryline using layout current layout)

      Go to field (Elinventoryline: ID Nr)

      Go to portal row (select next)

      Select all

      Paste () (Select)

       

      Go to related record (from table Elorder using layout current layout)

      Go to field (Elorderline: Qty)

      Go to portal row (select next)

      Select all

      Copy () (select)

      Go to related record (from table Elinventoryline using layout current layout)

      Go to field (Elinventoryline : Qty)

      Go to portal row (select next)

      Select all

      Paste () (select)

       

      Go to related record (from table Elorder using layout current layout)

      Go to field (Elorderline: price)

      Go to portal row (select next)

      Select all

      Copy () (select)

      Go to related record (from table Elinventory using layout current layout)

      Go to field (Elinventoryline : price)

      Go to portal row (select next)

      Select all

      Paste ()  (select)

       

        • 1. Re: Problem with copy and paste records
          davidel
            

          Sorry guys,

           

           I forgot to tell you that I am totally new to Filemarker and I only learned

          filemaker about three months ago by reading the filemaker guide book.

          Currently we are using Filemaker Pro 10, and our operating system is Windows vista.

          I would be grateful if you could give me some advice on my problems

           

          Best Regards

          • 2. Re: Problem with copy and paste records
            philmodjunk
              

            First note, copy and paste are not good script steps for moving data around your database. They only work if the referenced field is present on the current layout and copy destroys anything that the user may have earlier copied to the clipboard. To move data about, you can use set variable and set field instead.

             

            Second note, I'd suggest NOT copying the data from one table to another and especially NOT deleting records from your table to show that they've been removed from inventory.

             

            You can use one table to show invoiced line items, Ordered Line Items and Inventory on hand and just structure your portals to input data directly into that centralized table.

             

            Think of this table like a bank ledger or check book register. Ordered items are deposits (debits). Invoiced items are withdrawels (credits). A running balance on a given part number tells you the stock on hand.

             

            Your InventoryLog table might have the following fields:

            InvoiceNumber // link to matching invoice record so lines appear in invoice portal

            OrderNumber // link to matching order record so lines appear in order portal

            PartSerial // uniquely identify items to track cost changes due to currency fluctuations

            PartNumber // Use sort to group records by this value to see all inventory changes for this part

            QtyAdded // Qty field in Orders portal

            QtyRemoved // Qty field in Invoices portal

            cBalance // QtyAdded - QtyRemoved

            sTotalOnHand // Summary of cBalance, Running total, Restart total when sorted by PartNumber

            TransType // Auto enter "Sale" if InvoiceNumber isn't blank, Auto enter "Order" if OrderNumber isn't blank, Otherwise leave blank for user to input description for any other type of inventory change.

            TransDate // Date order/purchase/other change in inventory

            UnitPrice

            ExtendedOrder // UnitPrice * QtyAdded

            ExtendedSale // UnitPrice * QtyRemoved

             

            In addition to your portals referencing this table, you can create a list or table view report that lists all inventory transactions grouped by part number to show you current stock on hand as well as a history of all changes in inventory.

            • 3. Re: Problem with copy and paste records
              davidel
                

               

              Dear PhilModJunk,

               

               

                                Thank you so much for your help. I have been having problem figuring out how to get it done for the past three months

              I really appreciate for all of your advice.