3 Replies Latest reply on Oct 28, 2013 4:47 PM by philmodjunk

    Sorting on related records where not all records have related records (1-to-1 join)


      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. 

           Any suggestions?