2 Way References

[Tyson_Broyles]

First of all if there is a technical term with this structure please let me know so I know how to search it better. 

Basically the structure is like so:

There are 3 tables. 
Property
Units
Lease 

Property can have many units
Property can have many leases that also reference units for each property using Enumlist via: 
               
                   SELECT(Units[Unit #],[Property Name]=[_THISROW].[Property Name])

Now in I have created a view that I want to based on the Units Table, e.g. a Rent Roll. The units table would then be able to reference the leases and pull active lease data into the units table.

The other nuance is that the Lease can select multiple units. E.g., I lease two units in 1 building. So in the view, 2 units may have the same lease data. I can eliminate this feature if it isn't practical. 

To restate my ultimate goal, I would like to be able to create a view based on the Units table that pulls any lease data into the inline view. 

I am stuck on how to get data connected back to the unit as a list or multiple values. I do have some formulas that reference the lease to get rent data points that pulls into the Units table so I think it is possible. Just trying to get my thinking straight on this. 

Best,

Tyson

[TeeSee1]

So your Lease table has an ENUMLIST that references units, then you can try a VC in the untis table with the following expression

SELECT(lease[lease key], 
 AND(
  IN([_THISROW], [enumlist of refs to units]),
  [lease status] = "Active"
 )
)

[Tyson_Broyles]

SELECT(lease[ID], 
 AND(
  [Property Name] = [_THISROW].[Property Name],
  Text([Unit #]) = [_THISROW].[Unit #],
  [Status] = "Active"
 )
)

So to elaborate on this issue a little more. The reason this formula does not work is because it converts the Unit#(s) to text. If there are multiple Unit #s then it will convert them into one long text string thus never being able to compare to a single Unit #. 

I do like the idea of the In function, but I suppose I don't understand completely how it searches. Does it work similar to a Contain function? So "In this Enum List, if this unit is selected, then ref?"  

[TeeSee1]

If you want to find out how AppSheet functions work, consult this help site..

https://support.google.com/appsheet/answer/10107681?hl=en