Please describe your tables and layouts in more detail. There are several possible methods for doing what you want. I can't tell which would apply to your database.
How many tables do you have? Describe any relationships you've created, what fields are used in the relationship?
Are you using a portal in this layout? If so, describe it in detail.
It looks like you are missing a third table that would join between Jobs and Materials.
Hi there and thanks for the prompt response, when I refer to db's I actually mean tables, so there are 2 tables:
tbl1 has 7 fields, serial number, name, length, width, total area, sheet cost, cost per sq cm.
Total area is a calculation (l x w) and cost per sq cm is also a calc field (sheet cost / cost per sq cm)
tbl 2 currently has not a lot in but this is where I create a job with the size of material I need and then I want to select from a drop down field the material name from table 1 and then calculate the cost of the material needed based on the table1 material cost per sq cm (of the selected material type, i assume from a look up).
I have not used any portal and the current relationship is name from table 1 to material name in table 2.
I hope this makes some sense.
I agree with comment, you need at least one more table. This sounds like your basic invoicing database and if so, you need one table as a "price list" that lists the materials available and their unit prices. That would appear to be your table 1. You need another table where one record represents one "job" that would appear to be your table 2. Your third table should list each item and the quantity to be used. Calculations in this third table will compute cost and other values you might need. In this third table, 1 record represents one item for one job.
Typically, you create a layout that displays one invoice record with a portal that lists each related record from our third table. When you enter data in your portal, you select from a drop down menu of values from your price list table (table 1) and unit price values are copied/auto-entered into the record from a related record in the price list table.
I could go on in more detail, but will stop here so that you can consider what I've posted and see if it makes sense to you.
Phil, much thanks for this and it sort of makes sense but I am still a little confused about how the relationhip works and the lookup, any chance you could through together a simple example for me to walk through.
my email is: gary at bt dot net
Use private messages so your email can't be "scraped".
Expect an email with "The sample filemaker file you requested." in the subject line.
Thanks and point noted about email address - I should know better :-)