Sort Records by Date when a different field is used for Data Grouping

[kpcollier]

I'm trying to figure out a way to sort my grouped data in a tabular report by a Date field. The Date field is not the field being grouped.

Pretty much, whatever 'group' has a record with the earliest Estimated Install Date should be listed first. Then, the group with the 2nd earliest date, followed by the latest date.

 

Any help or ideas would be appreciated.

[futurist]

Hi @kpcollier

You can have a Task that goes through each of the records and assigns them a number (to be stored in a new field; I named it "Order_By_Date" on my example) depending on at which order the date comes in. 

once that's been assigned, my records would look something like:

 

and i can group records using that order_by_date. 

 

you could even set the "Order_By_Date" field to text, so instead of it just containing the number, you could even add the name of the category next to it, so in my example, instead of just 1, it would say "1. Complete" "2. Pending" and so on, so that people viewing the report datapage would also know the category based on the grouping. So on the Trigger, you'd simply append the category right beside the counter.

 

Hope that works for you!

[kpcollier]

Hi @futurist,

This is a really neat workflow. Thanks for sharing. 

However, I'm having trouble getting this to work with the logic that I need.

I see now that the image that I shared is not the greatest example. Estimated Install Date is all the same date for each record under a group - however this is not always the case. Sometimes, the estimated install date will be different for the different records under the grouping. 

So, I need to almost group these by Item in the task (Item is the field being grouped in the image) so that all of the items/records in the group get the same sort number.

[futurist]

Hi @kpcollier,

So from what I understand, if records in a group might have different dates, should each of these groups be represented by the date within that group that is the most recent? What if another group is represented by the same date? Should the Task then count how many records of that date that each group has, then whichever has the bigger number, they should have the higher ranking? i'm not sure if i'm straying too far from what you are trying to do though

[kpcollier]

On 7/29/2022 at 4:39 PM, futurist said:

So from what I understand, if records in a group might have different dates, should each of these groups be represented by the date within that group that is the most recent?

Yes, like this. Which ever record has the earliest date, the group it belongs to will be at the top. 

 

On 7/29/2022 at 4:39 PM, futurist said:

What if another group is represented by the same date?

I didn't really think about this, but if the dates are the same, then it doesn't matter what order they are. They can revert back to default or just have no/random sort. These groups usually will be sequential.

 

On 7/29/2022 at 4:39 PM, futurist said:

Should the Task then count how many records of that date that each group has, then whichever has the bigger number, they should have the higher ranking?

This would be okay, but the sorting of groups that are the same date is not very important, like mentioned above, so it may be too much. But, this is a good idea that might make it quite easy just doing a count of the records to give it a set sorting place.

[futurist]

Hi @kpcollier,

I modified the Task I provided:

 

Basically what this does is first, it grabs one record for each category (in my example it's Status) with the latest date (these basically act like representatives of each category), then stores all of those into a virtual table. Then, I sort that table to descending order based on the date (so that the most recent ones come at the top; this is important). Finally, I added one final For each loop that goes through each records in the virtual table, and add the sort order accordingly based on the count (this is why I had to order the records in the virtual table first, so that the For each loop goes through the most recent records down to the least recent records). it stamps the sort order for all records of that category.

 

[kpcollier]

@futurist This is awesome, you are definitely on another level! Thank you very much. I am going to implement this, and try to get it to work on triggers as well. 

[futurist]

I dont think this will work if you plan to have this as a Trigger within the same table, @kpcollier. this will lead to a recursive error. unless you plan to set up the trigger on another table.

[kpcollier]

16 hours ago, futurist said:

this will lead to a recursive error.

Yeah I very quickly found this out, haha.

This report is almost like a schedule. The grouped field is a name of a material, and the records in the group are specific tasks to do for that material, such as fabricate it and install it. Each of these tasks gets a target date, because these materials usually need to go in on a certain time and in a certain order. 

This is why I am trying to sort the grouped fields by a date, because otherwise, it is pretty difficult to go through the whole report to see which task is due next. The Task you shared definitely helps, but it limits us to waiting every hour for it to update instead of happening in real time.

[kpcollier]

Now I am thinking maybe a Formula will do the trick. If I can somehow get a formula to do what you did above and give each record a designation of 1.), 2.), or so on, I think I'd be able to use that formula field for the grouping. It would also update in real time because it is a formula. 

Not sure if this is possible, but I'm going to give it a try. 

[futurist]

I personally haven't done anything like what youre trying to do, @kpcollier, but as far as I know, formula fields only allow you to make calculations that do not require accessing data from other rows/tables (which seems like an integral part of what youre trying to do because youre comparing dates from other records). this is what calculated fields are for, but as you know, calculated fields cannot be enabled for grouping.

 

what im thinking is for you to have another table that is a direct copy of your original table, thats gonna act as a repository of the data (with their respective sorting order), which gets populated everytime you make an update/submission on the original table. then youre gonna use that duplicate table since the records there are now stamped with their sort order. you can do this using triggered actions on the original table, wherein the triggers fire for every insert/update of data, to populate the duplicate table.

[futurist]

Hi @kpcollier,

I tried setting up that trigger that runs on insert for my main table, which inserts the records (with their respective ranking) on another table (which is the exact same  structure as the original table). Here's my raw data from the main table:

 

 

And this is them sorted on the duplicate table: 

 

Here's the trigger (not gonna lie, this was definitely longer and more complex than i anticipated):

 

[kpcollier]

Thank you for the detailed post, @futurist. I will give this a shot. Much appreciated!!