Many to Many relationship setup

[Olivier]

Hi,

I am trying to do a very basic many to many reference but Appsheet keeps on creating the wrong links and ignoring the ones I created :’(.

Here is what I do :

1. TABLES : 3 tables : Investors, Properties and Relations. The table Relations should refer to the Investors and Properties tables in order to indicate who owns what and in what percentage.

2. KEY : The key of Investors is Investor_ID, the key of Properties is Property_ID and Relations is Relation_ID

3. VARIABLES :
   in table Relations, I have 2 variables set as “Ref” : Investor_ID and Property_ID
   in table Investors, I have a variable “Relation_Link” set up as “List” with the formula "REF_ROWS(“Relations”,“Investor_ID”)
   in table Properties, I have a variable “Relation_Link” set up as “List” with the formula "REF_ROWS(“Relations”,“Property_ID”)

Normally, from what I have seen in app example & forum, everything should be fine. However, Appsheet keeps on creating 2 new ref variables in the table “Investors” every time I save the application + ignore the link to Investment table. Pretty annoying. Been there for 3 hours now.

What do I do wrong ?

Best

[Marc_Dillon]

Appsheet auto-generates the reverse reference REF_ROWS virtual columns, when you set a Ref type column in another table. It kind of sounds like you maybe created these yourself? In either case, try the following. Change both Ref-type columns in Relations to Text. Delete all REF_ROWS columns in Investors and Properties. Save. Change the 2 columns in Relations back to Ref. Save again.

[Olivier]

Hi Marc,

Thanks for your answer.

I tried doing what you described : ie replacing the refs. by a Text, then deleting the reverse references and then resetting the refs.

However Appsheet keeps on creating the 2 reverse references in the same table (Investors) instead of creating one in each (Investors with INVESTOR_ID and Properties with PROPERTY_ID). What is odd is that there is no “PROPERTY_ID” variable in table Investors, so I don’t understand what is going on with this reverse ref. Does not make sense to me.

The 2 reverese refs that are created automatically in Investors table

I am sure Many to Many relationships does work in AppSheet as I saw them working. Is there a special way to set them up properly from scratch ?

Best

[Marc_Dillon · Accepted solution]

Oh I see.

Did you by chance point BOTH Ref columns to the Investors table, instead of one to the Property table?

[Olivier]

Fantastic !! The lesson : do not forget to setup the parent table in the Ref variable

Thanks

[MultiTech]

Just dropping in a link to a summary post I made, which contains a sample app, about how to accomplish something like this.

Many-to-Many Relationships Tips & Tricks 💡

AppSheet be default supports One-to-many relationships: you have one record (say an Order) that has many related records (order details). But there is another relationship connection type that allows you to connect many records to many records you have a table of records (say Products) where each record can be related to many records in another table (say orders). this means when you look at a product, you can see a bunch of related orders - and when you look at an order, you will see a bu…