0

Having records sorted in Table by more than one column

In a table view we can sort records by one column only.
We have to click the column heading and then click Ascending or Descending box.

Suppose now that 2 columns in my table are CN (as Client Number) and IN (Invoice Amount).

and I have the following data:
CN     IN 
1025 315
0650 200
1025 420
1025 320
1002 830

And I want these records sorted first by client (ascending or descending) and then by invoice amount (ascending or descending).
I should get the following (suppose ascending for clients and descending for invoice amount):
CN     IN 
0650 200
1002 830
1025 420
1025 320
1025 315

How can I do that?

8 replies

null
    • Sean
    • 4 yrs ago
    • Reported - view

    This link explains how...

     

    https://ninoxdb.de/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/can-you-do-secondary-sorts-within-a-view-5bf45478d91e505c0994c84e

    • JGM
    • Jose_Monteiro
    • 4 yrs ago
    • Reported - view

    I thought of something similar to what is suggested in that thread (field concatenation), but I hoped there was an easier way to do this kind of sorting.

    In this case, we need to ensure that each field has a fixed length before concatenation, otherwise, we can get wrong results.
    If they are not fixed length, we need to format them first to make them fixed length.
    Care must be taken with strings, especially if we have non-ASCII characters.

    Thanks for your help.

    • JGM
    • Jose_Monteiro
    • 4 yrs ago
    • Reported - view

    About sorting only string fields.

    If we have two string fields, it is not possible, as far as I understood, to sort one in ascending order and the other in descending order after concatenation.

    The solution given in the topic above works with number and date fields.
    Unless there is another trick to do this. :)
    If anyone knows how to, please share it with us.

    Thank you again for your help.

    • Sean
    • 4 yrs ago
    • Reported - view

    You probably won't like it... :)

    This is a workaround that requires you to add another field for ranking that would be used in the concatenation instead of the actual descending ordered field. You could update the ranking field using the console, if you like, and make sure you start off with a number large enough that the left most digit is the same for all numbers.

     

    let recCount := 5000 + count(select YourTable);
    for r in (select YourTable) order by YourDescendingField do
    r.(Number := recCount);
    recCount := recCount - 1
    end

    • JGM
    • Jose_Monteiro
    • 4 yrs ago
    • Reported - view

    It solves this sorting issue for the records we already have in the table.

    After adding more records we have to run the script again whenever we need to look a the table sorted this way.
    Perhaps the best solution is to have a Form View with a button and this code in the "On click" field to be it executed when we click the button.
    Nevertheless the best solution would be perhaps allowing sorting in a Form View by more than one field like:
    select Table1 sort by Field1, Field2, ...

    Thanks.

    • Sean
    • 4 yrs ago
    • Reported - view

    I agree and I'm a little surprised that sorting on multiple columns hasn't been implemented yet. Meanwhile, it is flexible enough to let users develop workarounds to solve this kind of problem. As I understand it, the secondary sort will only matter if there are duplicates in the primary sorting field. I modified the code so that each duplicate will have its own indexed range of numbers and I added code to put in "Trigger after update" for the secondary sorting field. Updated code for all records...

     

    let primarySort := unique(((select YourTable) order by PrimaryField).PrimaryField);
    let curCount := 0;
    let psCount := count(primarySort);
    let recCount := 100 + count(select YourTable where PrimaryField = item(primarySort, curCount));
    for i in range(0, psCount) do
    for r in (select YourTable where PrimaryField = item(primarySort, curCount)) order by SecondaryField do
    r.(Number := recCount);
    recCount := recCount - 1
    end;
    curCount := curCount + 1;
    recCount := 100 + count(select YourTable where PrimaryField = item(primarySort, curCount))
    end

     

    This is for "Trigger after update" in the secondary sort field...

     

    let tPrimaryField := this.PrimaryField;
    let recCount := 100 + count(select YourTable where PrimaryField = tPrimaryField);
    for r in (select YourTable where PrimaryField = tPrimaryField) order by SecondaryField do
    r.(Number := recCount);
    recCount := recCount - 1
    end

    • JGM
    • Jose_Monteiro
    • 4 yrs ago
    • Reported - view

    Excellent piece of code. :)

    Clear and simple.

    I've been thinking about it, but I haven't been able to get this far.

    I still don't feel comfortable with the Ninox programming language.

    Usually I know what I want, but I don't know how to code it.

     

    Thanks for your help.

    • Sean
    • 4 yrs ago
    • Reported - view

    Thank you and you're welcome. One more line you can add to the end of the "Trigger" code is openTable("YourTable"). This will refresh the sort and it will update the table display if that group is currently in the table view.

Login to reply

Content aside

  • 4 yrs agoLast active
  • 8Replies
  • 2448Views