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.

  1. On the list, you first select the attribute type


  2. If it is a single or multiple-choice attribute, you need to import the label/tag for the different choices






  3. 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$'
CODE

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.