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
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?
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.
<<Start: ORDERBY(SELECT(Related MOSM Dock Report (Photo)s By Report Date],[category]=โPlaaplaaโ),[category],TRUE)>>
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
The Key Column was missingโฆ
<<Start: ORDERBY(SELECT([Related MOSM Dock Report (Photo)s By Report Date][KeyColumnName],[category]=โHullโ),[category],TRUE)>>
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
Remove the dot away as it was in the original
Super! I got it through! and template showing exactly what i would
like show.
Thank you for your help once again!
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
@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.
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])).
@Aleksi_Alkio I got it ! Thanks for your detailed explanation. I got new tips to Appsheet now.
User | Count |
---|---|
43 | |
32 | |
25 | |
23 | |
14 |