9 Replies Latest reply on Mar 22, 2017 5:34 AM by beverly

    Best - Table Occurrence - Portal Setup

    burghfan

      In our solution we have a "Projects" Table and a "Manufacturers" table.

       

      On the "Projects" table multiple "Manufacturers" can be linked too the project. This is currently done by having multiple fields (id_manufacturer_1, id_manufacturer_2, id_manufacturer_3 and id_manufacturer_4 with Corresponding Table Occurrences where in the first TO the ID_Manufacturer = id_manufacturer_1, in the second TO the ID_Manufacturer = id_manufacturer_2, in the third TO the ID_Manufacturer= id_manufacturer_3 and in the fourth TO the ID_Manufacturer = id_manufacturer_4

       

      Is there a way to show the information from the "Projects" table show up in a portal in the "Manufacturers" table? In other words can I create a "Projects" to "Manufacturer" Table Occurrence where;

       

      ID_Manufacturer = id_manufacturer_1

      ID_Manufacturer = id_manufacturer_2

      ID_Manufacturer = id_manufacturer_3

      ID_Manufacturer = id_manufacturer_4

        • 1. Re: Best - Table Occurrence - Portal Setup
          BruceRobertson

          None of the above.

          As with just about every case with numbered fields, the best practice is to NOT have them.

          Instead create a project_manufacturer join table with foreign key fields for IDf_project and IDf_manufacturer.

          • 2. Re: Best - Table Occurrence - Portal Setup
            philmodjunk

            The Join table that Bruce refers to would look like this:

             

            Projects---<project_manufacturer>----Manufacturer

            ---< means "one to many"

             

            If you enable "allow creation" for the join table, you can use a portal to the join table to build the links between projects and manufacturers.

            • 3. Re: Best - Table Occurrence - Portal Setup
              burghfan

              A question, should I use the join table for the portal I want on the "Manufacturers" table?

               

              I did this and nothing shows up. Is this because I have more than one "Manufacturer" on the "Projects" table?

              • 4. Re: Best - Table Occurrence - Portal Setup
                philmodjunk

                You can put a portal to the Join table on your projects layout and use it to create new Join table records that link the current project to different manufacturers. You can put a portal to the Join table on your Manufacturers layout and use it to link the current Manufacturer to different projects. These two uses of a portal to a Join table are not mutually exclusive.

                 

                The portal will be empty until you create records in the portal. Starting from a Projects layout, you might do the following:

                 

                Put the Join table's match field for Manufacturers in the portal row and format it with a value list of Manufacturers. By selecting a manufacturer, you both create a new record in the join table and link it both to the current project and the selected manufacturer with a single value list selection. (The match field linking it to the current project gets the needed value automatically.) You can add any additional fields from the Manufacturers table occurrence to this portal based on the Join table as you need to show information about the selected manufacturer.

                 

                Please note that the one thing that you can't do in this example is add a manufacturer that isn't already in the manufacturer's table. You'd need to go to a layout for that table, add the new record first, then return to the projects layout and select the newly added manufacturer in the portal. There are ways to automate that process.

                • 5. Re: Best - Table Occurrence - Portal Setup
                  burghfan

                  So existing Projects will not show up in the portal?

                  • 6. Re: Best - Table Occurrence - Portal Setup
                    philmodjunk

                    Not until you use the join table to link them to the current Manufacturer. If you have a lot of records and an existing relationship, a script can be used to update the Join table.

                    • 7. Re: Best - Table Occurrence - Portal Setup
                      keywords

                      For existing projects you should create a once off script to do the following:

                      Loop through all project records, and on each record—

                      (1) take the ID from id_manufacturer_1 and create a join record

                      (2) repeat for id_manufacturer_2, 3 and 4

                      Once you have completed that script you will have upgraded your existing system to the new structure. Then you can:

                      delete the id_manufacturer_1, 2, 3, 4 fields

                      delete the script

                      • 8. Re: Best - Table Occurrence - Portal Setup
                        burghfan

                        Thanks for all of the suggestions and help. I must have done something wrong... I'll be back at it next week to try and figure out where I went wrong, I will be away from the office for a few days.

                        • 9. Re: Best - Table Occurrence - Portal Setup
                          beverly

                          followup after reading all the posts:

                           

                          yes, create the Join table

                          yes, put the join portal on the Projects layout detail and you can add Manufacturers

                               use a drop down, perhaps to select Manufacture ID to add (the foreign key is set)

                               you can put the related manufacturer name (view only) also in the JOIN portal

                          yes, put the join portal on the Manufactures layout detail and you can add Projects

                               use a drop down, perhaps, to select Project ID to add (the foreign key is set)

                               you can put the related project name (view only) also in the JOIN portal

                          yes, you can see the Manufacturer and Project in the JOIN table (list view layout) because you can put the related fields (view only) from the two relationships to the respective foreign keys to their parent primary keys

                           

                          it's called "tunneling data through", even though you ENTER the foreign keys in the join table, the relationship allows the other fields to be shown in the portal or list view from the perspective of that JOIN table.

                           

                          HTH,

                          beverly