join data from more tables

[lada]

Hi Team,

I have table "products". Then I have another 5 tables for different kind of materials needed for production of the product. There are few common fields in material tables (as record ID, supplier, price but there are 4-10 different field which are specific for the given material table.

Table product is related with each individual material table.

What I am trying now is to set up a layout based on "purchase orders" table which should enable selecting materials from all material tables.

I tried to create joining table for all materials but stuck with relational problems (many table occurences) and relations resulting in circle.

I was thinking about putting material in one table but I know there will be more groups of materials in future and such table would have many fields (70-100) from which in one record would be only 10 used.

Maybe I will have to do this at the end but I feel there should be some better solution.

Can anyone help?

[Fenton]

One problem with the materials in different tables is that on any table where you have to choose from them, a value list can only come from 1 table; because the value list comes from the index of the field.

You can work-around this by have a "group" of material choice first. Then stack the Material_ID field on the layout 5 times, on top of each other, transparent; each one with a different value list, for the 5 tables. Put a transparent button on top, which goes to the right field instance for that group. FileMaker 8.5 would make this fairly easy, using the Go to Object step (after naming each instance of the field, in the Object Info pallet). You could not tab to the field; it would have to be removed from the Tab Order.

So it could be done. But it's (real) clunky. For one thing, the IDs, if FileMaker auto-enter serials, would have to be prefixed differently; no big deal really. Perhaps you're not using serial IDs; well, I almost always do, for many reasons. With 5 tables every lookup or related field calculation would have to look at 5 places; doable but a PITA.

You say more groups of materials in the future. Great (not), you have to go add more field layers on layouts, and redo every lookup and related field calculation. Ugh.

In my opinion, it's easier to put the materials in one table and keep them separate via the interface than it is to have 5 tables. 70-100* fields is not that bad. You can use the new Tab object to show the correct fields for that group in its own tab. You can go to the correct tab if you script navigation between records (8.5 helps here, but 8 will do).

Then you can use real IDs, proper relational design. You don't have to worry much if more groups are added later. Just add a tab; no changes needed in other tables. You do have to have a "group name" so you can separate them.

*You might be able to reuse more fields than you think. Try to think of them as generically as possible. Remember you can give the same field slightly different label names on the different tabs. I wouldn't reuse the same field in very different ways however; too confusing. Definitely reuse globals whenever it makes sense; possibly put some of them in a separate table; use script Variables more.

[lada]

Hi Fenton,

I was thinking over your suggestion. I would also prefer to have all materials in one table. Reason why I still hesitate is that original database was created in Microsoft Acces and now I am converting it into FM. This is why I would like to keep original Access schema. It means to import tables into FM, define relations, calculations etc. There are about 1000 of records together in material tables.

In the original Access file there are individual material tables and one "all materials table". Input af a new material is done through individual material table but purchasing is made through "all materials table". I am not Access expert but can see that "all material table" is not in relation graph at all. I can imagine with my novice knowledge of FM how to script adding or deletion of a record to "all material table" during every new entry into "individual material table". What I can not imagine is how to make sure that data in "all material table" are up to date when there is change in individual mat. table. Do you think that some sort of "find on common material fields" command on all tables at one go and then saving updated found data into "all material table" would do job?

I tried to understand how lookup works but it seems it needs relating all material to individual mat. tables which end in closed relation graph. I hope you still follow me...