Add a new row to a ref table

[Hinse_Wouters]

Hello, 


I have a question about row refrence (but not the normal way i think?) 

I have: 

TABLE 1: ACOUNTS (A lot off) 
TABLE 2: SUBSCRIPTION (Every Acount has a lot off subscriptions) 
TABLE 3: USES OF SUBSCRIPTION (Every SUBSCRIPTION has 10 USES) 
TABLE 4: LICENSENS PLATES (Every acounts has a lot of licenses plates) 


When i ad one USE (table 3) -> I select one of the licensplates that was linked tot the acount where the license plate was linked to before this. (THis works fine) 

But no when i ADD a LICENSEplate when i'm adding a LICENSE plate in the form of TABLE 3 -> It opens a FORM for THE LICENSE PLATE, but it doesn't fill the ACCOUNT name to the LICENSPLATE FORM
Is there an option to change this? 

PHOTO TO BE CLEAR: NUMMERPLAAT = LICENSEPLATE in DUTCH. 

HERE I PRESS "NEW" (I'm no in the subsription of the company "TRANSJOINT"

Here i need to select Transjoint again.. Is there an option to atomaticly add the "TRANSJOINT"? 

[Joseph_Seddik]

Does each Licenseplate belong to only one customer ? or can one plate be assigned to more than one customer? 

[Hinse_Wouters]

Only One customer, but each customer had a lot of licensplates. 

[Joseph_Seddik]

OK, thank you. 

And what is the relationship between Subscription Use and License Plates? 1-to-1? or can one License have many Uses? 

The same questions for the relationship between Subscription and Account please. Does every Subscription have one Account only? or can be shared across several accounts?

[Hinse_Wouters]

That is a diffucult one.. 

There is no real connection between LICENSE PLATE and USES Only between Account and license plate / Acount and Subsription / Subsription and Uses 

SO i thinks: 
                        Account (Parent) 
Subsciption (child)                  LICENSE PLATE (Child) 

Subscription use (GRANDchild) 

and the only connection between Subscription use and License plate is: A refrence column (And here we use Accounts fore) 


Every subscription has One Account, correct. 

[Joseph_Seddik]

There is no real connection between License and Use, yet you established one through a reference column? Why? 

But in any case, since each Subscription belongs to only one Account, when you are in Uses, you can easily get the Account through a dereference expression: 

[subscriptionID].[accountID]

https://help.appsheet.com/en/articles/1090811-dereference-expressions 

[Hinse_Wouters]

I think we miss the problem here: 

The only problem is: when i press on "new" to add a new license platee -> The account Isn't filled automaticly.. So i need to look for it again. 

The reason why i don't link LICENSE PLATE to SUBSCRIPTION is -> Every 10 SUBRICRIPTION USES we make an new SUBCRIPTION (because it only stands for 10 uses)... 

Is there any change i can send a video? 

[Hinse_Wouters]

https://capture.dropbox.com/h7TvOgi9aDpjGNIk?src=ss
Explanation movie  @Steve Coile

[Joseph_Seddik]

Hello Hinse, 

Thanks for the video, it clears a lot; nested forms get things complicated. There's more than way to do this, but the easiest in my opinion is:

In Subscription Use table, add a column: "SubsAccount", and set it's formula to: 

[subscriptionID].[accountID]

In the License table, set the initial value of the Account column to: 

ANY([Related Subscription Uses].[SubsAccount])

Where "Related Subscription Uses" is the name of the automatically-created reverse-reference column in the Licenseplate table. 

[Hinse_Wouters]

Hello, 


Thank you so mutch for helping me. 
I have one other problem with this... 

I have 3 types of Subscriptions ( i have abonnement, current accounts and per turn) when it is easier for you you can also use "Related Subscription Uses1"/"Related Subscription Uses2", ... 


So do i need to fill the 

In the License table, set the initial value of the Account column to: 

OR(ANY([Related Subscription Uses1].[SubsAccount]),ANY([Related Subscription Uses2].[SubsAccount]),ANY([Related Subscription Uses3].[SubsAccount]))

[Joseph_Seddik]

The use of OR is incorrect; OR returns a YES/NO value. 
If you have the three types in the same table, then you don’t need any modification. Otherwise, if you have three different tables, then you should use IFS() checking each corresponding reverse-reference column with ISNOTBLANK([Realted ….])

[Hinse_Wouters]

Hello, 

Thank you.. It works for The first one but not for seconde one.. 

I show you the code: 
Abonnement = Subscription 
Lopende rekening = current accounts
per maand = Per turn

 Is there an solution for this? I did i do something wrong. 

I always get the same account as return... 

When i need to make an other movie, please send me 

[Joseph_Seddik]

Please show me the columns of your three tables from the Editor, and I will write down the corresponding expression. Thank you. 
Your expression will always return the same because you are checking against the whole account list not the ones related to this subscription. 

[Hinse_Wouters]

I hope this is clear, my next app will be in english


Subscription USES

EACH TURN USES 

ACOUNTS 

current accounts USES

 

 License PLATE 

[Joseph_Seddik · Accepted solution]

Hi Hinse, I'm enjoying reading Dutch 🙂 

The only issue is I wanted to see the reverse reference columns, these are automatically added columns with App Formula beginning with: REF_ROWS, but I couldn't find them in the photos; probably truncated. 

It is OK. I'll make an analogy of your data structure and provide the expression based on this analogy, I hope it would help you. 

1. You have a table Accounts, owners of Cars (License Plates). 
2. You have a License Plates table, each belonging to one Account. 
   1. Table License Plates has a Ref Column "accountID" pointing to Accounts table.
   2. Therefore, Accounts table will have a system-generated reverse-reference virtual column named: "Related License Plates".
3. You have a Subscriptions table, each subscription belongs to on account:
   1. Subscriptions table has a Ref Column "accountID" pointing to Accounts table. 
   2. Therefore, Accounts table will have a system-created reverse-reference virtual column named: "Related Subscriptions".
4. Table Subscription Uses I, each row belongs to a Subscription. 
   1. Subscription Uses I has a Ref Column "subscriptionID" pointing to Subscriptions table. 
   2. Therefore, Subscriptions table will have a system-generated reverse-reference virtual column named: "Related Subscription Uses I". 
   3. Subscription Uses I also has a Ref Column "licensePlate" pointing towards License Plates table.
   4. Therefore, License Plates table will have a system-generated reverse-reference virtual column named: "Related Subscription Uses I".
   5. You should add a new virtual column named: "subsAccountID", with the following App Formula: [subscriptionID].[accountID]
5. You also have Subscriptions Uses II and Subscriptions Uses III tables, containing (among others) the same three columns as in Subscriptions Uses I in point 4. above. 

Based on this setup, here's the expression to use as Initial_Value for column "accountID", in table License Plates:

IFS(
   ISNOTBLANK([Related Subscriptions Uses I]), 
      ANY([Related Subscription Uses I].[subsAccountID]), 
   ISNOTBLANK([Related Subscriptions Uses II]), 
      ANY([Related Subscription Uses II].[subsAccountID]), 
   ISNOTBLANK([Related Subscriptions Uses III]), 
      ANY([Related Subscription Uses III].[subsAccountID]), 
   TRUE, ""
)

[Hinse_Wouters]

Thank you so mutch for this!