Sort and filter the child tables on workflow ...

[Koichi_Tsuji]

Sort and filter the child tables on workflow template.

I have two tables, parent and child with strong relations (ispartof : true).

To create the workflow to parent table, when the column on parent is changed, the action is triggered to send email.

On this template, the child table listed in table form on google doc with start expression like this.

<<Start: [Related MOSM Dock Report (Photo)s By Report Date]>><<[Photo]>>

I wonder how to

1. Orderby :

I have column with name of “Category” on the child table. How can we alter the start expression to sort the list by “Category” either asc, desc?

2. Filter :

Also similar thing, but i wish to filter this table on template by “category” column, to show the specific items only with the selected “category”.

Thank for your attention, but your help is appreciated to get the right syntax to achieve category, filter the child table for template.

[Aleksi_Alkio]

<<Start: ORDERBY(SELECT(Related MOSM Dock Report (Photo)s By Report Date],[category]=“Plaaplaa”),[category],TRUE)>>

[Koichi_Tsuji]

Hi thank you. I tried your expression, but unfortunately, I ended up with error.

To see how oderby/filter work together,

i break down. First step, to make “orderby” to sort the row. With this syntax it worked.

<<Start: ORDERBY([Related MOSM Dock Report (Photo)s By Report Date],[Category],TRUE)>><<[Photo]>>

I assumed “filter expression” in addition to above syntax may solve the problems, so I made syntax like this.

<<Start: FILTER(ORDERBY([Related MOSM Dock Report (Photo)s By Report Date],[Category],TRUE),[Category]=”Hull”,True)>>

This combination of filter + orderby did not work … ended up with error.

–

I also assumed your syntax might be missing ‘’ [ " before the name of column for select function, tried :

<<Start: ORDERBY(SELECT([Related MOSM Dock Report (Photo)s By Report Date],[category]=“Hull”),[category],TRUE)>>

but also did not work neither.

Wondering how to solve the problems…@Aleksi_Alkio

[Aleksi_Alkio]

The Key Column was missing…

<<Start: ORDERBY(SELECT([Related MOSM Dock Report (Photo)s By Report Date][KeyColumnName],[category]=“Hull”),[category],TRUE)>>

[Koichi_Tsuji]

Still encountering error messages…

Pulling out the “select expression” part.

SELECT([Related MOSM Dock Report (Photo)s By Report Date][KeyColumnName],[category]=“Hull”)

I added dot in between [ ] [ ], and added key from child table.

SELECT([Related MOSM Dock Report (Photo)s By Report Date].[Photo Unique ID],[category]=“Hull”)

I m wondering something wrong on Select function.

This is error message copied from Log.

qte

‘ORDERBY(SELECT([Related MOSM Dock Report (Photo)s By Report Date].[Photo Unique ID],[category]=“Hull”),[category],TRUE)’ is invalid due to: Error in expression [Related MOSM Dock Report (Photo)s By Report Date].[Photo Unique ID] :

unqte

[Aleksi_Alkio]

Remove the dot away as it was in the original

[Koichi_Tsuji]

Super! I got it through! and template showing exactly what i would

like show.

Thank you for your help once again!

[Aleksi_Alkio]

You may be interesting in reading Praveen’s post…

plus.google.com - Filtering of ref lists – expressed (and run) more efficiently Quite often, … Filtering of ref lists – expressed (and run) more efficiently Quite often, … plus.google.com

[Koichi_Tsuji]

@Aleksi_Alkio Thank you very much for sharing this valuable post. If i m correct in reading this, those expression can be used both on template and app formula as well?

On my case for this post, i used expression on workflow template.

My tables are linked together by virtual column systematically generated on parent table.

For this particular virtual columns we always have something like

REF_ROWS(“MOSM Dock Report (Photo)”, “Report Date”)

to pull “whole rows” under the child table.

To restrict the row appearing with filter by select function, i would be doable by putting syntax on App formula by replacing default syntax?

Or should be generate the new virtual column then kick the expression into app formula?

Just wondering how we can expand the use case for those expression, i.e. only for workflow template or other cases.

[Aleksi_Alkio]

Because the virtual list is already a filtered list, it’s more effective to read values from that list instead of reading the whole table again.

One user case could be if you need to sum another field from those child records. Then the app formula would be like SUM(SELECT([VirtualListName][PriceColumn],TRUE)) in the virtual column. Normally you would need to write the formula as SUM(SELECT(TableName[PriceColumn],[ID]=[_THISROW].[ID])).

[Koichi_Tsuji]

@Aleksi_Alkio I got it ! Thanks for your detailed explanation. I got new tips to Appsheet now.