morning, all. i need to filter out child records in my body template that have a non-blank entry in “Finished Date”. how do i express filtering for child records in a template?
i presently have:
<<Start: ORDERBY(SELECT(STATE[LOCATION],TRUE),[LOCATION])>>
| Name | Surname | Job Title | Phone | |
|---|---|---|---|---|
| <<Start: ORDERBY(SELECT([Related STAFF LISTs],TRUE),[SURNAME])>><<[Name]>> | <<[Surname]>> | <<[Job Title]>> | <> | <><> |
<>
i want to filter out child fields “Finish Date”<>"
thank you for your help
Try…
<<Start: ORDERBY(SELECT([Related STAFF LISTs],ISNOTBLANK([Finish Date])),[SURNAME])>><<[Name]>>
Aleksi:
[Related STAFF LISTs]
thanks, Alexi. but [Finish Date] is a column in the child table. the above errs "Error: Workflow rule ‘PREPARE STAFF LIST’ action ‘EMAIL STAFF LIST’ Attachment template. Expression ‘ORDERBY(SELECT([Related STAFF LISTs],ISBLANK([Finish Date])),[SURNAME])’ is invalid due to: Unable to find column ‘Finish Date’. "
What column in the parent table is a Ref to the child table that contains the Finished Date column?
hi, Steve.
Parent table is STATE and ref column is LOCATION
child table is STAFF LIST and ref column to parent is also called LOCATION
What table has the Finished Date column?
hi, Steve.
the table STAFF LIST, child of STATE, has [Finish Date]
@Aleksi @Steve thanks for your help so far, guys.
i’m constantly thrown off course by how message exchanges hit this community and / or my gmail address. i think i should stick to the community blogg and ignore gmail messages.
i’ve enabled definition access to aleksi.alkio@appsheet.intercom-mail.com (no response). how do i enable for Steve?
today i hit another snag:
<<Start: ORDERBY(SELECT(BIRTHDAYS AND ANNIVERSARIES HQ[LASTFIRST], TRUE),[SURNAME])><<[Name]>>
errs saying:
“Error: Report ‘BIRTHDAYS AND ANNIVERSARIES HQ’ action ‘BIRTHDAYS AND ANNIVERSARIES HQ’ Body template. Expression ‘ORDERBY(SELECT(BIRTHDAYS AND ANNIVERSARIES HQ[LASTFIRST], TRUE),[SURNAME])><<[Name]’ is invalid due to: Unable to find column ‘Name’. Error: Report ‘BIRTHDAYS AND ANNIVERSARIES HQ’ action ‘BIRTHDAYS AND ANNIVERSARIES HQ’ Body template. Start expression ‘ORDERBY(SELECT(BIRTHDAYS AND ANNIVERSARIES HQ[LASTFIRST], TRUE),[SURNAME])><<[Name]’ should generate a List of Ref values. Please verify that it generates a List and that the contents of the List are Ref values. Ref values should come from the ‘Key’ column of the referenced table.”,
similar to previous request for help. and this is by the “create” option.
help, please?
It sounds that your [LastFirst] column is not a key column. With the Start: expression you always need to use Key column.
hi, @Aleksi.
“BIRTHDAYS AND ANNIVERSARIES HQ” was a slice with All Columns
of “STAFF LIST” where [LASTFIRST] is key
to make this one work, i had to make the slice Custom and include the key [LASTFIRST]
thanks for your time.