So this might be me having missed a memo on a better way to do this. Iโm currently using this code to go up 3 levels, to the Great Gandparent table (Site), from the current table (Material):
ANY(Select(Site[Address],[SiteID]=
ANY(Select(Plot[Site],[PlotID]=
ANY(Select(PO[Plot],[POID]=[_ThisRow].[PO]))
))
))
Is there a simpler way?
[_THISROW-3].[Address]?
I though this only worked in Workflows? Whereas this is a standard column formula
I could have sworn they added the ability to use it all the time.
No longer!
When I create a grandchild table, I typically will include an extra Ref column for the grandparent. Populate it via initial value with a deref from its direct parent.
Hi @Marc_Dillon I understand your point. But I need to minimise Ref columns as some of the tables have thousands of rows. So I try and use formulas like this in forms
Make them an Enum - we can de-ref from enums (with the base-type of ref) now. :_)
Before we had this ability, what I would do would be to include a column to hold the ref - but make the column a text type; this way I could have the data present at that level (whatever level that may be), but it doesnโt cause the whole ref-auto-element stuff. Then I would just use a lookup() to get the specific piece of data I wanted.
Iโd do it pretty much as you are:
ANY(
SELECT(
Site[Address],
IN(
[SiteID],
SELECT(
Plot[Site],
IN(
[PlotID],
SELECT(
PO[Plot],
([POID] = [_THISROW].[PO])
)
)
)
)
)
)
User | Count |
---|---|
43 | |
30 | |
24 | |
23 | |
13 |