11 Replies Latest reply on Nov 1, 2014 1:33 PM by gmfischer

    Foreign key and joined table

    gmfischer

      Title

      Foreign key and joined table

      Post

      I was told here a couple of weeks ago to solve a problem of a many to many relationship with a joined table

      I have a table with 3000 products, with a lot of data in it which include a reference to 5 separate manuals in a separate manual table I have a list of over 900 manuals with a field that is a url to it location on a website, it is simply part number field of the manual and a field of the URL

      A single product can have multiple manuals named (parts, repair, operations, etc...) and a single manual could be used on several different products.

      I have two tables with data already exist the products and the manuals I have created a third table as a joined table with a foreign key fields for products and manuals and have fields in both product and manuals that are primary field which I have given a serialized value in each. 

      I can't seem to get my head around how to tie the two tables to the joined table using the primary and foreign fields so when I look up a particular product I want to see all the manuals that are used on that product and their corresponding URL.

      My questions, how do I populate the foreign keys in the joined tables and connect the Manual numbers in the product table with the manual numbers in the manual table, sorry for my long winded question hope it gives you a clear picture of what I am looking for.

      .

        • 1. Re: Foreign key and joined table
          philmodjunk

          Since you have so much data, you won't want to set this up manually. A script can create the records in the join table and populate the needed foreign key fields in it--even put a value in a third field to identify the type of manual.

          Exactly how that script works will depend on the current design of the tables that currently hold this data. Do I remember correctly that in your Products table, you currently have multiple fields, one field for each manual?

          • 2. Re: Foreign key and joined table
            gmfischer

            yes that is correct, in the images attached I have included the relationship diagram, and in the Master Table or Product table I have the manuals under their descriptive names such as Obs Manual, Repair Manual, Parts manual, and then in the manual Table I simply have all the manuals listed as manual

            • 3. Re: Foreign key and joined table
              gmfischer
              /files/06202283b5/Relationship_diagram.png 786x749
              • 4. Re: Foreign key and joined table
                gmfischer

                sorry thought I could post all three at once

                • 5. Re: Foreign key and joined table
                  philmodjunk

                  And I presume that the "manual" fields store the URL, not an ID matching to the ID field in the Manuals table...

                  So your script would look something like this:

                  Go to Layout ["Master Table" (Master Table) ]
                  Show All Records
                  Go to Record/request/Page [First]
                  Loop
                     Set Variable [$ProductID ; value: MasterTable::PkProductID ]
                     Set Variable [$ManURL ; value: MasterTable::Ops Manual ]
                     Go to Layout ["Manual Table" (Manual Table) ]
                     Enter Find Mode []
                     Set Field [ Manual Table::Manual URL ; $ManURL ]
                     Set Error Capture [on]
                     Perform Find []
                     If [ Get ( FoundCount ) ]
                        Set Variable [ $ManID ; value: Manual Table::PkManualID ]
                        Go to Layout ["ProductManual" (ProductManual) ]
                        New Record/Request
                        Set Field [ ProductManual::_fkManualsID ; $ManID ]
                        Set Field [ProductManual::_fkProductid ; $ProductID ]
                        GO to Layout [original layout ]
                      Else
                         Show Custom Dialog ["error " & $ManURL & " was not found in the manuals table." ]
                         Halt Script
                       End IF

                       Go to Record/Request/page [next ; exit after last ]
                  End Loop

                  You will need to create the steps shown in blue four times inside this loop. Each set of steps would refer to a different one of the Manuals fields shown in you last screen shot.

                  • 6. Re: Foreign key and joined table
                    gmfischer

                    The "PKmanualID" is simply a serialize id number , "Manual"  is a 6 digit Manual Part Number , and Manual URL is the actual URL to take you to the file on the web. , hope that doesn't change your answer, thanks for taking the time to answer my elementary question, I am beginning to get my head around the moving of values in and out of fields, I will let you know how this works.

                    • 7. Re: Foreign key and joined table
                      gmfischer

                      I have studied you script and understanding what you have done here I have a few questions based on your script to help clarify in my mind what we are doing I would appreciate any help you could give, 

                      thanks again , I feel I maybe finally understanding how to do this stuff at a deeper level.

                      Greg

                      • 8. Re: Foreign key and joined table
                        philmodjunk

                        I like the way you visually asked these questions.

                        1) keep in mind that my assumption was that the field Ops manual stored the URL to the manual. If it stores a different value and this value can be used to find the correct record in the Manual Table, then this script needs to be modified by renaming the variable and setting a different field to the variable's value.

                        2) Yes any numeric value other than zero is evaluated as "True". No value or a value of 0 evaluates as "False". This is also the rule for If and case functions, BTW.

                        3) Well if you have used Manage | Database to create such a field in the join table, that's the place where you'd assign the value. But you can also specify the manual type in the Manuals table so that you only need to specify the type once instead of over and over in every new Join table record.

                        4) Changing records and changing layouts automatically commits records for you so you don't need to add a script step for this.

                         

                        • 9. Re: Foreign key and joined table
                          gmfischer

                          Well I tried the Script above and it worked through about 52 of the 3000+ records, I have 10 separate types of manuals, but not all products have a manual in of each type and the field would be blank, the matter of fact is that no one product has all the different manual types.  Do you think that is why it gets an error after only 52 records or is it something else?  I have included the first Loop and then the last loop below

                           

                           

                          • 10. Re: Foreign key and joined table
                            gmfischer
                            /files/b99df785ef/End_Loop.png 757x565
                            • 11. Re: Foreign key and joined table
                              gmfischer

                              Nevermind , I figured it out , the Manual Table dose not have the referenced manual from the Product table, Data is bad or in correct, problem solved, well... at least this one.