Creating SQL-Related Attributes for Activities and Lists

This guide provides step-by-step instructions on how to create SQL-related attributes for activities and lists. These attributes are designed to automate calculations or update specific columns based on SQL queries.

Table of Contents

SQL-Related Attributes for Activities
SQL-Related Attributes for Lists

SQL-Related Attributes for Activities

Steps to Follow:

Choose an Activity: Select the activity you want to add the SQL-related attribute to. For example, we will add the attribute in the retailer workflow activity.



Choose or Add an Attribute:
- Select a question type based on the expected query result.
- For options like category_selected, use a single choice.
- For plain text (e.g., token number, date), use text.
- For integers, choose an integer type.
- In this example, we will use plain text to add the customer token number.



Add Question Title and Tag: In the overview section, add a title and tag for your question.



Settings Tab:
- Go to the settings tab to add the SQL attribute.


- Before and after adding the SQL attribute, ensure the following options are set correctly:
a. Set 'Autofill Questions Answers' to 'Yes'.
b. Ensure 'Impossible to Change this Question' is set to 'Yes' for role-based access.




SQL Query:
- Add your SQL query in the provided format.

- Use meta expressions for preselecting values from a list. For example, to auto-select and associate the ID of a customer from the customer list and the token list, use a meta expression to add an expression that can retrieve the customer token number.



- select the _id from the customer list and click next


- Optionally, set a default value.



- Save changes for both the attribute and the activity.

Note:

Ensure that tags in queries with options match the single choice tags.

SQL-Related Attributes for Lists

Steps to Follow:

Access Lists Section: Go to the lists section in the side panel. You can click on summary to view all the lists.



Choose a List: Select the specific list you want to add the SQL-related attribute to. In this case, we will use the customer list.



Define the Attribute:
- Click on edit customer list and select the attribute you want to use by clicking on the add attribute button. For this example, we used an input text.


- Add the title and tag for your customer list. For example, last retail audit date.




SQL Query for Lists:
- To add an SQL attribute query, click on edit the attribute you have created.
- Click on the settings section, and you will see an option to add an SQL query. Consider various options before adding the query, such as hiding the attribute from the mobile app user, ensuring a unique value for this attribute, and locking the field to prevent non-admin users from changing the query.




- Use a query format that specifies the item to update and copy-paste it into the SQL section.

Note:

You can wait for the hourly maintenance to update values or force maintenance in the settings for immediate effect (takes about 15 minutes).


By following these steps, you can effectively create and manage SQL-related attributes for both activities and lists, enhancing the automation and accuracy of your data management processes.

Updated on: 13/11/2023