I need help troubleshooting the sort order of...

(Jennifer Clem) #1

I need help troubleshooting the sort order of a grouped table view.

My app has a parent table (Events), and a child table (Attendees).

My desired view - All child records would be grouped by the parent record, then all those groups are sorted by the [DateTime] column in my Events table.

Right now, I can create a table view using my attendees table, grouped by the referenced event, which is great.

But it always shows up in alphabetical order, which doesn’t make sense.

They need to happen in DateTime order.

Is there a way, while using the child table view, to force the Groups of attendees for each event to be sorted by the time the event occurs?

(Grant Stead) #2

I would take view on the events sorted by date. Then the detail of the event show basically only the REFROWS children show 200 entries, and Bam. I do it all the time…

(Jennifer Clem) #3

@Grant_Stead I agree, however, I don’t want to have to click to see the information in the child rows.

I want to quickly scan one view, containing the name of the event plus the people attending all in one place.

Is that possible?

When I trigger a table view from the events table, and add the de-ref column for attendee’s, it just returns a count of attendees for that event, instead of individual names.

In detail view, I can only view 1 event at a time.

I’m trying to come up with a work around.

Can you turn a list output into text?

For instance, use a virtual column with a formula that retrieves the list of rows from the attendee table that have the same EventID as the current row.

Then in a second virtual column, could you convert that to text that then could be read from the primary table view?

(Jennifer Clem) #4

Looks great now, thanks @Aleksi_Alkio, @Suvrutt_Gurjar, and @Grant_Stead! So happy to have it working and not have to start from scratch.

(Jennifer Clem) #5

I wish there was an expression builder allowed for sorting on table views for groups, or some way to assign order of operations.

Group then sort, or sort then group.

I would be done already! lol

(Grant Stead) #6

What if you have a column in your attendees for parent date… Dref… Then group by event, sort by

event date…

(Grant Stead) #7

@Jennifer_Clem1 also, admittedly, appsheets group and sort is not very robust…

(Jennifer Clem) #8

@Grant_Stead Same Result, it is attempting to sort the child rows underneath each parent (group) instead of the group as a whole.

Returns a nice alphabetical list.

Any other ideas?

I’m stumped at this point.

I’ve spent a few hours just trying to figure this one piece out to make the app live.

Now I think I’m going to have to rework the whole app to utilize other view types, and it wont function nearly like i want it to.

(Suvrutt Gurjar) #9

Hi @Jennifer_Clem1,Based on my understanding of your need, you wish to group the attendees by event and the events need to display by the events’ date /time in the same view.

I request you to explore below

  1. I presume you are using Event ID to reference the event name in child table Attendees

Please pull in Event date time column and Event name from the parent Event table into Attendees table. ([Event ID].[Event Datetime] and [Event ID].[Event Name]

  1. Please create a concatenated column {Event Date & Name] in Attendees table. I created it as a virtual column in test app. Please put [Event Date] first in concatenation.

  2. In the table view of Attendees table, please put the group by column as this newly created concatenated column {Event Date & Name].

Please put ascending/descending order as you desire.

The view displays the events in either descending or ascending datetime order as per your setting with attendees listed in each group.

I tested the above works with event date value,but I believe its should work well with event datetime value as well.

Hope this is what you are looking for and helps.

(Jennifer Clem) #10

@Suvrutt_Gurjar Hmm, sounds promising. I will give it a try and let you know. I like it in theory.

(Aleksi Alkio) #11

Approach of @Suvrutt_Gurjar is a good one. When you concatenate the group name, you should create a format 2018-06-22. Otherwise the ascending and descending will give you wrong result. This is happening because the concatenate will convert the date as a text string.

(Suvrutt Gurjar) #12

Hi @Aleksi_Alkio,thank you for your guidance on the date format. As always, your input is vital for correct, efficient solution.