To clarify your question, when you invoice WIDGET1 which normally sells for $2.50, you the look up to return a different price because it is a specific client? Or you want to change the price on the fly as you bill this client?
In fact i want both
i want the price to automatically return a different price depending on the client
and i want to be able to change it on the fly as i bill the client (to to be a calculation that i cannot change…)
You can make the price a look up. I am sure you could create a secondary price on the line item that is the price from a related table to the client. Or on the client profile you might have a percentage that is a discount from the regular price. You could accept that price or overwrite with a new price. Once the invoice is committed you security could make the price fixed (locked in).
In a catering application, I provided the menu price but provided a field for custom price. I think checked THAT price to make sure it was not too low. You have a lot of options.
I am having the same difficulty here being new to Filemaker I am trying to have a price of the product depending of the client, the price is not a discount from the regular one, but created independently.
But I think that my relations between my tables are a bit mixed up.
Here is how I modeled them:
Client --< Price >-- Product >--- Type
id id id id
name idCli idTYP type
One of the objectives is to show the client only the products he can purchase and show them the price determined for them.
Perhaps I should add somewhere in my tables the products the client can access.
If you have any idea or hint?
PS: Foreign Keys are in italic for better readability