Workflow - QUERY and SORT syntax

🧩 Low-Code Development Workflows
#1

Hi,

Is there any documentation about syntax for “query” and “sort” fields?

If it doesn’t exists, can someone give a few examples? My current goal is retrieve (with record search on an iterator) all records in the last 24h.

Thanks!

#2

Hi there!

Thanks for reaching out.
Here is a link to the documentation for Corteza, this specific link leads directly to the syntax needed for date time functions.
https://docs.cortezaproject.org/corteza-docs/2021.3/integrator-guide/expr/fnc-reference.html#_date_time_functions

I would first presort records using “createdAt DESC”. This will sort the records from most recent to least, this will allow us to see which records we wish to retrieve.

In the Prefilter records section entering “DATE(createdAt) < DATE(NOW( ))” will only display records from dates before the current date.

While this is not a perfect solution it may be a work around for now.

#3

Hi,

Thank you so much for your reply.

Although I can use the “createdAt DESC” on a record list block (let’s say i’m creating a page with a record list):
image
(in this case, timestamp is a custom field)

I can’t use a similar expressions on Workflow, and that’s what I’m looking for!

image
image1366×805 115 KB

image
image1362×673 66.3 KB

I’ve also checked in the existing workflows for examples, but could find anything useful (yet!)…

#4

So, I think I figured the syntax (by looking at examples on the other workflows).
However it lead me to another issue, considering the following steps:

1 - Manual Start (for testing)
2 - Expressions: customerName (string) - "ACME"
3 - Iterator: query - "customer = " + customerName

ERROR: workflow 261151556135550979 step 7 execution failed: unknown field “ACME”

I’m trying to lookup records in which “customer” (custom field - string) equals to “ACME”, but the workflow seems to be looking for a custom field named “ACME”. How can I access the value in the “customer” field instead?

I’ve also tried “customer = ACME” and “customer == ACME” (or variations like “customer =” + “ACME”) with the same results

EDIT: In the meantime I tried to compare an Integer instead of a String and it worked as expected… Something like “Length(customer) >=” + var1 returned the correct records. It seems the problem is the way I’m comparing strings…

#5

I finally got it!!!

Regarding my latest example, here’s some mental notes in case someone else needs them:

1 - The SORT syntax is actually the same as used in a record list, but with quotation marks: “createdAt DESC”. (As on Stephan’s wrote. I missed them and it didn’t worked);
2- About the QUERY, if I need to get the VALUE of the field, the variable must be between apostrophes (’). Here’s the correct form in my example: "customer = ’ " + customerName + " ’ " (spaces between apostrophes and quotation marks added for readability)

#6

TL;DR of the conversation:

Here are the docs for writing query strings (not workflow specific)

Here are the docs for writing sort expressions (not workflow specific)

When defining query/sort strings in the workflow steps; if you wish to use a simple string (such as createdAt DESC) make sure to uncheck the “expression” checkbox under the text box (when the parameter is marked as an expression, the contents are treated as an expression and are evaluated).

If you need to define a complex query/sort string, make sure to properly format values you are comparing to, such as "customer = ’ " + customerName + " ’ " (notice the ' because you’re comparing to a string; when ' is omitted a field with that name is implied).
The syntax and logic closely represents SQL.