Sorting on related records where not all records have related records (1-to-1 join)
I am running into an issue with trying to sort records on a layout. Rather simple structure:
TableA <1=1> TableB
Relationship has 'Create related records in B' checked ON.
Layout is based in TableA showing a variety of fields from both A and B.
The problem comes into play when I sort on a field that is based in TableB. Call it TableB::Done. It is a simple checkbox, using a value list of "1". That is, when it is unchecked, the value in the field in empty. (I wish FM had a better binary datatype.) Now, not all records from TableA are going to have related records in TableB yet.
So I tend to get 3 types of values to sort: Done = 1, Done = "", and <Done does not exist; no related record>. The problem is that in sorting, either ascending or descending, the Done=1 values never sort to the top of the list. They always get buried in the middle of the records. So it appears that in 'ascending' order Done="" records come first, but in descending order <non-existant> records come first.
I tried creating a sort helper field in TableB so that I would have Sort = 1 (Done = 1), Sort = 0 (Done = ""), and then a non-existant record would have no field at all, of course.
I REALLY do not want to create a field in TableA to try and handle this. That table is already way too wide with fields and I don't want to contribute to the problem.
I also can't just re-base the layout in TableB, as it is important to show TableA records whether or not they have TableB relations.