I have a problem to solve and I'm confused. Creating a ERP / CRM solution I need to set the right price to the product in an order. I'm working on Mac, using FileMaker Pro 16.
I have a Products table listing all products.
Then I have a Products groups table - each product in one group, each group consists of one or more products.
I have a Customers table.
And a Customers groups table - each customer belong in one of those groups, each group consists of more than one customers.
There is a Price table setting the price for each unique combination of Products group and Customers group. The product may have more different prices - one for each combination of Products group and Customers group.
I also have Orders table and Order lines table.
On my Order layout the user can choose the Customer first (so I know the Customers group).
There is a portal to set/show/delete Order line items. There could be a conditional value list showing only Products from the Products groups which this Customer may order.
So my first question is how to make it? Is it some kind of double-sided-conditional value list?
And the more important second question is: how to set the right price for each chosen product in the line item (portal)?
Now the user switches to the Price table layout, enters the find mode and enters the values into Customer name (portal - Customers) and Product name (portal - Products). This works. But I need some automation, the above mentioned solution is hardly suitable for my beta-version. Using the set variable I was only able to find some price - usually not the right one.