AnsweredAssumed Answered

return a value from a table based on criteria from two other tables

Question asked by Gecho on May 14, 2010
Latest reply on May 14, 2010 by comment_1

Title

return a value from a table based on criteria from two other tables

Post

Hi all,

 

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

Outcomes