The problem is that you have set up multiple fields for the same type of data in one record. In order to look up the data, you'd need to define a different relationship for the look up for each set of separate fields as each specifies a different match field. Some people encounter the same issue when they make the mistake of using a repeating field where they then get a value to look up only in the first repetition of the field.
A better approach that eliminates the need for multiple relationships to different table occurrences of the same look up table is to replace this set of fields with a portal to a related table. That makes each set of fields the same set of fields in a different related record. The result on your layout can look much the same but now you can set up a single relationship from this new related table to your look up table in order to look up the needed data.
You can see a working example of this in the Invoices starter solutions that come with FileMaker 11-12. The invoices layouts in these solutions use a portal to a line items or invoice data table--which in turn looks up pricing info from a Products table.
Thanks for the advice, I think I need to look at it more closely, I've created a new database using the Invoice template as you mentioned above, I foresee some major rewriting taking place to get this all sorted.
Many thanks for your help
I've set up a portal as best I could looking at the invoices file, I can't figure out how to get a new line or why the grand total is multiplying the total by the number of lines, e.g. I have the portal set to seven lines, I have 100 in the total and the grand total shows 700, but I can't figure out how to get a new line to enter additional products?
Without a more detailed description of what you have set up, I can't provide specific assistance.
There are two ways to make it possible to add new records to your portal:
Go to Manage | Database | Relationships, double click the relationship line linking the portal's table occurrence to the layout's. Select "Allow creation of records via this relationship". This causes a blank "add" row to appear in your portal after the last portal entry and you can add new records just by entering data in the portal row.
Put a button on your layout that performs a script to add the new record.