return a value from a table based on criteria from two other tables
I am very new to FileMaker, so please bear with me.
I am trying to create a database for myself as a self employed person offering a variety of services to a number of companies. Each service has it's own rate and that rate is dependant on the company.
I have a "work" table, which contains "Client Name" and "Job No"
a "rates" table which contains "company name", "charge item" and "rate"
and a "charges" table which contains "Job No", "quantity", "charge item" and "rate"
The relationships are currently
rates:charge item = charges:charge item
work:job no = charges:job no
In the "work" layout I have a portal which is linked to the "charges" table and shows the "quantity" and "charge item", for which I want to return the company specific rate from the "rates" table and them multiply it out by the quantity to get the total.
This way in the "work" layout, I should end up with a record for each job, that has the client and job details at the top and then a list of the services employed for that job, the rates and the total amount.
Please help, I cannot figure out for the life of me how to achieve this.
Thanks in advance