5 Replies Latest reply on Jan 17, 2015 9:57 AM by philmodjunk

    Creation of relationship via portal

    deathrobot

      Title

      Creation of relationship via portal

      Post

      I have a table called Documents. Each document can be related to one or more other tables (Companies, Projects, People, etc.). I set up a join table called DocumentsAssigned. It has an id field for the document (id_document), plus id’s for the various other tables (id_companies, id_projects, id_people, etc.) Each record in the DocumentsAssigned table has a value for id_document, plus a value for ONE of the other id fields (e.g. if one document is assigned to a person AND a company, there will be two records in the join table).

      On a layout in the Documents table, there are portals for each of the related types (Companies, Projects, etc.) I want to script the creation of a relationship for the current document like so (for this example I’m trying to add a relationship to a person); 1) click on a “+” button, 2) have a new record created in the join table a show in the portal, 3) have a dropdown menu of people to choose from, 4) click on the person’s name to select. However, I’m coming across the following dilemma; if the portal points to the join table, ALL related records show up in the portal, not just people. If the portal points to the People table two jumps away, creating a new record doesn’t show up since there is not yet any value in the id_people field in the join table.

      Hoping this all makes sense and there is a way to make this work. Thanks for any advice.

        • 1. Re: Creation of relationship via portal
          philmodjunk

          But you don't need a script for this, just a portal to the join table in place of the portal to the people table. Format the PeopleID field inside the portal as your drop down. You can set up one such portal to the same join table for each of the other tables, just put a different set of fields inside the portal and add a portal filter that omits records that don't have a value in the ID field for the table you have set up this particular portal to create links to it.

          • 2. Re: Creation of relationship via portal
            deathrobot

            Thanks, Phil. That certainly works, and I may end up going with your suggestion. Ideally however, I'd like to get the scripting method to work due to some specific graphic/user interface issues. I also have many layouts that use a script so would prefer to keep things consistent. I could always split the DocumentsAssigned join table into separate tables for each relationship (there would be a total of five), but was hoping there might be another way. Performance-wise, is there a big difference between adding more tables and filtering portals via your method?

            • 3. Re: Creation of relationship via portal
              philmodjunk

              The problem is that I don't see anything for your script to do to make that happen. This all happens automatically without any scripting once you select a title from the drop down.

              • 4. Re: Creation of relationship via portal
                deathrobot

                I leave the "Allow creation of records via this relationship" off and script the part that makes a new record in the join table and pastes in the document id. The rest (where I can click on the person id field) works normally, but it won't create a new record; the record has to already be made. Since I'm sharing this join table to use between Documents and several other tables, if I set the portal filtering as you mention I don't see the new record because there is nothing in the id_People field yet. It does work to filter the opposite way, however:

                Let ( 
                    $otherIDs =  Documents » DocumentsAssigned::id_ASCAPInquiries & Documents » DocumentsAssigned::id_Companies & Documents » DocumentsAssigned::id_Invoices & Documents » DocumentsAssigned::id_Projects & Documents » DocumentsAssigned::id_Songs;
                    $otherIDs = Null
                )

                but seems a little messy; if I add any document relationships I have to remember to change every portal filter. It seems the only ways to achieve what I'm looking for are:

                1) Turn on the "Allow creation of records via this relationship" option (I prefer not to go this route)
                2) Use the above filtering on each portal
                3) Split out the join table into separate ones for each related table. In that case, I could use a more straightforward portal filtering (e.g. show when Documents » DocumentsAssigned::id_Documents <> Null )

                I suppose there's no benefit to choosing option 2 over 3 or vice versa?

                • 5. Re: Creation of relationship via portal
                  philmodjunk

                  If I chose not to allow adding new portal records via the blank "add row" of a portal, but used a script button to add that record, I would use a script to create the new record in a portal with a sort order that puts the newest record at the top of the portal--having to scroll to the end of the list of portal records to add a new record is the main reason I would make such a change in the first place.

                  I'd then click into and select from a drop down list located inside the portal to make my selection and link this new join table record to the correct record on the "far side" of the many to many relationship.