Referencing tables in many to many relationship

[Jan_Lambrecht]

I connected two tables (tableA and tableB) with a join tableC. In fact, tableA and tableB are connected now in many to many relationship.
Now, I want to display in the view of tableA (deck view) the related records of tableB.
How can this be done? For the moment, I only can display an inline view of the join tableC, but this table doesn’t has the properties of tableB.

Thanks in advance, Jan Lambrecht.

[Steve]

Try as the App formula expression of a virtual column in tableA:

SELECT(
  tableC[tableB],
  ([_THISROW] = [tableA]),
  TRUE
)

[Jan_Lambrecht]

Ok, thanks already.
I setted the properties of this virtual column as follows: type=list, element type=ref.
The problem is now that I can’t set the referenced table name. The referenced table name should be tableB.

Why can I not specify the referenced table name?
Thanks in advance, Jan Lambrecht.

[Steve]

Jan_Lambrecht:

The problem is now that I can’t set the referenced table name. The referenced table name should be tableB.

This is an app editor bug. Immediately after you save the changes, you’ll get a message that the base table wasn’t specified so a default was chosen. You can then go back to the column’s configuration and will find the option to choose the the table.

[Jan_Lambrecht]

Hi,

After I saved, the element type changes to ‘text’. When I changed it again to ‘ref’ I still cannot set the referenced table.
I’m a beginner. Probably I setted something wrong. Are there important things to keep an eye on?
The tables A and B have each a virtual column that contains a list of referenced entries in the ‘join’ table C.
‘join’ table C has two normal ‘ref’ columns that are pointing to the table A and table B.

[Steve]

Please post the following screenshots:

• The virtual column configuration including the TYPE DETAILS section.

• The virtual column’s entire App formula expression.

• The column list (from Data >> Columns) of Table A, showing at least the key column.

• The column list of Table B, showing at least the key column.

• The column list of Table C, showing at least the columns referencing tables A and B.

[Jan_Lambrecht]

Hi,

Here are the screenshots of the virtual column configuration of table A (called Cats)

Below the screenshot of the virtual column’s entire app formula expression:

Below is the column list of table A (called Cats)

Below is the column list of table B (called Prps)

Below is the column list of table C (this is the ‘join’ or ‘bridge’ table. (called BridgeCatsPrps)

Many thanks,

Kind regards, Jan Lambrecht.

[Jan_Lambrecht]

Hi,

Trying second time. Hope that the screenshots are included.

Here are the screenshots of the virtual column configuration of table A (called Cats)

Below the screenshot of the virtual column’s entire app formula expression:

Below is the column list of table A (called Cats)

Below is the column list of table B (called Prps)

Below is the column list of table C (this is the ‘join’ or ‘bridge’ table. (called BridgeCatsPrps)

Many thanks,

Kind regards, Jan Lambrecht.

[Steve]

All images are broken. Try downloading the images, then attach the downloaded files.

[Jan_Lambrecht]

ok, now they are jpg.
Here are the screenshots of the virtual column configuration of table A (called Cats)

Below the screenshot of the virtual column’s entire app formula expression:

Below is the column list of table A (called Cats)

Below is the column list of table B (called Prps)

Below is the column list of table C (this is the ‘join’ or ‘bridge’ table. (called BridgeCatsPrps)

[Steve]

Thanks for the screenshots! Everything looks correct, so I see no reason for the EnumList base type to switch to Text. I’m afraid I’m going to have to recommend you engage support@appsheet.com for help with this.

[Jan_Lambrecht]

Hi,

Ok, thanks. I will contact the support for this.

Kind regards, Jan Lambrecht.