2 Replies Latest reply on May 14, 2010 1:33 PM by comment_1

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

    Gecho

      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

        • 1. Re: return a value from a table based on criteria from two other tables
          philmodjunk

          You should be able to define a "rate" field in your charges table and set it up with the looked up values field option to copy the matching rate from your rates table. This field can then be placed in your portal.

           

          Under other circumstances, you could simply place the rate field from your rates table in the portal, but rates are subject to change and you don't want a rate change to alter charges records that already exist and the looked up value option avoids this issue.

          • 2. Re: return a value from a table based on criteria from two other tables
            comment_1

             


            Gecho wrote:

            The relationships are currently

            rates:charge item = charges:charge item

            work:job no = charges:job no


            That's not enough: if you want to lookup the correct charge, the relationship must match on the company as well - and a record in charges needs to "know" which company the job is for. You could add a calculation field to the charges table for this.

             

             

            NOTE: your relationships should be based on unique ID's instead of names, e.g.:

             

            Jobs::JobID = Charges::JobID

             

            where JobID in the Jobs table is set to auto-enter a serial number. That way you can rename an item without breaking the relationship.