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?
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?
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.
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.....:(
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:
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::__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