A Table is a specific field type that can be used both in apps and in business processes. By adding it to a form, you will be able to organize data in rows and columns and insert formulas to make calculations.
For instance, add a table to the Orders app form to display all the goods selected by a customer. Insert a formula to one of the table cells to automatically calculate the total amount depending on the number of the purchased goods.
Add a table to the app form
You can add a table field when creating a new app or editing an existing one.
начало внимание
Only users in the Administrators group can add and set up tables.
конец внимание
Drag the Table property form a side panel to a form and configure the settings:
- Name*. Enter a name that employees will see on the create, view, and edit forms of an app item.
- Code*. Field code which is used when working with API, scripts, templates.
- Tip. Here you can enter additional information, for example, a description or a short instruction. This helps employees fill out a table correctly.
- View. Select the table view:
- Table. Standard table view.
- Template. You might want to use this setting when working with large tables. It is not always necessary to show all the table rows. For instance, sometimes you need to display only the total amount while keeping the other rows hidden. In this case, use a template.
In the Template field, enter a text that users will see on the form, for example, Order total.
Click on the {+} icon to add the total value of the corresponding column. This option is available if the table has already been created and configured. Otherwise, you can add only the number of table rows.
- Use one header for all nested tables. You can create a table inside another table. By default, each row of a nested table will have a header. If you want the header to appear only under the parent column header, enable this option.
- Table Settings. Click this link to go to the table display settings. You can read more about them in Table settings.
Add a column to the table
In a table, you can add columns and specify what type of data they will contain, create auto-complete formulas.
To add a new column, in the table settings window click the + icon.
Select column type:
- Data. Each column can contain data of only one type. For example, only numbers, email addresses, or app items. You can choose any of the standard ELMA365 data types. For a detailed description of each type see System data types.
- Formula. This option is available only for such data types as String, Money or Number. It allows you to specify a formula for calculating the value in each row of a column. These rows cannot be filled out manually. A formula may include constants, operators, links.
A constant is a fixed value; it is not calculated, but always stays the same. It can be a number or a text. String constants should be written in double quotation marks.
Operators are used to perform arithmetic operations and return numerical values. You can add, subtract, multiply, and divide numbers using the following operators: +, -, *, and /. Parentheses () are used to set the priority of mathematical operations.
Links allow making reference to other table columns or to the app’s fields.
Data type column
To create a column, enter the following information:
- Display Name*. The name of the column that will be displayed on the create, view, and edit forms of an app item.
- Property Name*. The name of the property used when working with the API, scripts, and templates. The name is generated automatically, but you can change it if necessary.
- Tip. An explanatory text or an additional description that will be displayed as a tooltip when you hover over the column heading.
- Type*. The data type used in this column. For example, by selecting the String type you will be able to fill the column with any text information.
начало внимание
If you select String, Money or Number data types, you will be able to calculate the column total. For the Money and Number data types, you can calculate the sum, maximum, minimum, or average. For the String type, you will be able to add a caption.
конец внимание
Formula type column
To understand formulas better, consider the following example.
An employee enters order details in a table on the Orders app form. He or she specifies the name of the product, its quantity, and the price per unit. The total price for each table item can be calculated automatically via a formula, by multiplying the unit price and the quantity of the product.
Create a column of the Formula type where the quantity will be automatically multiplied by the price.
To do so, click the + icon and select Formula.
In the provided window, configure the Formula settings. First, create a name, for example, Total cost.
In the Formula field, click the {+} icon. In the drop-down list, you will see the names of other columns and app fields. Select Quantity (link to the second column) and Price (link to the third column). Add the * operator to multiply the contents of the second and the third columns.
In the Type* field, select Money, as the column will show the total cost for each order position.
Note that the types of fields you can add to the formula depend on the type selected for the column. To add a field, click {+} and select a variable in the drop-down list. For a String type column, you can only use fields of the String type in the formula. For a Number type column, you can only add Number type fields. If you select a variable of a different type, the formula won’t be validated. However, for a Money type column, you can use the Money*Number combination, that is, you can multiply the value of a Money type field by the value of a Number type field.
начало внимание
For a Formula type column, ELMA365 can show the total in the footer. Click the footer. In the drop-down list, select the function you need: text, total, maximum, minimum, or average.
конец внимание
Now when an employee fills out the table, he or she will only need to select a product and enter the quantity, and the total cost will be calculated automatically.
|
Delete columns or rows from a table
To delete a column, open the table settings window. Click the gear icon next to the name of the column you want to delete. A pop-up window will open. In the bottom right corner, click the recycle bin icon and confirm deletion.
If you want to delete a row in a table, you can do it on the app’s create or edit form. Hover over the row you want to delete. Then click the recycle bin icon that appears on the left. Alternatively, you can use the Сtrl + Delete keyboard shortcut to delete a row you select.
Note that if the table includes columns with the Read only option enabled, you won’t be able to delete rows. You can change this by opening the table settings and checking the Allow deleting rows with read-only cells box.
Add data from another app to the table
When creating a table, you might need to refer to data from another app. Suppose you keep records of all stock items in the Stock List app, and all customer orders are accounted for in the Orders app. You can add a table to the order form, in which employees can select stock items from the drop-down list. At the same time, ELMA365 will automatically retrieve the item price from the Stock List app and display it in the table.
To do this, add a Table type field to the Orders app form. Then, in the table settings, create the Data column. In the Type field, select App and add a link to the Stock List app. The column name is filled in automatically and matches the name of the app item.
Once you've completed the settings, save the table. On the app form, you will see a table that will refer to the items of the Stock List app. By clicking on the cell in the Product column, an employee can select a product.
The Stock List app also stores each product’s price. You can add a Formula type column to the table and configure it so that the information about the price is displayed automatically. The employee will only need to select the product’s name.
To do this, add a Formula type column. In the column creation window, in the Formula field, click the {+} icon. In the drop-down list, you will see the names of other columns and app fields. Select Product (link to the first column), then Price per unit (property).
Now, instead of entering this information into the form manually, the employee will open a drop-down list, select and item, and the system will automatically show its price.
Provide access to a file in a table
Take a look at the following example:
- there is a Table-type field added to an app item form;
- File-type data is used in the table's column;
- during a business process, a user is given permissions to the app item but they cannot view the file in the table.
In this case, you can use a script and the .setPermissions() method to provide access to the file.
начало внимание
The Administrator always has access to files regardless of access permissions.
конец внимание
Script example:
async function setPerm(): Promise<void> {
// Find the current user
const user = await System.users.getCurrentUser();
// Get the current app item
let app = await Context.data.order.fetch();
// Process each row with a cycle
for (let i = app.data.table!.length - 1; i >= 0; i--) {
const row = app.data.table![i];
// Check if a row stores a file. Here you can specify other data for providing access to the file
if (row.file) {
// Create a new object of the Tpermissions type with permissions to read and delete. Give access to the user written to the user variable
const permissions = new Permissions([
new PermissionValue(user, [PermissionType.DELETE, PermissionType.READ]),
]);
// Provide permissions to the file of each row
await row.file.setPermissions(permissions);
}
}
}
Note that in this example the access will only be given to the user stored in the user variable. All the other users will lose access to the file. To avoid that, add new permissions to the array of existing ones using methods .getPermissions() and .push. Code example:
// get current permissions
const currPermission = await row.file.getPermissions()
// create access object
const NewPermission = new PermissionValue(user, [PermissionType.DELETE, PermissionType.READ]);
// Add access object to the existing access permissions for the file
currPermission.values.push(NewPermission);
await AppFetch.setPermissions(currPermission);
Found a typo? Highlight the text, press ctrl + enter and notify us