Many to Many relationship setup

AppSheet AppSheet Q&A
1

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

2

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.

3

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

3X_6_6_66af831a4d53ffb3b0c26d45b15d156b9ef0f060.png
3X_6_6_66af831a4d53ffb3b0c26d45b15d156b9ef0f060.png1203×362 26.1 KB

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

4

Oh I see.

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

3X_f_b_fb8b632c3916df4255a8e5c891874b26cdae128a.png
3X_f_b_fb8b632c3916df4255a8e5c891874b26cdae128a.png618×507 19 KB

1 Like
5

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

Thanks


3X_a_b_ab664d9e98779e71ea65744124b06a54b7646e57.gif

1 Like
6

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](https://community.appsheet.com/t/many-to-many-relationships/37050) 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…

3 Likes