5 Replies Latest reply on Apr 9, 2014 2:11 PM by philmodjunk

    copy data from another field and table with variable

    DavidS

      Title

      copy data from another field and table with variable

      Post

           Hello, i am new with filemaker, and i have maybe a simple problem?!

            

           I want to copy a field from a related table to another field in a another table.

           See picture!

           In the picture(1) you can see the data i want to copy or extract, to copy it to a another table.

           If i choose in a portal the material (see material in picture) such as "New imperial Red" then i want automatic the price in the field "Price" in the same portal. Butt it must be the price in the table with de field (example) "Kl3" or "Kl4" ...it changes when there is a another customer. So if customer 4 want to purchase something such as "New imperial red" then the database must lookup the right price in the table with prices (you can see in the picture)

           Butt i dont now how to do that, because i cant choose the field that is related to the customer...only the material or a exact field i can choose...so i want to do it with a variable... butt how...

            

           Somebody is understand what i meaning a help is very welcome and sorry for my english...

            

      1.jpg

        • 1. Re: copy data from another field and table with variable
          philmodjunk

               You do not have a portal in your screen shot. Your screen shot shows a table view of your data.

               You should not be "choosing a different field". Instead use a relationship to match to the correct record.

               I would describe this in more detail, but I am not sure what exactly you are trying to do.

               Maybe you want to do this:

               You are recording purchases or sales. Each row is a different record for a different item sold or purchased. When you select an item for this row, the item's price should be looked up for that item. And this can be a different price for different customers or different groups of customers...

               Is that what you are trying to do?

                

          • 2. Re: copy data from another field and table with variable
            DavidS

                 Here is the portal picture

                  

                 You can see the field in the portal (Materiaal) when i choose a materiaal then automatic the price must be in the field "M3 PRIJS"

                 This information must be extracted from the table in the picture above my first post!

                 I hope that is enough information?

            • 3. Re: copy data from another field and table with variable
              philmodjunk

                   Yes, but am I correct that you have multiple prices for each item and the different prices are in different columns of that record?

                   If so, this is what I have already suggested should be changed by setting up a table where you have just one price per record. (Though in some solutions, you might auto-enter all the prices from all the columns for that item into different field and use a calculation field that selects the correct price for that customer.)

                   The details for how you would set that up and use it with your portal will depend on why you have multiple prices per item and what data on your layout can be used to select the correct price for that item.

              • 4. Re: copy data from another field and table with variable
                DavidS

                     Yes, i have multiple prices for each item. Butt that depends wich customer id i use...so for example: If i use customer (Kl)3  (you can not use only numbers as field name)  want to order something like: Emerald Pearl (see picture table) , then the output wil be "4277" in the "M3 PRIJS" field in the portal. So i want to use a variable for this.

                     Such like this:

                     Set variable  [$KlantId; Value:"Kl" & OrderOfferteIdRecords::KlantnummerId]

                     The field "KlantnummerId" is the id from the customer  the output is "Kl3" if the customer id is 3. Butt i want this output to use to get to the right field (Kl3) in related table KlantenPrijzenRecords. I cannot use a search or choose a field with this output if i choose a material!

                     I must tell the program that if i choose a material in field "materiaal" with the material Emerald Pearl in record 3 and with the price in the field "Kl3" the output is 4277 for example

                      

                     I think the setup of the table must be different is what you say! I hope not.....:(

                • 5. Re: copy data from another field and table with variable
                  philmodjunk

                       We are on very different pages here. I see absolutely no need for a variable. But I do see a need to redesign your database to better support what you want to do.

                       You seem to be naming fields for specific customers and that is very unlikely to work as you would need to keep modifying your database design each time that you add a new customer.

                       Am I correct that the prices are specific to individual customers and not groups of customers?

                       What you need is a set of tables where you currently have fields--if correctly I understand the design of your database.

                       For setting the prices for each customer, you need this set of tables and relationships:

                       Customers----<Customer_Pricing>------Products

                       Customer::__pkCustomerID = Customer_Pricing::_fkCustomerID
                       Products::__pkProductID = Customer_Pricing::_fkProductID

                       And a number field in Customer_Pricing records the unit price for each product for a selected customer.

                       Then, when setting up an invoice or order, you'd use these tables/relationships:

                       Customer|Inv----<Invoices-----<LineItems>-----Customer_Pricing|Inv

                       Customer|Inv::__pkCustomerID = Invoices::_fkCustomerID

                       Invoices::__pkInvoiceID = LineItems::_fkInvoiceID AND
                       Invoices::_fkCustomerID = LineItems::_fkCustomerID

                       LineItems::_fkCustomerID = Customer_Pricing|Inv::_fkCustomerID AND
                       LineItems::_fkProductID = Customer_Priding|Inv::_fkProductID

                       Customer and Customer|Inv are two occurrences of the same data source table. Customer_Pricing and Customer_Pricing|Inv are also two occurrences of the same Data Source table.

                       With these tables and relationships, a number field in LineItems can look up the specific customer price for a selected product from Customer_Pricing|Inv and no scripting is required.

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