Many to Many relationship setup

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

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.

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

Oh I see.

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

1 Like

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

Thanks

1 Like

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

2 Likes