Sum Select (Multiple References)

Tyson_Broyles · 01-06-2022 01:25 PM

Hello, I am trying to detemine how to best sum select a ENUM LIST field type with multiple selections. The exact scenario is:

I am trying to add a LEASE to a UNIT and reference the Sq. Ft. of that Unit on the LEASE table. The following formula works great for a single selected UNIT.

ANY(
    SELECT(
        Units[Sq. Ft.],
        AND(
            [Property Name] = [_THISROW].[Property Name],
            [Unit #] = [_THISROW].[Unit #]
        )
    )
)

I do have a scenario where multiple UNITs are selected. In this case the formula does not work because it wants to match the UNIT # to this Row which in this case is multiple UNIT #s.

I understand why it doesn’t work, but not sure the best path forward.

Other Data:

LEASE is it’s own table
UNIT is it’s own table

The LEASE form has a selection field for UNIT that is an ENUM LIST field type

Steve

Try:

SUM(
  SELECT(
    Units[Sq. Ft.],
    AND(
      [Property Name] = [_THISROW].[Property Name],
      IN([Unit #], [_THISROW].[Unit #])
    )
  )
)