Going up 3 levels

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.

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.

1 Like

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

No longer!

1 Like

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])
          )
        )
      )
    )
  )
)
3 Likes

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.

3 Likes