I have a date column in my events table and I want to group events by date
To make the groups more user-friendly I have a virtual column that returns text labels like “Today 3 Nov”, “Tomorrow 4 Nov”, or “5 Nov”
When I group by this virtual column the groups are sorted alphabetically instead of chronologically
Is there a way to group my events using the friendly text ( “Today”, “Tomorrow”, “5 Nov”), but still have the groups sorted in true date order?
Will something like this do?
This approach shown in the GIF below needs the following components to be added to the app.
Addition of a small read only table with 4 rows and two columns, a VC, an action and a deck ( or similar summary )view.
That definitely looks very interesting,
but I really want the grouping for a table view in desktop
I believe what you are looking for is not ( at least easily possible) with straightforward approach.
That is why suggested the above alternative.
One thing I have noticed recently is that Enum columns, when used for Grouping, sort based on the ORDER of the Enum values listed in the column config. If you want Ascending, it’s the order they are listed in the Enum Column config. Descending is the reverse order. You can re-organize the Grouping order by moving the Enum values in the list.
I wonder if this can be used with Enum values that are pulled in by an expression? In other words, make the column you are using to Group by an Enum type column. Then assign, into the Valid_If, an expression that grabs the values from all rows and sorted using an ORDERBY() function. It sould be a complex function. I’ll try testing it.
I’ve found this behavior inconsistent. Test carefully!
I was also initially inclined to use the enum approach. Realized that the enum will need to have a moving set of valid values and display values because the dates of today, tomorrow and the day after tomorrow will change on a daily basis. So the enum may need possibly a daily scheduled bot etc.
Anyway, this could be possibly done , (I mean adding moving values ) and I could be possibly missing something obvious that is easier. I am sure @WillowMobileSys will come up with some innovative approach. Will be keen to see his solution.
I put my thoughts above to a test and it did NOT work.
While I can sort the Enum list dropdown in the expected order, it appears that AppSheet does not using this ordering for Grouping. It reverts to text based sorting. See images below.
There are a couple of things you still can do:
The only way I know, without additional work, to get them sorted in your desired order is to make the text sort in that order. That typically requires prefixing with numbers 1,2,3,4,5, etc. It’s not very user-friendly and since your ordering is DYNAMIC, you have to update EVERY row daily.
If you are willing to compromise a bit. I do have another solution described below
Correct Order for Enum Dropdown
Attempt at using the Enum column for Grouping the data - Not correct order
******** Alternative Solution
The idea here is to implement a static list of Enum values in the column configuration. See image.
This would result in a view that looks like the one below. The “Today” and “Tomorrow” labels would stick to the top of the list - which might be good if these are the most important sets of rows.
The remainder of the rows would then be sorted by their date order.
Two things are required to make this work:
I hope this helps!
Thank you @WillowMobileSys for some new ideas.
Yes. I realised that the solution can be longer.
So suggested a summary view based solution. The VC will keep creating the dates expressions as the days shift and then linktofilteredview selects the desired records.
Of course, it can be personal choice of what one thinks as compact approach.
If you are OK with adding an index at the head of the string, then here is another idea.
Create a VC using the INDEXOF() technique by @Steve (described here), something like
"["
& COUNT(
SPLIT(
ANY(
SPLIT(
(" , " & SORT(SELECT(ORDERS[order.date],TRUE,TRUE)) & " , "),
(" , " & [order.date] & " , ")
)
),
" , "
)
)
& "]: "
& SWITCH(
[order.date],
TODAY(), "TODAY - ",
TODAY() + 1, "Tomorrow - ",
""
)
& TEXT([order.date],"D MMM")
to be used as a grouping column.
Then you get a table view something like this. (adding formats would also help users, I suppose)
Note. The computation of the VC seems a bit heavy, so test it well and make sure you have a good housekeeping process in place.
Hi @TeeSee1 ,
May be I am missing some point. However if a prefix in the form of [1] for previous dates, [2] for Today , [3] for Tomorrow and [4] for later dates is to be applied, then I believe the following simpler expression for the VC will work. I mean this expression does not have the SELECT() statement in the VC. The VC computes based on the current row’s date and adds the necessary prefixes and formatting of the date in D MMM format.
IFS(
[Order Date] <TODAY(), CONCATENATE(“[1]: Past Dates”),
[Order Date] =TODAY(), CONCATENATE(“[2]: Today - “, TEXT([Order Date], “D MMM”)),
[Order Date] =TODAY()+1 , CONCATENATE(”[3]: Tomorrow - “, TEXT([Order Date], “D MMM”)),
[Order Date] =TODAY()+2 , CONCATENATE(”[4]: The Day after Tomorrow - “, TEXT([Order Date], “D MMM”)),
[Order Date] >TODAY()+2, CONCATENATE(”[5]: Future Dates”)
)
The grouping looks like below in the test results
If the requirement is to group the events into a certain number of chunks, then, your solution makes sense.
My assumption was to group them into individual dates, which requires a different approach.
Got it @TeeSee1 . Thank you.
I now understand the thought behind your solution - grouping by each individual date with special emphasis by way of additional prefix on the immediately following dates dates of today, tomorrow etc.
