You can define an attribute in a list based on a SQL query. It is very useful as you can use the data from the workflow to define attributes in the lists.
For example, you can define customer status based on workflow activity.
On the list, you first select the attribute type
If it is a single or multiple-choice attribute, you need to import the label/tag for the different choices
Write your query
You have a SQL query section in which you can write your SQL query.
The query should return 1 dimension only.
The only thing that changes compared to the usual queries is the WHERE condition. You need to state in your WHERE condition to which item you are calculating the query.
You need to write : WHERE <item_id> = '$item_id$'
For example, your query is based on the list, you will write : WHERE _id='$item_id$'
For a single choice attribute, the query needs to return the tag of each option/
For a multiple choice attribute it returns one result y=opt1 if only one choice is selected, y=opt1,opt2 if several choices are selected.
In our example, it could be the following query depending on the number of submissions made on this workflow.
With the following information:
workflow id = qisdugusqhdioqs
id corresponding to the id in the workflow = item_selected__id
SELECT CASE WHEN COUNT(_id) > 0 THEN 'option1' ELSE 'option2'END FROM w_qisdugusqhdioqs WHERE item_selected__id = '$item_id$'
You can’t have null values in a calculated attribute query for a single/multiple choice attribute. You need to configure a specific option “None” with a tag but the query can not return NULL.