Browse not sorting on calculated and lookup fields

[terryd]

I have 2 problems with browse sorting which I demonstrate in the attached modification of WEB3
Firstly I created a local variable tenthKB which displays on the browse. I calculate it in the SetQueuerecord embed point as MAIN:SizeLimit/10
If you click on the header to sort the sort doesn't.
Secondly I created a country table where the id of the country file is a field in the mailbox. I then created a field in the browse displaying the related country name. In my applications this is a very common occurence where I display a value from the parent in a browse rather than the ID  and frequently my users would like to sort by that parent name,
I look up the parent record in the SetQueueRecord embed. The Country names appear but not in order

If I sort the browse by name or mailbox number descending and then resort by TenthKb or Country name this browse reverts to Mail box number order



[attachment deleted by admin]

[kevin plummer]

Hi Terry,

sort on local variables has never worked and I think the reason is that it is not part of the view.

As for sorting on related fields from another table, you need to join the tables and enter the relationship (you do this by highlighting your table and pressing insert to add another table and then press edit to add the relationship). This is then added to the view and is then sortablke.

Cheers,

Kevin

[terryd]

HI Kevin
I do the joining on all my tables. I didn't do it in this example because it made no difference. I am attaching the same example where I have joined the two tables and the result is the same.

[attachment deleted by admin]

[Bruce]

Hi Terry,

You can only sort by fields which are in the View. (So local calculated fields are out.)
In the second example, as far as I can see, you are still loading the secondary record manually in the BrowseRow routine; Your code is;

    CTY:CountryID = MAI:CountryID
    GET(Country,CTY:PK_CountryID) 
    IF ERRORCODE()
        message(clip(error()))
        COUNTRY:CountryName = 'TBA'
    ELSE
    END

This code should be completely removed. Loading records like this is very inefficient, and very SQL unfriendly.
Rather add the Country table to the Mailboxes table (ie as a child table) in the Data pad, and remove it from OtherFiles.
then the country name becomes part of the view, and you can sort on it.

This might be a good tip for the Webinar on Thursday if I can remember it

cheers
Bruce

[terryd]

Thanks Bruce
Appreciated.
Yes I normally attach to the primary file  but I was just putting together an example quickly.
Anyway that explains the problem.