Start Expressions and Grouping

[matthewmoran]

This should be simple but I'm relatively new to Appsheet. I have more experience with SQL, BigQuery, and Google Apps Script.

I have a single table, NDARequests, with the following fields:
-RequestID
-RequestDate
-LastModified
-RequestName
-Status
-AssignedTo

I'm creating an email and want it grouped by Status. The status's are prefaced with a 3 digit number and then the status phrase:
"110 - New Request"
"120 - Counsel Review"
"130 - Client Review"
"150 - Internal Review"
... and so on

Have a Google Docs template:

I am, and will, do more with the ordering but the below is working.

<<Start: ORDERBY(SELECT(NDARequests[RequestID], Number(Mid([Status],1,3))<200, TRUE),[Status])>>
:: any additional fields in a table row
<<end>>

What I'm looking to do is more like this:

OPEN NDA REQUESTS

<<STATUS>>
- Rows of data for the above status

<<STATUS>>
- Rows of data for the above status

<<STATUS>>
- Rows of data for the above status

How do I structure a nested start expression to achieve that? 

It is simple with a joined/referenced table. 

Thanks.

[Hussein_Osseily]

I have the same logic somehow , instead of status it is truck ref . 

i display it as you have explained. 

Truck Reference: 

shipment number 

Pcs 

weight 

shipment number 

Pcs 

weight 

Truck Reference: 

Shipment number 

pcs 

weight 

…

if this is what you want you can do so : 

in your table NDARequests create a Virtual Column of type List name it group : 

use the following expression:

SELECT(NdaRequests[REQUESTID],[STATUS]=[_THISROW].[STATUS])

then in your template you can hive this expression: 

<<Start: SELECT(NDARequests[REQUESTID],AND([RequestID]=INDEX([GROUP],1),[STATUS]=[_THISROW].[STATUS]))>>

<<[STATUS]>>

<<Start: [GROUP]>>

<<[RequestID]>>
<<[RequestDate]>>
<<[LastModified]>>
<<[RequestName]>>
<<[AssignedTo]>>

<<END>>
<<END>>

this how i achieved what you want you can tweak it to tour needs . 
i wrote this from my phone but should work for your example, apologies for any typos.

[matthewmoran]

When I use that select I don't get any rows returned. I see what group is doing.

I modified the template what I have below. It correctly returns statuses that are less than 130 (or whatever number I enter) but the <<[Status]>> and all rows for that status are repeated for each row.

This makes sense to me, in that I am returning all rows with a status of <130. And for each row I'm then displaying the returned list of [Group] - which are all the records for a given status.

So, for three records with a 110, I get the bold 110 New Request and the two records in the table that are that status. Then I get them a 2nd time. For the three 120 records I get the bold status name and the 2 records. Then I get it two more times.

So, what I want is one record for any given status and then to reference the [Group] list to display the complete list of records. I had to redact data but an image of the result I'm getting is included below.

The template

<<Start: ORDERBY(SELECT(NDARequests[REQUESTID],Number(Mid([Status],1,3))<130),[Status])>>

<<[Status]>>

<<Start: [GROUP]>>

<<[RequestName]>>

<<[DateReceived]>>

<<[Assignee]>>

<<[BusinessLead]>>

<<End>>
<<End>>

[Hussein_Osseily]

Indeed the intended expression had to display the status once only. 

the first select expression in the virtual column is grouping the IDs which should be the key of the table, assuming RequestID is the key of the table. 

if the Vn column is grouping the IDs of the key correctly, then in your template, try to place the first <<[END]>> in the last column of the table, beside <<[BusinessLead]>> <<[END]>> 

<<[END]>>

[Hussein_Osseily]

Can you try this as well: 

in virtual column: 

ORDERBY(SELECT(NDARequests[REQUESTID],AND(Number(LEFT([Status],3))<130),[STATUS]=[_THISROW].[STATUS],[Status])

this should select the numbers less than 130 and the status=Status if this row

in the template use this: 

<<Start: SELECT(NDARequests[REQUESTID],AND([RequestID]=INDEX([GROUP],1),[STATUS]=[_THISROW].[STATUS],Number(LEFT([Status],3))<130)))>>

<<[Status]>> 

<<Start: [GROUP]>>

<<[RequestID]>>
<<[RequestDate]>>
<<[LastModified]>>
<<[RequestName]>>
<<[AssignedTo]>><<END>>

<<END>>

[Hussein_Osseily]

Hello @matthewmoran 

If you haven't figured it out yet, here's a working solution, yesterday i was typing from phone without having the expressions tested , was 03h00 in the morning my side

Virtual Column i used this, you can tweak the names to match your tables, make sure you select the key column in the expression, here, in this example, RequestID is the key: 

ORDERBY(
  SELECT(
    Requests[REQUESTID],
    AND(
      NUMBER(LEFT([Status], 3)) < 130,
      [STATUS] = [_THISROW].[STATUS]
    )
  ),
  [Status]
)

In your template use the following: 

<<Start: SELECT(Requests[REQUESTID],AND([RequestID]=INDEX([GROUP],1),Number(LEFT([Status],3))<130))>>

<<[STATUS]>>

<<Start: [GROUP]>>
<<[RequestID]>>
<<[RequestDate]>>
<<[LastModified]>>
<<[RequestName]>>
<<[AssignedTo]>><<END>>




<<END>>

your template should look like this

The Result

[Hussein_Osseily]

Use this instead in your template if you wish to order by [status] asc:

<<Start: ORDERBY(SELECT(Requests[REQUESTID],AND([RequestID]=INDEX([GROUP],1),Number(LEFT([Status],3))<130)),[STATUS])>>

[AleksiAlkio]

You should be able to do this nested grouping without any virtual column. It's not a good idea to have an additional virtual column only for the template purpose. I have done this with sample apps.. I just need to find the app and what was the simplest solution.

[matthewmoran · Accepted solution]

This is what I used to get it working... basically, taking clues here and with a few other posts I found.

<<Start:ORDERBY(FILTER("NDARequests",[RequestID]=MAXROW("NDARequests","_RowNumber",AND([STATUS]=[_THISROW-1].[STATUS],Number(Mid([Status],1,3))<200 ))),[Status])>>

<<[Status]>>
<<Start:FILTER( "NDARequests", [STATUS]=[_THISROW-1].[STATUS])>>
<<[RequestName]>>
<<[DateReceived]>>
<<[Assignee]>>
<<[BusinessLead]>>
<<End>>
<<End>>

[AleksiAlkio]

This is what I meant.. 

[matthewmoran]

Similar to what I arrived at. Minrow/maxrow, return a single value for the label. Then, nested inside, filter with [_THISROW-1].[STATUS].