KiSSFLOW supports commonly used spreadsheet formulas to perform business logic in the app, such as the LEN, DATE, IF, and AVERAGE functions can be used.

All of these formulas are very helpful and easy to use if the results are based on the fields available in the parent form.

However, different functions are needed to perform an aggregate function based on the fields in a table in the form. For example, identifying the total sum of the sub cost of each line item in a Purchase Request app. Or pre-populating a value from a master or another process without using a Lookup field. Or pre-populating the Employee Department & Employee DOJ from the Employee master while initiating an app.

To do these, you must use the ‘D’ Functions in KiSSFLOW.

This function is used to calculate the totals of the field from a table in a form.

<tableName> is the name of the table.

<field_name> is the name of the field Name in the table.

<criteria> is an optional argument. The criteria can be used to sum a specific set of item cost. For example, to sum all the item cost of the category IT. The syntax is <tableName>.<field_name> = <value or field_name>

In the following example, the field Estimated PR Value uses the formula: 

This function is used to count the number of items based on a number field, in a table.

In the above example, if you were to use this formula:

The result would be 3.

If you were to use this formula:

The result would be 2.

This function is used to calculate the count the number of items based on a text field, in a table. 

In the above example, the formula:

Results in 3.

The formula:

Results in 2.

This function is used to determine the minimum value of item in a table. 

Using this same example above, the formula,

results in 150.

The formula

also results in 150.

This function is used to determine the maximum value of item in a table. 

The syntax is:

DMAX(<tableName>.<field_name>,<criteria>)

The formula,

DMAX(PR_Line_Items.Unit_Price)

results in 450.

This function is used to lookup and fetch a field value from another KiSSFLOW process or Master. For example, to get the value of “Item Rate” field from “Item Master” you can use this function.

The syntax is:

DLOOKUP(<lookup_process/master_name>.<fetching field>, <criteria>, <orderby cell>, <order>)

In this formula, the process/master name, fetching field, and criteria are all required.

<lookup_Process / master_name> is the name of the process or master.

<fetching_field> is the name of the field in the process or master. 

<criteria> is the condition to fetch the data. You can use the AND, OR or IF conditions here. The syntax for the criteria is:

<Master/Process.Field Name> <Condition> <Field in the Current Process>. Eg. “Item_Master.Description = Item_Name”

<orderby cell> (Optional) – The field on which the data will be sorted . If not mentioned then the sorting will be done on the <fetching_field>

<order> (Optional) – The value for this argument could be “asc” or “desc”. This determines the sorting order. By default it is “desc”. Also you can write a formula that returns either “asc” or “desc” to make it more dynamic

Example:

To lookup the price, the formula looks like this:

DLOOKUP(Item_Master.Price,Item_Master.Description = Item_Name)

In the above example the “Item_Name” is a field in the current process and the value will be passed from the current form.

Note:

If you need to fetch the value from the same app you are currently working in, then just replace the table name with “Self”. That is, if you want to calculate the total PR Value in the purchase request , then the formula will be:

DSUM(self.PR_value)

This function is used to concatenate a text field in a table.

The syntax is: 

DCONCATENATE(<tableName>.<field_name>,<criteria>)

For example, using the above image, the formula:

DCONCATENATE(PR_Line_Items.Item_Description)

results in: KeyboardMouseWebcam

The formula:

DCONCATENATE(PR_Line_Items.Item_Description,PR_Line_Items.Quantity>=10)

results in: KeyboardMouse