1 Reply Latest reply on Oct 12, 2012 6:07 PM by BruceRobertson

    Tables and Scripts in FM10adv

    carfiles

      Question about tables and copying fields into one another: FM10adv

       

      I've created a datafile named 'Sales' with two tables.

      Table one named: Items for Sale (this I use as the main table)

      Table two named: Pending Sales

       

      All the new records are entered using 'table one' and each and every record have it's own unique serial number to identify the product (there are never two serial numbers alike). The two tables are linked using the serial number.

       

      When a sale is made is not always final, sometimes we issue the customer a 'proposal' meaning that they may or may not come back later to actually purchase the product. In this case a simple script creates a new record in 'table two' and all the information including the customer's and the product gets sent there. The original record remains intact in 'table one' and available for sale to someone else.

       

      This leads to my question. When a customer does come back to purchase that item and that item is no longer available for sale, I need a script that will transfer his personal information to a different item that is available. So far I have a script that originates from 'table two' and searches for current inventory in 'table one' and presents me with a list of other similar items to choose from. I need a script that populates the new item I select from the list with the customer's information that is located in a record in 'table two'. I have no problem doing this when the item in 'table one' is the same (serial number) as the the item in 'table two'. Only when the items are different is where I having a problem copying this information over.

       

      Any help would be greatly appreciated, thanks...Gary

        • 1. Re: Tables and Scripts in FM10adv
          BruceRobertson

          You'll get plenty of suggestions that you have taken a slightly wrong structural approach to the problem.

           

          This isn't really a copying-fields problem.

           

          The preferred method is to have a contacts table where you enter the contact information; and to link other tables such as your pending sales table to the contacts table.

           

          The pending sales table in its most basic form would have the customer ID; and sales product ID and date the pending sale was initiated; and perhaps some other data strictly about the pending sale (price for instance).

           

          No product or customer information needs to be present in your pending sales table.