5 Replies Latest reply on Jan 13, 2015 2:51 AM by ianmanning

    Link one field to two tables

    ianmanning
    ianmanning Jan 9, 2015 8:28 AM

      Title

      Link one field to two tables

      Post

      I have an existing database with multiple tables. I now need to be able to have a relationship from one field that will get data from either of two tables.

      Table 1 is a List of Promotional Events.

      Table 2 is a link table to facilitate linking many events to many attendees

      Table 3 holds details of one group of possible attendees  

      Table 4 holds details of another group of possible attendees  

      How can I have a portal on Table 1 showing the details of the Attendees from both Table 3 and 4?
      If all possible Attendees were in one table it would be simple
      Event table -----Link Table -----Attendee table
      Portal on Event table layout showing details from Link table with fields from Attendee table.

      I can give a much more detailed explanation if required but I hope this is sufficient to understand the problem.

      Thanks for your help

      Ian

       

      • 136 Views
      • Tags:
        • 1. Re: Link one field to two tables
          schamblee
          schamblee Jan 9, 2015 10:27 AM (in response to ianmanning)

          Your layout would be based on the Link Table and then you can show information from the other two tables.

          • 2. Re: Link one field to two tables
            philmodjunk
            philmodjunk Jan 9, 2015 1:32 PM (in response to ianmanning)

            While not useful for editing data, you could put a portal to attendees on your Events layout and include fields from the two related "detail tables" in the portal row. Assuming that you would not link the same attendee to both tables, you could place pairs of fields on top of each other, one field of each pair from a different "detail" table, if that is a format that can work for you.

            • 3. Re: Link one field to two tables
              ianmanning
              ianmanning Jan 12, 2015 1:34 AM (in response to ianmanning)

              Thanks for your answers. Unfortunately, I do need to / would like to,  enter data via the portal.

              I guess my only options would be to combine the data from the two tables into a single table or to have the link table copy all the relevant data from the attendee to its own record after selecting the attendee from a layout showing both attendee tables.

              If I combine the tables what is the easiest way of recreating the Static links that are currently present? 
              ie. currently
              Table1 Field1 links to table2 field1
              Table1 field 2 links to table3 field1

              If I combine Tables 2 and 3 I now need to be able to add a field containing the old table name and an "and" into the relationship
              Table1 Field1 links to CombinedTable field1 with relationship oldlink AND TableName
              Table1 field 2 links to CombinedTable field1 with relationship oldlink AND TableName

              TableName is a constant for each link but currently does not exist. Do I have to create a new Global Field for each variation or is there a way of adding a constant into the relationship?

              Thanks
              Ian

               

              • 4. Re: Link one field to two tables
                philmodjunk
                philmodjunk Jan 12, 2015 9:46 PM (in response to ianmanning)

                You can edit the attendees data in this portal (I was a bit simplistic in my last post). But you cannot edit the link between attendees and events in this portal as you are "reaching through" the join table to fields in Attendees.

                To edit the links, you would use a portal to the join table--which can also include fields from Attendees as well.

                The key difference is that deleting a record from the portal to Attendees deletes that record for that person and they vanish from all events in your system. If you delete a record in the portal to the join table, no attendees data is lost, but you delete just the link between the current event and that attendee.

                • 5. Re: Link one field to two tables
                  ianmanning
                  ianmanning Jan 13, 2015 2:51 AM (in response to ianmanning)

                  Hi Phil,
                  I think I understand what you are saying.

                  Put a Portal on the Events table showing related records from the join table, place fields from the two attendee tables on the portal rows so that if the attendee is from table3 it will show through the blank field from table2 placed on top of it. (if there was a related record in both tables, not possible as the key is a UID, you would see both names in the same space)

                  Am I correct in thinking, for this to work each new link record would have to be added outside of the portal because I cannot access the fields on the bottom layer in the portal row.

                  This wasn't quite what I wanted but I can live with it.

                  Going back to my previous reply is there an easy way to add a constant into a relationship ie. relationship =
                  Table 1, field 1  =  table 2, field 1 AND "Editor" = table 2 , field2
                  I currently add a global field to Table 1 and put Editor in the field using a script at startup it works but is a bit clunky and I was hoping there was a nicer way of doing it.

                  Thanks for your support and advice.

                  Best regards

                  Ian