Sorting is not working properly

[Anonymous]

HI All,

I have below formula -
Sort(GroupBy('Preventative Quarantines',
"cre08_name",
"cre08_cedula",
"cre08_quarantinestatus",
"PQName"
),
First(PQName).'Check In',
Ascending
)
The sorting is not working properly. I think First in the formula is causing problem is there any alternative for that? I am using that to access the column which is not in groupby.
Please help @RandyHayes 
@WarrenBelz 
@mdevaney 
@yashag2255 

[RandyHayes]

@Anonymous 

Your formula is not making sense...what is it that you are actually trying to sort?

Your GroupBy formula is going to produce a table with 4 columns.

The last column (PQName) is a table column that will contain all the records that match the first three columns.

So again, not sure exactly what you are trying to sort.  

If you are trying to sort the PQName table, then I would suggest pre-sorting:

GroupBy(
    Sort('Preventative Quarantines',
        'Check In'
    ),
    "cre08_name",
    "cre08_cedula",
    "cre08_quarantinestatus",
    "PQName"
)

This will have all of the records sorted by the 'Check In'.  The Group by will then have a Table column called PQName that will contain matching records...and they will already be sorted.

I hope this is helpful for you.

[Anonymous]

Hi @RandyHayes ,

Thank you for the response.

I have Preventative Quarantine table in which I have multiple columns like- Name, Cedula, Quarantine status , Check in date, check out date etc.

I want to group by with only these columns - Name, Cedula, Quarantine status and not other like Check in date, check out date etc.
Then I need to show all these columns (Name, Cedula, Quarantine status , Check in date, check out date) in gallery where I can filter the records and do the sorting on all columns. The screen looks like this-

Hope this makes sense now

[RandyHayes]

@Anonymous 

So I'm trying to track your last post back to your first post and am not seeing the connection to what it is that you want to sort.

However, it appears from the image that you have icons on each column of your Gallery and perhaps it is that you want to sort on those columns.

If that is the case, then you need to provide the column names to the sort function, and you need to move to SortByColumns and not sort.

So, a simple solution is to have a formula such as this on the OnSelect action of the sort icons:

   UpdateContext({lclSortColumn: "ColumnName", lclSortOrder:!lclSortOrder})

This will set a context variable to the name of the column (replace the above with the *real* name of the column from your datasource).  It will also flip/flop the sort order boolean context variable.

Now, your formula becomes:

SortByColumns(
    GroupBy('Preventative Quarantines',
        "cre08_name",
        "cre08_cedula",
        "cre08_quarantinestatus",
        "PQName"
    ),
    Coalesce(lclSortColumn, "defaultSortColumn"),
    If(lclSortOrder, Descending, Ascending)
)

NOTE: Based on your group by, you will ONLY be able to sort on the Name, Cedula and  Quarantinestatus columns.  

[Anonymous]

Hi @RandyHayes ,

Thank for the response. But I want sort the column which are not in the group by as well. How can I do that?

And defaultsortcolumn - is this dynamic? 

[RandyHayes]

@Anonymous 

First - defaultsortcolumn - that is meant for you to replace with the name of the default column that you want sorted by.  The point is that when you first run the app, lclSortColumn will be empty, so the SortByColumns function will fail.  The Coalesce allows you to specify the column that should be the initial sort.  Just replace it with that column name.

As for sorting on columns that are in the grouped records...that really doesn't make any sense.  The columns you are wanting to sort are part of the records of the group.  How are you even planning to display them?  

[Anonymous]

Hi @RandyHayes 

defaultsortcolumn - Understood.

I want to sort all the columns displayed in  the below gallery. 

I have Preventative Quarantine table in which I have multiple columns like- Name, Cedula, Quarantine status , Check in date, check out date etc.

I want to group by with only these columns - Name, Cedula, Quarantine status and not other columns like Check in date, check out date, camp.
Then I need to show all these columns (Name, Cedula, Quarantine status , Check in date, check out date) in gallery where I can do the sorting on all columns. 

The formula which you suggested works fine for group by and sorting the columns which are in the group by but now I want to sort the gallery records using the columns which are not in the group by but they are present in the Preventative Quarantine table like checkin date, checkout date and camp.

[RandyHayes]

@Anonymous 

But here is the issue - You have grouped your table by Name, Cedula, Quarantine status and nothing else.

So, all of your records in your table will have just those columns with their values.  You will also have a column called PQName in your record that is a table of all the records that match the other columns values.  

So, you might have, for example, 10 records in that column with 10 different Check In and Out dates.  What are you displaying in your Gallery??  You can't set a label to a table!  So what are you going to display in the check in and out labels??  I see you have values in the picture, how are you deriving them in the label?

[Anonymous]

Hi @RandyHayes 
So the gallery Item is below-
SortByColumns(
GroupBy(Search('Preventative Quarantines',UserName.Text,"cre08_name"),
"cre08_name",
"cre08_cedula",
"cre08_quarantinestatus",
"PQName"
),
Coalesce(lclSortColumn, "cre08_name"),
If(lclSortOrder, Descending, Ascending)
)
And the columns which are not coming from group by, I am using below syntax-
Last(ThisItem.PQName).'Check In'
This is how it is showing the values

 

[RandyHayes]

@Anonymous 

So the label formula is my point...you are only displaying the Last record value for Check In (and Out I assume).

My question then is, out of the variable amount of records that you could have in PQName, is the Last record the only one you are concerned with and the rest ignored?