I have a commercial real estate solution. My Properties table has a one-to-one relationship with a Building Details table…as not all properties have a building (some are land, etc.). The Building Details table has a one-to-many relationship with a Units table. Think of that table as the suites/spaces within a building.
Not all Units are associated with a building as land can be portioned into numerous units (parcels) that can be separated, leased or sold off. So, as a result, I have a Unit Details table that is related as well. This results in a table relationship hierarchy as follows:
PROPERTIES Building Details Units Unit Details
Units Unit Details
Thus my table occurrence looks like this:
Now to the issue/challenge at hand. At the Building Details level (table) I need to determine how much space is available for sublease, how much space is available in multiple spaces that might make up the largest spaces available to lease, how much space was leased in the current year, etc. If I can properly do one of the calculations I need, I am confident I can figure out the others – although each may require a few additional steps.
I have a field in the Unit Details table such as AvailableSublease which is a checkbox field. If a Unit Details record has that field checked, then I need to total the RentableSF field for each record in the Unit Details table to return a total number of AvailableSubleaseSF in the Building Details table. Here is the calculation for the AvailableSubleaseSF field:
GetSummary ( prop|bldgdetails|units|UnitDetails::RentableSF ; prop|bldgdetails|units|UnitDetails::AvailableSublease)
Even though I have checked the AvailableSublease field, and have entered amounts in the RentableSF field, I get nothing…bupkis! I imagine the problem has to do with the calculation being over related records (in my case 3 tables), but I’m sure I am missing something. Thanks in advance for any clarity that the community can provide.