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 :
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.
KEY : The key of Investors is Investor_ID, the key of Properties is Property_ID and Relations is Relation_ID
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
Solved! Go to Solution.
Oh I see.
Did you by chance point BOTH Ref columns to the Investors table, instead of one to the Property table?
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?
Just dropping in a link to a summary post I made, which contains a sample app, about how to accomplish something like this.
User | Count |
---|---|
36 | |
33 | |
27 | |
23 | |
18 |