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:
prop|bldgdetails|units|UnitDetails
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.
Did you look up the getSummary function in Help? I ask that because you appear to be trying to use getSummary in a way that it is not designed to work.
GetSummary is solely a means for accessing the subtotals you might see in a report's sub summary part so that it can be used in a calculation. It isn't something that you can use to conditionally access records.
A summary field in unit detail will return a total when accessed from units or building details that returns a value computed from all the records in unit detail that are linked to the current record in Bulding Details or Units. Think of it this way, I you put a portal to unit details without any portal filtering on a layout based on either of these other table occurrences, the summary would be computed from the records shown in that portal.
To selectively compute a total for only some of those related records, such as all related records for unleased units, you'd need to do one of the following:
1) set up a relationship that only matches to unleased units
2) set up a one row portal filter with the summary field from unit details located in the portal row and a portal filter that only filters for unleased units
3) Use executeSQL with an aggregate function (sum, count, max...) and a Where clause that only specifies unleased, related unit detail records
4) Use a script that finds those records on a unit details layout, sets a variable to the value of this same summary field and then returns to building details or units and sets a field to that value. This script has to run every time you make a change to the unit details table that affects this calculated value.