2 Replies Latest reply on Oct 11, 2010 3:58 PM by mwilds

    Look Up help Please



      Look Up help Please


      This is my first post ever on a forum so please excuse my ignorance, I haven't worked with filemaker in a while because it has worked flawlessly for years since I built it. But now my company has multiple contracts with different companies and each contract has different prices for the same type of work. In the past we only worked for one company so I built a Invoice file that looked up descriptions & prices from a work file based on a unit number ie. unit 123 = dig ditch 1.00 per ft. and these units are defined by the company's we work for so now I have unit 123 with a different price from each company we work for. So what I was wondering is if there is a way that I can have a different file for each company that holds a description and price , and be able to lookup data from each file based on a unique customer number and a unit number from my Invoice file. I am using FM 5

      Thank you,

        • 1. Re: Look Up help Please

          Sympathies for being stuck with FM 5. The later versions of FileMaker offer so many more options. And the issue you describe is much easier to setup in later versions that allow for more sophisticated relationship definitions.

          Given that you are using version 5:

          In both files, define a text calculation that looks like this: CompanyID & " " & Unit Number

          Use these two fields to link your files in the lookup relationship.

          When you enter both a company ID and a unit number in the respective fields of your first file (presumably a line items type file), it will look up a matching unit price from the second, matching by both CompanyID and unit number values to get the value for the appropriate company.

          If you are using a third file that lists the individual line items in a portal like you should be doing. The company ID value should be entered in a field in this file and the CompanyID field in the line items table should look this value up from it so that you do not have to enter a company ID on every line of the portal.

          • 2. Re: Look Up help Please

            Thanks Phil for the help I think this will work good.