I am a bit puzzled by your design. The basic concept makes sense. A join table that documents the components used to assemble or manufacture an item is often called the BOM (Bill of Materials) table and is usually used to list the components and quantities of each consumed to assemble or manufacture the item identified in your parent table (Assembly).
But I don't see the reason for three different occurrences here and two different match fields in assembly matching to the same join table in two different relationships.
What are you trying to accomplish exactly by specifying a "Fire Rating" when it comes to working with the BOM entries in your join table?
- have created a second instance of these for selecting the material on the assembly
- And a third instance for the price.
I see no reason for both instances. A single instance should work for both as your two Occurrences match to exactly the same data source tables with exactly the same match fields. The same portal on the same layout could even be used for both.
And in such a BOM table, I'd have one record in the join table for each item, one for the Glass, one for the liner and one for the tape. I don't know if this is what you have set up here or not...
I have set up a join table and entered new records
A record will have firstly the glass, then the seal and may or may not require a liner.
Each assembly will have a fire rating – (None, FD30, FD60)
The glass is primary within the join table. So we will say what rating each glass is and link a suitable seal or liner.
The cost of the glass, seals and liners is only on their respective tables. So I was using the first instance to select the glass and the second instance to pull the prices for glass seals and liners. I created the third instance because I was not successful getting the prices from the second instance.
I’m not looking to be able to pick the seals or liners from value list – they should be populated by picking the glass relevant to the assembly fire rating.
I wasn’t sure if the relationship was correct.
Let's check a few details:
Your layout is based on Assembly 2?
And what does a record in Assembly represent?
When you select or enter a value for Assembly 2::FireRating, what changes do you expect to see as a result and where on your layout?
This is a copy of the assembly table - because there are more tables linked to it I thought it would be better to just take a picture of the tables I'm having problems with.
The assembly table is where we build the door kit, i.e. door,frame,screen,architraves, locks hinges, vision panels(glass apertures) etc.
I even have it producing a drawing, but i have never pulled information through a join table. All other elements - Frames,doors etc I have working fine but they're not through a join table.
Note : on the assembly layout we will input the fire rating required, this will then filter all materials available in the drop down lists for doors, frames etc. We can input the sizes of the cutout for the apertures in the doors for glass. The glass in related to the fire rating, So when we pick the glass we want the matched seal or liner to populate the relevant fields. This will be required for cultists, PO's etc in the future.
This is a copy of the assembly table
I don't think so. This looks like a second Tutorial: What are Table Occurrences? with the same data source table (assembly). This is a crucial issue as your layout will specify a specific table occurrence in Layout Setup | Show Records from and if you have something other than Assembly 2 specified here, the relationships that you've posted do not apply
this will then filter all materials available in the drop down lists for doors, frames etc
Then you'd need a relationship between assembly 2 and the individual tables that you have for these items, not as a relationship to the join table. The data that you are trying to "pull through" the join table sounds like data that should either be data that remains in the join table (if looked up (copied) from the other linked tables) or referenced by putting fields from the table occurrences "on the far side" of the join table directly on your layout. It depends on what this data is and how you will use it.
You may find this instructional database file on conditional value lists of interest to you:
Adventures in FileMaking #1 - Conditional Value Lists
Thanks, I'll have a look at them.
Apologies for not noticing this sooner, but your Join table is not actually a join table. If this were a true join table, it would link many records in Assembly 2 to many records in the other related tables. In such a relationship, the _pk (Primary key) field would be defined in Assembly, not in the join table.
Time to go home and put this Migraine to bed....
Ah I think that is the biggest part of my problem. I should have noticed this., Thanks