I have a table that i am trying to group by descending date and then within that group sort by a billing code alphabetically. The underlying billing code data is not in any kind of order in the raw table.
I understand how sorting only effects the group by value, and I have read that a query column lets you get around this by letting you sort that query first, and then use the sorted query as the collection source and then group by.
My issue is my collections are not respecting the query sort so even when my query is sorted by billing code, the collections still are listing items by the original table order which isn’t what i expected. I see in the data view the query has sorted the values correctly, they just don’t show up like that in the collection table.
I thought the sorted query data becomes the new table order for the collection when used as a source, is this incorrect thinking?
Are you sorting in the query, and then overriding that sort by sorting the collection itself?
Good question and maybe that is what i am missing. The default sorting says Table Order so I thought that means the order of the source which should be the query, but i guess if that means it is still going back to the raw table it would override the sort.
How do i use the sort of the query while still allowing me to group by sort on something the query isn’t sorted by?
Again i am trying to group by date, and sort by code. I have the query doing the code sort, then was going to group by date and sort that descending.
Does that sound right?
Not necessarily. If the query is sorted by date, and you left the collection defaulted to table order, I think it would respect the query sort. I think the problem is that you change the collection to sort by billing code, so now all those dates get mixed up to prioritize sorting by billing code, which overrides the sorting by date.
Try something once. First sort the query by billing code. Then apply sorting by date on the collection. Essentially doing the opposite of what you tried. I feel it might work. So really you are sort the secondary values first to get the query table in the right order for billing codes, before sorting the primary values which chunks those sorted billing codes into groups by date.
This doesn’t seem to be the case which is why I am not sure if this is working correctly.
I have simplified the data and made a separate app to try to understand how this works. I have a simple table with Billing codes (4 digit numbers), dates (random dates in Jan 2024), and costs (random costs) all in a random table order as the underlying data.
I created another table for the queries. One query that is just the data with no sorting, one that sorts by code, and one by date. I see in that table in the data view, all the queries columns have sorted the rows as expected.
I then create a layout view with Table collections, each using a different source query. With the default table sorting and no grouping, each collection has the same sort order of the original data and doesn’t seem to respect the query sort order.
Doesn’t seem to be working how i expect or how i have read others have used queries to sort and group data in collections.
I tried this and a number of different grouping/sorting of the collections and i can’t make rhyme or reason for how it is deciding how to sort things. I think to meet my needs, i have to group by date and sort date descending, but the billing codes in that group are always in the default table order instead of the query order.
I appreciate your input, Jeff.
Let me know if sharing the template would be useful, maybe you see something i don’t.
I’ll try to take a look and try it out myself later. There’s some other options, but since you are mixing ascending and descending, that kind of adds a layer of complexity.
Feedback from support is this is the expected behavior of the new Table collections, which seems like odd behavior as the lists and card collection do respect the query sort. The data grid also does not sort per the query sorting.
So using a query column to get around grouping and sorting is not possible with the new table and data grid collections it seems. Bummer.
Yeah, that’s a known problem with the new table. I really hope that would be sorted soon.
Ahh, didn’t realize it was a table or grid component. I still think it’s possible though.
- You need a math column to convert the date into YYYYMMDD.
- If you don’t have one, create a separate billing code table with billing code column and a text column with text based numbers ,including leading zeros, in reverse order (A=26, B=25…Y=02, Z=01).
- Create a relation linking the billing code in your data to that billing code table.
- Create a lookup column returning the text based number.
- Finally create a template column joining the math date and the lookup number. This will be the column you sort by in descending order.
Once I can get on my computer I can try to come of with a more thorough explanation, but hopefully this makes sense.
Interesting solution. I’ll give it a shot and let you know.
Ya it’s a bit confusing how the different collections are treating the queries differently. Hopefully that gets resolved so they are at least consistent between the different types of collections.
Thanks for the input!
1 Like