Grouping Records in Start Expressions without a slice - possible?

AppSheet AppSheet Q&A
,
1

In a slice:
[SubItemID]=MINROW(“SubItems”,“_RowNumber”,AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW].[DueDate]))

Returns a record for each unique due date. I can use this in automations to group records.

I’d like to be able to do this without using a slice.

In an email template:
<<LIST(MINROW(“SubItems”,“_RowNumber”,AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW-0].[DueDate])))>>

Returns a single record.

[_THISROW].[DueDate] returns no records.

Is there a way for me to create the same type of dataset as I do with the slice without using a slice?

Why? - Because I want to. Because there are times that adding slices for each type of grouping I want to accomplish is cumbersome. With the glut of objects (slices, views, actions, bots, format rules, etc.) needed to create a reasonably robust tracking system, I’d like to avoid creating another object for everything I do.

Thanks.

2

Like this:

<<Start: FILTER("Subitems", slice-row-filter-expression)>>

So:

<<Start: FILTER("SubItems", [SubItemID]=MINROW(“SubItems”,“_RowNumber”,AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW].[DueDate])))>>
2 Likes
3

Thanks Steve,

Your formula did not return what I needed… in fact, it did not return any rows. But it got me thinking.

I created a template with the following:

RETURNED LIST:  THISROW ONLY <<START: FILTER("SubItems",[SubItemID]=MINROW("SubItems","_RowNumber",AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW].[DueDate])))>> ITEMS FOR DUE DATE: <<[DueDate]>> <<END>>  THISROW-0 <<START: FILTER("SubItems",[SubItemID]=MINROW("SubItems","_RowNumber",AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW-0].[DueDate])))>> ITEMS FOR DUE DATE: <<[DueDate]>> <<END>>  THISROW-1 <<START: FILTER("SubItems",[SubItemID]=MINROW("SubItems","_RowNumber",AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW-1].[DueDate])))>> ITEMS FOR DUE DATE: <<[DueDate]>> <<END>>

The resulting email was:

| RETURNED LIST:

THISROW ONLY

THISROW-0

ITEMS FOR DUE DATE: 8/2/2025

THISROW-1

ITEMS FOR DUE DATE: 8/2/2025

ITEMS FOR DUE DATE: 8/3/2025

ITEMS FOR DUE DATE: 8/4/2025
|
|----|

Powered by AppSheet

Which I think I understand.. sort of.

The [_THISROW-1] in the MINROW sub-data call refers to the prior FILTER(“SubItems”…

[_THISROW-0] refers to the MINROW data call. I am unsure what [_THISROW] is referring to as it returns no data.

Finally, to display all the records that had a given due date, I used the following:

RETURNED LIST:  THISROW-1 <<START: FILTER("SubItems",[SubItemID]=MINROW("SubItems","_RowNumber",AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW-1].[DueDate])))>> ITEMS FOR DUE DATE: <<[DueDate]>> <<START: FILTER("SubItems",AND(ISNOTBLANK([DueDate]),[DueDate]=[_THISROW-1].[DueDate]))>> <<[SubItemID]>>  |  <<[DueDate]>> <<END>><<END>>

In the nested FILTER above, [_THISROW-1] refers to the data in the MINROW call in the prior start expression.

And with that, I can accomplish what I need.

Finally, can we please get actual SQL statements?
… a person can dream, can’t they?

1 Like