Your layout would be based on the Link Table and then you can show information from the other two tables.
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.
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?
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?
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.
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.