How to Enable the User to Add, Edit, View, Remove or Clone Row If the Conditions Are Satisfied

Required Permissions

This section requires the Application Designer permission from your Domain's administrator.

Check What are the Design Tasks? to learn more about permissions to work with Design Mode and other tools.

Abstract

• Restrict Adding New Rows
• Restrict Editing Existing Rows
• Restrict Cloning a Row
• Restrict Rows' Deletion
• Restrict Rows' Recalculation
• Restrict What Rows to Be Displayed

Learn how to configure a Formula to enable certain actions on an application's table if its conditions are satisfied.

Users will add, edit, remove, view or clone rows if and only when a restriction Formula is satisfied. Certain action commands from the Settings (Add Record, Remove Record, Clone Rows, and Recalculation) will be available in the application but not clickable until the Formulas' conditions aren't met. (See Enable or Disable Adding, Cloning, Recalculating or Removing Rows for more information about actions you can enable for the users for each application's table.)

Note

The steps are the same for both the Master and Detail tables.

Restrict Adding New Rows

Follow the steps to insert a Formula to add new rows if and only if its conditions are satisfied. These are called Add Restrictions.

1. In the Design Mode, click on the Settings in the left taskbar.

   

2. Click on the table's tab for which you want to insert a Formula to manage adding new rows.

   

3. Under the table's Settings' sub-panel, click on Add button under the Restrictions options. The action opens the Formula Editor to insert a Formula.

   

4. Click on the Save button to save all the changes. You find the Save button in the upper-left corner of the Design Mode.

   

Success

Users will add new rows to the table if and only if the Formula conditions are satisfied.

Restrict Editing Existing Rows

Follow the steps to insert a Formula to permit rows editing if and only if its conditions are satisfied. These are called Edit Restrictions.

1. In the Design Mode, click on the Settings in the left taskbar.

   

2. Click on the table's tab for which you want to insert a Formula to manage editing existing rows.

   

3. Under the table's Settings' sub-panel, click on Edit button under the Restrictions options. The action opens the Formula Editor to insert a Formula.

   

4. Click on the Save button to save all the changes. You find the Save button in the upper-left corner of the Design Mode.

   

Success

Users will be able to edit existing rows if and only if the the Formula conditions are satisfied.

Example

The application Warehouse Orders manages the orders from a warehouse.

Each Master table's row stands for an order. Each Row has a Lookup drop-down list called Order Status. Order Status records the unique Id for each possible order status. (E.g. The "Placed" status has the Id 1 and the "Cancelled" status has the Id 2.)

Order Status tracks an order's state by recording the status' Id from a Storage containing the various statuses' names (e.g. "Placed", "Waiting for Payment", "In Preparation", and so on). A specific application enables to record the different statuses of Order Status.

When an order has the state "Cancelled" or "Completed", the users shouldn't be able to make any other changes: the order is already shipped or has been archived.

In this case, it would be useful to block the editing entirely when the Order Status is either in the "Cancelled" or "Completed" states. A Restriction formula for editing is the best solution in this case.

The Designer goes in Design Mode and into the Settings panel. They click on the Edit restrictions to insert the following Formula.

/*
An Edit Restriction enables to edit a row when Formula verifies that all the conditions are verified. 
This formula allows to edit a row if and only if the order status 
differs from "Cancelled" and  "Completed". 

OrderStatusId is the Numeric field that records the Identity of the
different lookup drop-down list's options. 

$CURRENTSTORAGE.OrderStatusId is the system variable referring
to the value of the row's OrderStatusId Lookup drop-down list.

The function =NEQ() checks that two values AREN'T the same.

In particular, =NEQ($CURRENTSTORAGE.OrderStatusId;2) means that
OrderStatusId MUST NOT be equal to the Id 2. Id 2 stands for the
"Cancelled" status.

=NEQ($CURRENTSTORAGE.OrderStatusId;6) means that
OrderStatusId MUST NOT be equal to the Id 6. Id 6 stands for the
"Completed" status.

The =AND() function accepts as parameters as many formulas and 
values as desired. =AND() verifies that all the its parameters
are satisfied.

In this case, the Formula checks that the Order Status IS NOT
either "Cancelled" o "Completed". If  "Cancelled" or "Completed", the Formula will block any edits.

*/

=AND(
   =NEQ($CURRENTSTORAGE.OrderStatusId;2);
   =NEQ($CURRENTSTORAGE.OrderStatusId;6)
)

The Designer saves the configuration and the changes in Design Mode.

Now, the application won't permit changes to any row having the "Cancelled" or "Completed" status.

Restrict Cloning a Row

Follow the steps to insert a Formula to permit rows cloning if and only if its conditions are satisfied. These are called Clone Restrictions.

1. In the Design Mode, click on the Settings in the left taskbar.

   

2. Click on the table's tab for which you want to insert a Formula to manage cloning rows.

   

3. Under the table's Settings' sub-panel, click on Clone button under the Restrictions options. The action opens the Formula Editor to insert a Formula.

   

4. Click on the Save button to save all the changes. You find the Save button in the upper-left corner of the Design Mode.

   

Success

Users will be able to clone existing rows if and only if the the Formula conditions are satisfied.

Restrict Rows' Deletion

Follow the steps to insert a Formula to permit rows deletion if and only if its conditions are satisfied. These are called Delete Restrictions.

1. In the Design Mode, click on the Settings in the left taskbar.

   

2. Click on the table's tab for which you want to insert a Formula to manage rows' deletion.

   

3. Under the table's Settings' sub-panel, click on Delete button under the Restrictions options. The action opens the Formula Editor to insert a Formula.

   

4. Click on the Save button to save all the changes. You find the Save button in the upper-left corner of the Design Mode.

   

Success

Users will be able to delete one or more rows if and only if the the Formula conditions are satisfied.

Example

The application Warehouse Orders manages the orders from a warehouse.

Each Master table's row stands for an order. Each Row has a Lookup drop-down list called Order Status. Order Status records the unique Id for each possible order status. (E.g. The "Placed" status has the Id 1 and the "Cancelled" status has the Id 2.)

Order Status tracks an order's state by recording the status' Id from a Storage containing the various statuses' names (e.g. "Placed", "Waiting for Payment", "In Preparation", and so on). A specific application enables to record the different statuses of Order Status.

Each User belongs to one or more User Groups to classify their roles and operations. (E.g. Clerk, Warehouse Coordinator, Stocking Associate, Forklift Operator, and so on.)

Users should be able delete orders' rows only under certain circumstances:

1. The order status is "Cancelled".
2. The user is either a Clerk or a Warehouse Coordinator. If the management have assigned their role, they know what they are doing: they can delete rows freely.

In any other case, the users should not be able to delete the rows. A Restriction formula for deletion is the best solution in this case.

The Designer goes in Design Mode and into the Settings panel. They click on the Delete restrictions to insert the following Formula.

/*
 An Delete Restriction enables to edit a row when Formula 
 verifies when all the conditions are verified.

 This formula allows to delete a row when at least one condition
 is satisfied:

   - if the order status differs is "Cancelled" (Id 2).
   - if the user is either a Clerk or a Warehouse Coordinator

 The =CONTAINSLIST() function accepts two list of items as parameters.
 =CONTAINSLIST() checks if the first parameter's list contains
 at least one element from the second parameter's list.

 The first parameter of =CONTAINSLIST() is the system variable
 $CURRENTUSER.GROUPNAMES. $CURRENTUSER is the variable referring
 to the currently logged user. $CURRENTUSER.GROUPNAMES is the 
 option referring to the User Group list.  $CURRENTUSER.GROUPNAMES
 variable refers already to a list of values.

 The second parameter of =CONTAINSLIST() is the function =LIST().
 =LIST() is employed as parameter of formulas to insert lists of
 values by hand. In this formula, the Designer inserts a precise list
 of values, =LIST("Clerk"; "Warehouse Coordinator"); i.e. the User 
 Groups' names between quotation marks ("").

 OrderStatusId is the field that records the Identity of the
 different lookup drop-down list's options. 

 $CURRENTSTORAGE.OrderStatusId is the system variable referring
 to the value of the row's OrderStatusId Lookup drop-down list.

 The function =EQ() checks that two values are the same.

 In particular, =EQ($CURRENTSTORAGE.OrderStatusId;2) means that
 OrderStatusId must be equal to the Id 2. Id 2 stands for the
 "Cancelled" status.

 The =OR() function accepts as parameters as any number of formulas.
 =OR() verifies that at least one of its parameters is satisfied.

 The formula allows the row's deletion if the order status is
 "Cancelled" or the user's groups list contains either
 Clerk or Warehouse Coordinator or both the conditions are TRUE.
*/

=OR(
   =CONTAINSLIST(
         $CURRENTUSER.GROUPNAMES;
         =LIST("Clerk"; "Warehouse Coordinator")
         );
   =EQ($CURRENTSTORAGE.OrderStatusId;2)
)

The Designer saves the configuration and the changes in Design Mode.

Now, the application won't allow to delete orders if and only if the status is "Cancelled" or the user is a Clerk or a Warehouse Coordinator.

In other circumstances, a notification explains that the operation is not allowed for the row.

Restrict Rows' Recalculation

Follow the steps to insert a Formula to permit rows' recalculation if and only if its conditions are satisfied. These are called Recalculate Restrictions

1. In the Design Mode, click on the Settings in the left taskbar.

   

2. Click on the table's tab for which you want to insert a Formula to manage rows' recalculation.

   

3. Under the table's Settings' sub-panel, click on Recalculation button under the Restrictions options. The action opens the Formula Editor to insert a Formula.

   

4. Click on the Save button to save all the changes. You find the Save button in the upper-left corner of the Design Mode.

   

Success

Users will be able to recalculate the values of existing rows if and only if the the Formula conditions are satisfied.

Restrict What Rows to Be Displayed

Follow the steps to insert a Formula to display certain rows on the table if and only if its conditions are satisfied. These are called Row Restrictions.

Warning

Check the exceptions to considered while using Formulas in Row Restrictions at step 4.

1. In the Design Mode, click on the Settings in the left taskbar.

   

2. Click on the table's tab for which you want to insert a Formula managing which rows to display.

   

3. Under the table's Settings' sub-panel, click on Row button under the Restrictions options. The action opens the Formula Editor to insert a Formula.

   

   Warning

   You cannot employ a Logical Column (i.e. $$logicalColumnName) in a Row Restriction formula. The Formula Editor won't validate Formulas employing Logical Column references in Row Restrictions.

   Warning

   Row Restrictions work differently from other places where you can insert Formulas (e.g. Calculated Fields, Layout Colors, and so on). The context of filtering and retrieving rows for the table causes such exception. This means that Formula working as usual in Calculated Fields might not work and require some changes. Follow the rules below when writing Row Restrictions formulas:

   a. All the Formula's visiblity conditions must refer to rows' fields on the current table and application.

     // Here some examples.
   
     // NO. This won't work because it doesn't refer to values on the current table.
     =EQ($CURRENTUSER.EMAIL; "j.doe@vesenda.com")
   
     // YES. This works because it refers to values on the current table and application.
     =EQ($CURRENTSTORAGE.taskOwnerEmail; "j.doe@vesenda.com")
   

   b. Functions like =LOOKUP(), =LOOKUPLIST(), and others functions recovering information from other Storages can't refer to current table's values in the application. =LOOKUP() e =LOOKUPLIST() must refer to either constant values or system variables.

     // NO. The =EQ() refers to a value on the table, i.e. $CURRENTSTORAGE.Id_Project.
     =CONTAINS( 
           =LOOKUPLIST(
                 #sProjectManagers;
                 #sProjectManagers.Email;
                 =EQ(sProjectManagers.Id_Project; $CURRENTSTORAGE.Id_Project)
           );
           $CURRENTUSER.EMAIL
     )
   
     // YES. =EQ() refers only to costants or system variables like $CURRENTUSER.EMAIL
     =CONTAINS( 
           =LOOKUPLIST(
                 #sProjectManagers;
                 #sProjectManagers.Id_Project;
                 =EQ(#sProjectManagers.Email; $CURRENTUSER.EMAIL)
           );
           $CURRENTSTORAGE.Id_Project
     )
   

   c. When you employ =IF() functions, you must comply to the same requirements of rule (b); i.e. constant values or system variables only.

     // Example 1. NO. =EQ($CURRENTSTORAGE.Completed; $TRUE) refers to a field's value on the table.
     =IF(
           =EQ($CURRENTSTORAGE.Completed; $TRUE);
           =EQ($CURRENTSTORAGE.isApproved; $TRUE);
           =EQ($CURRENTSTORAGE.isApproved; $FALSE)               
     )
   
     // Example 2. YES. In the condition of the =IF() there are only system variables 
     and costants ("Managers").
   
     =IF(
           =CONTAINS($CURRENTUSER.GROUPNAMES; "Managers");
           =EQ($CURRENTSTORAGE.isApproved; $TRUE);
           =EQ($CURRENTSTORAGE.isApproved; $FALSE)
     )
   

   d. In a Detail Row Restriction, the system variable $CURRENTSTORAGE.$MASTER.fieldName behaves like a constant value and you can use it. $CURRENTSTORAGE.$MASTER.fieldName doesn't vary for the Detail's rows of a Master table.

     /* 
     The following Formulas as Row Restrictions for Details are valid.
     The formulas employ $CURRENTSTORAGE.$MASTER.fieldName to refer to values.
     */
   
     // First Example
     =IF(
           =EQ($CURRENTSTORAGE.$MASTER.isArchived; $TRUE);
           =NEQ($CURRENTSTORAGE.taskStatusLookupId; 2)
     )
   
     // Second Example
     =CONTAINS( 
           =LOOKUPLIST(
                 #sProjectManagers;
                 #sProjectManagers.Id_Project;
                 =EQ(#sProjectManagers.Email; $CURRENTSTORAGE.$MASTER.Email)
           );
           $CURRENTSTORAGE.Id_Project
     )
   

4. Click on the Save button to save all the changes. You find the Save button in the upper-left corner of the Design Mode.ù

   

Success

Users will view only the rows satisfying the Formula's conditions.