Combination of ref and a filter

[Hinse_Wouters]

Hello guys,

The reason why this is difficult is:
There is a parent table (ACCOUNT)
There is a child table (Abbonnement)
There is a grand child (Wasbeurt-Abbonement)
But there is also an grand grand? chil table (Nummerplaten)

AND

There is also a connection between
The parent table (Acount)
The grand child/ child table (nummeplaten)

and now i want to have a ref column in the table (wasbeurt-Abbonement) with a reference to the table nummerplaten where only the account is the same we we connect the nummerplate to.

I hope this is clear…

Thank you!

[Aurelien]

Hi @Hinse_Wouters

Not exactly clear, but I will give it a try

Hinse_Wouters:

The reason why this is difficult is:
There is a parent table (ACCOUNT)
There is a child table (Abbonnement)
There is a grand child (Wasbeurt-Abbonement)
But there is also an grand grand? chil table (Nummerplaten)

in Wasbeurt-Abbonement: Virtual column: “Account” (“Ancestor” )
[Abbonnement].[ACCOUNT]
in Nummerplaten: Virtual column: “Account”
[Wasbeurt-Abbonement].[Abbonnement].[ACCOUNT]
or
[Wasbeurt-Abbonement].[Account] if you made first suggestion

in Wasbeurt-Abbonement: Virtual column “yourQuestion”, type List, subType Ref:

FILTER("Nummerplaten",
  AND([wasbeurt-Abbonement]=[_THISROW],
      [Account]=[_THISROW].[Account]
  )
)

You may want to share screenshot of your table to make it clearer.
For reference:

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

help.appsheet.com

List Dereference

Dereference the items of a list in bulk to produce a list of the resulting values.

[Hinse_Wouters]

Hello,

Thank you for your help. We are in the good way, but we aren’t there (my fault)…

So:
When we make an virtual column in wasbeurten_abonnement → We can’t chose wich “nummerplaat” (=licensesplate) we chose in the form of wasbeurt_abonement…
Look for image (i have made an ref column ‘nummerplaat’ with refence to nummerplaat table), that is where i’m looking for, but it looks like i can’t filter only those nummerplaten that are linked to the correct acount.

(here i can’t filter on acount where my wasbeurt_abonement is linked to and y nummerplaat is linked to…)

And to be clear → Wasbeurt -abbonement is a table, not an column. When i give this in:
“FILTER(“Nummerplaten”,
AND([wasbeurt-Abbonement]=[_THISROW],
[Account]=[_THISROW].[Account]
)
)”

[Aurelien · Accepted solution]

OK, thanks for futher explanation @Hinse_Wouters

The picture of the specs is helpful.
What about that ?
In Wasbeurt_abbonement, this will give you a list of Nmmerplaten that matches with abbonementen’s account:

FILTER("Nummerplaten",
  [Account]=[_THISROW].[Abbonementen].[Account]
)

Please note you have to enter this expression in the valid_If expression field.