AnsweredAssumed Answered

Link one field to two tables

Question asked by ianmanning on Jan 9, 2015
Latest reply on Jan 13, 2015 by ianmanning

Like • Show 0 Likes0
Comment • 5

Title

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

philmodjunk Jan 9, 2015 1:32 PM

Correct Answer

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.

See the reply in context

No one else had this question

Outcomes

schamblee Jan 9, 2015 10:27 AM

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

Actions

philmodjunk Jan 9, 2015 1:32 PM

Actions

ianmanning Jan 12, 2015 1:34 AM

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

Actions

philmodjunk Jan 12, 2015 9:46 PM

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.

Actions

ianmanning Jan 13, 2015 2:51 AM

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

Actions

5 Replies

schamblee Jan 9, 2015 10:27 AM
Mark Correct
Correct Answer
Your layout would be based on the Link Table and then you can show information from the other two tables.
Like • Show 0 Likes0
Actions
philmodjunk Jan 9, 2015 1:32 PM
Unmark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
ianmanning Jan 12, 2015 1:34 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions
philmodjunk Jan 12, 2015 9:46 PM
Mark Correct
Correct Answer
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.
Like • Show 0 Likes0
Actions
ianmanning Jan 13, 2015 2:51 AM
Mark Correct
Correct Answer
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
Like • Show 0 Likes0
Actions

Link one field to two tables

Title

Post

Outcomes

Related Content

Recommended Content