You can create a calculated field with the value -1 and use that the third matching key for PreviousRecordFlag.
I mostly use boolean logic for cases like this - so perhaps consider using 0/1 rather than -1.
I have a field "IsTrue" on every table that I can use to match with any boolean trues; for false, you can then use IsTrue ≠ BooleanFlag.
David Wikström Thank you. I appreciate the great advice. Unfortunately, I must still not have it quite right. I still see all records in the CurrentUnitOwner table, rather than the subset. I was able to get the subset in a portal though.
Unfortunately, I must still not have it quite right. I still see all records in the CurrentUnitOwner table, rather than the subset. I was able to get the subset in a portal though.
If you can get it to appear in a portal, then it's working. Sounds like you are expecting a table occurrence based on UnitOwners but with a different name to magically list only current owners. Your relationship graph does not work like that.
Since you are basing your layout on the UnitOwners table, you will always see all records in that table when in a layout based on that table, regardless of the relationships and Table Occurrences you use. Viewing through a portal will filter based on your matching fields.
So, as I see it you have a few options:
1. Script a Go To Related Records starting from your CurrentUnitOwners TO and display using your UnitOwners table. You will need to script any finds to first GTRR then constrain found set based on new find criteria. Otherwise any new find request will be working from ALL records in the table, not just the current owner records.
2. Similar to 1., but script a find to first search for >-1 in the sold flag field, then constrain found set as above.
3. Create a new TABLE for CurrentOwners and script the addition/deletion of records when the status of the sold flag field changes in the UnitOwners table. This table need only contain a foreign key linking back to the UnitOwners table.