Relationship of fields between 2 files

[Red Dude]

Hi all, self learned beginner here. I created a quotation tool where I will actually quote scopes of work, amount of hours, materials required, etc for customers. I have the data entry 95% complete.

The problem is with the Bill of Mateiral (and others I will create later). I have a seperate file called Product Catalog where I will import thousands of records eventually.

What I need to do is when I input a part number in the "Part Number" field in Quotations to look in the Product Catalog file, find that part number and auto fill in the Manufacturer and description.

I've read and read and did alot of different things but I think this should be something simple I am missing... like a box I didn't check somewhere.

Help please, attached are my 2 files.

FMP_DB.zip

[Tpaairman]

Well first of all, you don't need two separate files. Just create a new table in the first file. However you are on the right track with your relation.

What you need here is a portal instead of all the lists, and actually another table that shows all the parts you are using for that one Quotation. I will put this together for you and send you the new file, with a better explanation of how it is working. It would be rather lengthy to explain it all here without seeing the file work.

[Red Dude]

Thanks for the help.

The reason why I have 2 files is the products file will eventually hold upwards of 10,000 line items which may or may not make a difference.

the last time I used Filemaker was 5.5 :

[Tpaairman]

The number of records shouldn't be an issue. I do antique radios as a hobby, and I have a file that has data for both of my tube testers, as well as data for different types of tubes (each of those 3 plus many others are all on their own tables.) I'm up to about 8,000+ total records with no problems.

Tables was something new with (IIRC) version 7. In the older versions (in your case 5.5) you had to have a completely separate file for each part of your whole database, just as you did here. But now, think of each table as one of those separate files. The other great part is since each layout has a certain table assigned to it, you can have a navigation script to go from layout to layout, instead of opening another file.

Now on to the question at hand. Take a look at the attached file. This is the quotation file you have posted, but I've added two tables to it. First is the product catalog (I just created a new table and added a couple fields for show here) and a join table.

How this works is: the join field has two key fields to look at. One it Quotation#, the other is Part#. It has a relation to Quotation via Quotation#, and a relation to Parts Catalog via Part#. There is no relation between Parts catalog and Quotation.

In the join field, I put in 3 other fields: category, desc., and MFGR. These are all calculation fields, that the calculation result is simply it counterpart field in the Parts Catalog table. You can add what you need, but this should let you see what you need to do.

Meanwhile, back at the ranch, or in this case the Quotation layout, there is a portal that is using the relation to the join table. Something important is the relation is set up to allow the creation of related records (go to relations, and click on the little box in the line between the tables to make changes). When you type in a part number in the portal row, this automatically creates a new record in Join. That new record automatically fills in the Quotation# (since is was done in that layout and hence that table in the relation), and you typed in the part#, so at this point the calculation takes over on the other fields.

Quotations.fp7.zip

Edited May 12, 2009 by Guest

[Red Dude]

Wow, thanks for the time you put in replying.

The tables reference makes so much sense. I actually tried it myself and it works so much better. I imported over 10k records and it doesn't even bat an eye...

The portal I am going to read up on a little more, that's a little out there.

Thanks again, great help!