Advanced Functions

GETUSERNAME

GETUSERNAME()

The GETUSERNAME function can be used to obtain nicknames of current users.

For example, you can limit each member to fill only one record of data every day according to the uniqueness of nicknames.

INDEX

INDEX(array, position)

Retrieves a value from an array.

The INDEX function has the following arguments:

For example, if you use the formula INDEX(Checkbox,1), it will return the first positive value in the Checkbox field. If you use the formula INDEX(Checkbox,-1), it will return the last value in the Checkbox field.

MAPX

MAPX(operation,map_value,map_field,result_field)

The MAPX function can be used to calculate cross-form data. Usually, functions can only calculate data in current forms. But the MAPX function can calculate data that have been submitted or data in other forms.

The MAPX function has the following arguments:

Returns all values of result_field when the value of map_field in designated form is map_value and obtains corresponding return values according to designated aggregation operation operation.

It can be understood as such: find the return value that corresponds to the retrieval value in retrieval range and make an aggregation operation on the return value. For example:

MAPX("aggregation operation",retrieval value,retrieval range,return value)

Note:

If the retrieval value comes from Fields, it is colored blue. If the retrieval range and return value come from the same form in all form fields, they are colored green.

Here is a demonstration:

Argument Limits:

1. Aggregation operation (operation).

operation (aggregation operation) is a string. You can select values as follows:

2. Retrieval value (map_value) and retrieval range (map_field).

Fields that are supported to set retrieval value and fields that correspond to support to set retrieval range are shown as follows:

Note:

When the type of fields is Date&Time, you need to set retrieval value as DATE. You can directly use Date&Time fields as retrieval range. For example, set the Formula as follows:

MAPX("COUNT",DATE(Date&Time),Date&Time,Single Line)

3. Return value (result_field).

Taking A table as an example to set the MAPX formula and extract data from B table: MAPX("operation",field 1,field 2,"return value"), different return value fields can be set according to different aggregation operations:

Note:

1. Fields in retrieval range and return value do not include SubForm field.

2. Retrieval value cannot be Member/Members field. If you need to calculate data in Member/Members field, you can transform Member/Members fields into text fields and calculate data in text fields. The settings of retrieval range fields are same with that of retrieval value fields.

Examples:

There are two cross-form conditions:

1. In one form, calls data that have been submitted when entering data.

2. In two forms, calls data in B form that have been entered when entering data in A form.

Usage Example: Single Condition

You need to calculate the serial number of yourself when selecting courses in a course form. That is to say, you need to calculate the number of applicants in one of the courses and then plus 1.

The retrieval value in the example is the course field. The number of applicants will be calculated according to the courses that have been selected. Set the Formula as follows:

MAPX("COUNT",Course,Course,Name)

The Formula for "The serial number of myself" field is:

MAPX("COUNT",Course,Course,Name)

It can be understood as such: In data entered before, you can select Name fields for the Course field that has selected Fitness and calculate the number of names with the COUNT function. Finally, ass 1 to get the final result.

Note:

When setting the Formula as MAPX("COUNT",Course,Course,Name), if the return value is 0, the field will be displayed as Null Value.

Usage Example: Multiple Conditions

When there are multiple conditions corresponding to the value to be counted in a form, you can use the MAPX function to retrieve data across forms. For example, if you want to count the number of students enrolled in the same class of the same course, the criteria are the students' class and course. Then you can use the MAPX function to retrieve the data needed.

RECNO

RECNO()

RECNO function can be used to count the times that the current form has been viewed. The original value is 1.

For example, you can count the times that the current form has been viewed by combining this function with CONCATENATE function (used to add fixed sentences before or behind another function). You can set the Formula as follows:

CONCATENATE("Congratulations! You are the ",RECNO(),"one to view this form.")

TEXTDEPT

TEXTDEPT function can be used to obtain the name and serial number of departments.

1. Obtain the name of a department:

TEXTDEPT(dept_field, "name")

2. Obtain the serial number of a department:

TEXTDEPT(dept_field, "deptno")

For example, you can obtain the serial number of a department in Contacts through the TEXTDEPT function. You can also check the duplicate values of department serial numbers and make linkage with other data. You can set the Formula as follows:

TEXTDEPT(dept_field, "deptno")

TEXTUSER

TEXTUSER function can be used to obtain the nickname and serial number of members.

1. Obtain the nickname of members:

TEXTUSER(member_field, "name")

1. Obtain the serial number of members:

TEXTUSER(member_field, "username")

For example, you can obtain the serial number of members in Contacts. You can also check the duplicate values of members' serial numbers and make linkage with other data. You can set the Formula as follows:

TEXTUSER(member_field, "username")

UUID

UUID()

UUID function can be used to generate random codes. The default outputting format is 32 strings + four "-". So there are 36 strings totally.

For example, you can generate a random serial number through the UUID function. If you need to extract some strings in 36 strings, you can combine LEFT function with this function:

LEFT(UUID(),15)