In the second Table (called example Mike as one of the salesmen)
I hope that doesn't mean that you plan on separate tables for each salesperson--not a good idea! Use one table for all your salespersonnel.
You appear to have this relationship
Orders::SalesPersonID = Mike::SalesPersonID
and have defined something that is supposed to calculate a total from Orders for a given record in "Mike"...
But there is more than one way to do that. Exactly how did you set that up?
If you are using an auto-enter calcualtion on a number field, you are correct that this will not automatically update. You should use a field of type calculation defined in Mike or a summary field defined in Orders to compute the total.
many thanks for your reply. You're right, i changed the field type to calculation and now it updates correctly.
back to the concept of my first DB in FMP (and first overall as i never used any other DB builders)..
Several years ago i create an excel worksheet for keep records of orders and fill automatically the result and bonus sheets for each salesmen.
It works, but I wish to learn how to make something similar in FMP for mainly two aspects: Layouts and more easy access to the records.
I'm sure the way i started to build this DB is not even closer to the worse case, but i don't know how to use the power that FMP might offer.
You said that make one table for each salesmen is a bad idea, and i agree with you. For sure one table only that display the data related to the selected salesmen if for sure more efficient, but how to do that?? As each salesmen might have different conditions and targets.
For make the calculations i create in the Orders table many numeric fields with conditional calculations, in order to get subtotals divided salesmen by salesmen, product by product.... among the field related to the order (like date of order, customer number etc), the variables i need to track are:
Order confirmed by downpayment (Y/N)
the last two (Y/N) are the key factors that are going to modify their order turnover and invoiced turnover, and calculate the related bonus for them.
so for each record there are several fields calculating the result of the conditions
for example: for each salesmen i have one field with a formula like this
if (SM_ID=1 and prod_fam="AC" and conf_ord="Y" ; amount ; 0)
to calculate if this order goes to the order turnover of which salesmen.
I'm sure there is a better way to do that....
Thanks for your help