Using Formulas in Different Features
Introduction
When formulas are used in Manage Data > Batch Edit, Data Factory > Field Settings > Add Calculated Field, and Dashboard > Add Calculated Field, the settings are different from those in Design > Field Properties > Initial Value in a form. You can read the following article for details.
Batch Edit Through Formula
In Batch Edit of Manage Data and permission sets, you can set a field to Formula and edit through Formula. For example:
Functions Not Supported
The Batch Edit feature does not support the following functions:
- Math functions: COUNTIF(), FIXED(), LARGE(), RAND(), ROUND(), SMALL(), and SUMPRODUCT().
- Text functions: CHAR(), IP(), ISEMPTY(), REPT(), RMBCAP(), TEXT(), TRIM(), and VALUE().
- Date functions: DATE(), DATEDELTA(), DAYS360(), ISOWEEKNUM(), NETWORKDAYS(), SYSTIME(), WEEKNUM(), and WORKDAY().
- Advanced functions: GETUSERNAME(), MAPX(), RECNO(), EXTLOCATION(), TEXTUSER()T, EXTDEPT(), and UUID().
Other Rules
1. Only fields in main forms are supported to batch edit through Formula. Supported field types are Single Line, Serial No., Multi Line, Number, Date&Time, Radio, and Single Select.
2. Parameters in Formula can only be fields in main forms. Supported parameter types are Single Line, Serial No., Multi Line, Number, Date&Time, Radio, and Single Select.
3. The SPLIT function can be used. But you cannot simply use single result after the SPLIT function split the text. Instead, you can only use the COUNT function to calculate the number of groups after the text is split.
Formulas in Data Factory
In Data Factory > Field Settings, you can add calculated fields to edit formulas.
Functions Not Supported
- Math functions: COUNT(), COUNTIF(), LARGE(), SMALL(), SUMPRODUCT().
- Text functions: CHAR(), RMBCAP(), SPLIT(), UNION().
- Date functions: DAYS360(), ISOWEEKNUM(), TIMESTAMP().
- Advanced functions: GETUSERNAME(), MAPX(), RECNO(), EXTLOCATION(), TEXTUSER()T, EXTDEPT(), UUID().
Other Rules
1. You can only add text type fields and number type fields, and you cannot add date type fields.
2. In the Formula of Field Settings node, supported parameter types are Single Line, Serial No., Number, Date&Time, Radio, and Single Select.
Formulas in Dashboard
The dashboard is equipped with the Add Calculated Field feature. This is supported in the following charts: Stats and Detail Table.
Supported Functions
- Stats: support addition (+), subtraction (-), multiplication (*), division (/), and the following functions: SUM(), AVERAGE(), COUNT(), MAX(), and MIN().
- Detail Table: support addition (+), subtraction (-), multiplication (*), and division (/).
Other Rules
For the calculated fields in Stats and Detail Table, the supported parameter types are:
- Stats: Single Line, Multi Line, Number, Date&Time, Radio, Single Select, Serial No., Member, Department, and these fields are also supported in SubForm.
- Detail Table: Number in main forms.
Common Formulas for Converting Data
You can set formulas to convert data in different features:
Converting Numbers to Text
Where to Set | How to Set |
Field Properties | TEXT(Number) or just select the Number field. |
Batch Edit | When setting a Single Line field to Formula, you just need to select the Number field. |
Field Settings | TEXT(Number) |
Converting Date to Text (Year/Month/Day)
Where to Set | How to Set |
Field Properties | TEXT(DATE(Date&Time),"yyyyMMdd") |
Batch Edit | 1. Add another three Single Line fields, and set Formulas to query the day, month, and year respectively: Set the Year field to the Formula as YEAR(Date&Time); Set the Month field to the Formula as MONTH(Date&Time); Set the Day field to the Formula as DAY(Date&Time). 2. Convert the date to text by setting the Formula as follows: CONCATENATE(Year,RIGHT(CONCATENATE("00",Month),2),RIGHT(CONCATENATE("00",Day),2)) 3. After the settings, delete the Year, Month, and Day fields. |
Field Settings | To convert the month and year to text, set the Formula as (DATE(Date&Time),"yyyyMM"). To convert the date to text, set the Formula as (DATE(Date&Time),"yyyyMMdd"). To convert time to text with exact seconds, set the Formula as TEXT(DATE(Date&Time)). To convert the month and year to numbers, set the Formula as follows: YEAR(Date&Time)*100+MONTH(Date&Time) To convert the date to numbers, set the Formula as follows: YEAR(Date&Time)*10000+MONTH(Date&Time)*100+DAY(Date&Time) To convert time to numbers with exact seconds, set the Formula as follows: YEAR(Date&Time)*10000000000+MONTH(Date&Time)*100000000+DAY(Date&Time)*1000000+HOUR(Date&Time)*10000+MINUTE(Date&Time)*100+SECOND(Date&Time) |
Querying the Day of the Week from the Date
Where to Set | How to Set |
Field Properties | TEXT(DATE(Date&Time),“EEE”) |
Batch Edit | 1. In Design, apart from the original Time field, you need to add another Time 2 field. 2. Set the Time 2 field to Fixed Value, such as 2023-05-15 (Monday). 3. Set the Day of the Week (a Single Line field) to the Formula as follows: IFS(MOD(DAYS(Time,Time 2),7)==0,"Monday",MOD(DAYS(Time,Time 2),7)==1,"Tuesday",MOD(DAYS(Time,Time 2),7)==2,"Wednesday",MOD(DAYS(Time, Time 2),7)==3,"Thursday",MOD(DAYS(Time,Time 2),7)==4,"Friday",MOD(DAYS(Time,Time 2),7)==5,"Saturday",MOD(DAYS(Time,Time 2),7)==6,"Sunday") |
Field Settings | IFS(MOD(DAYS(Time,DATE(2023,5,15)),7)==0,"Monday",MOD(DAYS(Time,DATE(2023,5,15)),7)==1,"Tuesday",MOD(DAYS(Time,DATE(2023,5,15)),7)==2,"Wednesday",MOD(DAYS(Time,DATE(2023,5,15)),7)==3,"Thursday",MOD(DAYS(Time,DATE(2023,5,15)),7)==4,"Friday",MOD(DAYS(Time,DATE(2023,5,15)),7)==5,"Saturday",MOD(DAYS(Time,DATE(2023,5,15)),7)==6,"Sunday") |
Converting Member to Text
Where to Set | How to Set |
Field Properties | TEXTUSER(Member,"name") |
Batch Edit | In Manage Data > Export, you can export the data of the Member to the name column of the text. |
Field Settings | Not yet available. |
Converting Department to Text
Where to Set | How to Set |
Field Properties | TEXTDEPT(Department,“name”) |
Batch Edit | In Manage Data > Export, you can export the data of Department to the department column of the text. |
Field Settings | Not yet available. |
Setting the Format of Number Fields
For a Number field, in Design > Format, you can set the Number of Decimal Places or convert it to a Percentage.
Where to Set | How to Set |
Field Properties | ROUND(Number,2) or TEXT(Number,"0.00%") |
Batch Edit | Set in Design > Field Properties > Format directly. |
Field Settings | Not yet available in Data Factory. But you can export data to Dashboard. In the dashboard, select a Number field and select Data Display Format, then you can set the Number as Numeric, Percentage, or Custom. |
Judging Empty Fields
Set calculations according to whether the field is empty or not.
Where to Set | How to Set |
Field Properties | IF(ISEMPTY(Product Name)==0,Storage Quantity-Sales Quantity,0) |
Batch Edit | 1. Filter out the data whose Product Name is not empty, and batch edit the Quantity field through Formula as Storage Quantity-Sales Quantity. 2. Filter out the data whose Product Name is empty, and batch edit the Quantity field to Fixed Value as 0. |
Field Settings | To see whether the text is empty, set the Formula as follows: IF(ISEMPTY(Single Line)==0,value_if_true,value_if_false) To see whether the date is empty, set the Formula as follows: IF(ISEMPTY(TEXT(DATE(Date&Time)))==0,value_if_true,value_if_false) To see whether the number is empty: It cannot be directly realized through Field Settings. You need to: 1. Add two Data Filter nodes, one to filter the numbers that are empty and the other for the numbers that are not empty. 2. Connect the two nodes to Field Settings, then calculate the corresponding return value. 3. Add a Group by node and then connect the two Field Settings nodes to it. |