8 Replies Latest reply on Sep 22, 2014 12:41 PM by doorkitltd@gmail.com

    Relationship join table

    doorkitltd@gmail.com

      Title

      Relationship join table

      Post

      Hi

      Can someone help me with this relationship?

      I have a table called assembly where we are creating a quote for doors and frames.

      The doors can have glass.  On the assembly I want to pick the glass and have it automatically populate the glazing seal and/or liner seals that are related to that glass. The glass available in the value list should be relatable to the fire rating of the assembly.

             
      1. I have created a join table for the glass/seal/liners
      2.      
      3. I have created a second instance of these for selecting the material on the assembly
      4.      
      5. And a third instance for the price.

      I am only getting prices back for the first record in each table but only when I change the fire rating of the assembly.  So if I have more than 1 glass with different prices and different seals and liners it will only give the first in the list.

      Untitled.png

        • 1. Re: Relationship join table
          philmodjunk

          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?

                 
          1. have created a second instance of these for selecting the material on the assembly
          2.      
          3. 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...

           

          • 2. Re: Relationship join table
            doorkitltd@gmail.com

            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.

            • 3. Re: Relationship join table
              philmodjunk

              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?

              • 4. Re: Relationship join table
                doorkitltd@gmail.com

                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.

                • 5. Re: Relationship join table
                  philmodjunk

                  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

                  Caulkins Consulting, Home of Adventures In FileMaking

                  • 6. Re: Relationship join table
                    doorkitltd@gmail.com

                    Thanks, I'll have a look at them.

                    • 7. Re: Relationship join table
                      philmodjunk

                      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....

                      • 8. Re: Relationship join table
                        doorkitltd@gmail.com

                        Ah I think that is the biggest part of my problem.  I should have noticed this., Thanks