Solved: Add a new row to a ref table - Page 2

Hinse_Wouters · 02-03-2022 06:14 AM

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

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.

You have a table Accounts, owners of Cars (License Plates).
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".
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".
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]
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, ""
)

View solution in original post