Introduction to App Builder - Lesson 7: More about rules

This is the seventh lesson of the Introduction to App Builder tutorial series.

We've been using rules to develop our Northwinds application since Lesson 3, but now we'll deepen our understanding of them by getting acquainted with a few more concepts surrounding rules and their usage.

Default rules

If you want certain pieces of data to be added by default to new record(s) as soon as they are created, you need to use what we call a default rule. For example, if you're adding a new product, there needs to be a default price.

Default rules are created in the same area of App Builder where you build other rules, but before we create our first default rule, we'll take a look at another important concept: joins.

Joins

When we're creating rules, it is sometimes necessary to draw data from two separate tables. To do this, we join the tables together, using what we call a join. There are three types of joins: inner, left outer, and right outer.

Join types

Inner join: An inner join only returns the values that exist in both tables.
Left outer join: A left outer join returns all the values from the left table, regardless of whether or not they also appear in the right table.
Right outer join: A right outer join returns all the values from the right table, regardless of whether or not they also appear in the left table.

Create a default rule

Let's create a default rule for the example we used above: we don't want the end users of our Northwinds application to set a unit price. Therefore, we'll create a rule that pulls the unit prices from the data source.

In Lesson 6, we've already set the Unit Price controls in the application UI to be read only, so now we'll create a rule that specifically dictates this value.

Go to App Workbench > Rules and click + Rule.
Following App Builder's naming conventions, give it the name OrderDetail (Default UnitPrice).
In the Purpose field, select Default.
In the Target field, select the OrderDetail table.
Click Create.
Once App Builder creates the rule, you'll see the OrderDetail table in the Tables tab. We'll need the OrderID and ProductID columns.
Still in the Tables tab, click + Tables and select the Product table as well. It will appear linked to the OrderDetail table, because they are connected by a foreign key relationship.
Select the UnitPrice column in the Product table.
App Builder will automatically write the join, but click the Joins tab to make sure there are no errors:
As with all rules, it's always a good practice to validate it by clicking the Results button. You should see three columns, with one being the unit price for a given product.

Add a default rule to a panel

The default rules must be connected to a panel in order to have any effect. So let's return to the Orders page edit screen and select the OrderDetails panel.

Click More > Defaults. The Defaults dialog opens.
Click Register and another dialog will open, with a few options:
1. In the Type field, select Rule.
2. In the Rule field, select OrderDetail (Default UnitPrice).
3. In the Binding field, select Implicit.
  
  Note
  
  To learn more about the two binding options, see Implicit and explicit binding.
4. Click Save.

If you return to the Orders page preview now and try to add a new entry in the Order Details panel, you'll see that now the Unit Price is filled in automatically when you make a selection in the Product field, meaning your Default rule is working.

List retrieves

List retrieves are a way of expanding the functionality of list controls. A regular list control allows you to pull the ID value of a given record. A list retrieve enables you to also pull other values. We will demonstrate how list retrieves work by improving our existing Product (List) rule.

Navigate to the Product (List) rule by finding it in App Workbench > Rules.
In the Columns tab, click + Column.
In the Column or Expression field, enter P.UnitPrice.
In the Target field, select UnitPrice.
Click Save.
Now let's update the control that uses this rule. Navigate to the Orders page edit screen by finding it in App Workbench > Pages.
In the Order Details panel, select Controls to see the list of controls present in the panel.
Double-click the row of the Product control.

Note

You can also access these same options by previewing the Orders page with the View Page button, then opening the live designer in the action drawer and selecting the Product control.
In the List Options tab, click the Retrievals button.
In the Retrievals dialog, click + Add Retrieval.
In the Panel Column field, select UnitPrice. In the (List Source) Column field, select UnitPrice.
Click the confirmation icon to save and exit out of the dialog.

App Builder will take a few moments to save. After it does, return to the Orders page preview and try editing one of the products in the Order Details page, you'll see that the Unit Price control gets automatically updated based on your selection.

Validation rules

Another type of rule you can leverage are validation rules. As implied the name, they can be applied to certain columns to ensure that only data of a specific kind can be stored in them. Validation rules can be applied directly to tables in the data layer or to business objects in the business layer.

We'll demonstrate how validation rules work by creating one that rejects any discounts greater than 10%. To do so, we're going to use a WHERE clause.

Go to App Workbench > Rules.
Click + Rule to create a new rule.
Following App Builder's naming conventions, give it the name OrderDetail (Discount Validation).
In the Purpose field, select Validation.
In the Target field, select OrderDetail.
Click Create.
In the Where tab, click + Where Clause. A dialog opens where you can create WHERE clauses:
In the Left Expression field, enter OD.Discount.
In the Operator field, select >.
In the Right Expression field, enter .1.
Click Save and exit out of the dialog. App Builder has created the validation rule.

Now that the validation rule has been created, let's make sure it applies to the business object we created it for.

Go to App Workbench > Rules.
Select OrderDetail (Source), the business object that holds discount values.
Click Events. A dialog called All Events opens.
In the Save row, you can see which things take place when the end user saves a new record. That's where we need to apply the Validation rule we've just created. Click Rule Event Detail in that row.
In the Validations panel, click Register. The Validation dialog opens:
In the Type field, select Rule.
In the Rule field, select OrderDetail (Discount Validation), the validation rule we've just created.
In the Binding field, select Implicit.

Note

To learn more about the two binding options, see Implicit and explicit binding.
In the Message field, enter which message will be displayed to end users if they try to enter invalid data. This field is mandatory.
Leave the other fields with their default values and click Save to exit out of the dialog.

Return to the Orders page preview and try to add a new product in the Order Details panel with a discount greater than 10%. When you try to save, you should see an error show up, along with the error message you typed in.

Application level parameters

Instead of limiting the discount that users can apply directly through a rule like we just did, we can also make the discount limit a value that users can modify. The way that we can do that is by adding it as an application level parameter and storing it in the Parameter table.

In App Workbench > Pages, navigate to the Parameter page, which we created back in Lesson 4.
In the Panel tab, find the Panel Source field and click the table icon. This will take you to the Parameter table edit screen.
In the Columns tab, add a new column by clicking + Column.
Give it the name DiscountMaximum.
In the Logical field, select Number.
In the Physical field, select Decimal.
Click Save.
Now, let's add the new column to the business object that's based on the Parameter tab. You can either find it in App Workbench > Rules or by returning to the previous screen and then clicking the business logic icon next to the Panel Source field.
Once you're at the Parameter (Source) rule edit screen, go to the Tables tab and find the new DiscountMaximum column in the list of columns from the Parameter table. Click the checkmark to select it.
Now return to the Parameter page, either by finding it in App Workbench > Pages or by returning to the previous screen. Add the new DiscountMaximum column to the list of controls in the Parameter panel.
Now in the Parameter page preview, use the Edit button to choose whatever maximum discount you want. For instance, enter .3 to allow discounts of up to 30%.

Edit the validation rule to use the new parameter value

Even though we want to allow our users to determine the maximum discount, the validation rule we created before uses a fixed, hard-coded maximum discount, so it still won't tolerate any discounts bigger than 10%. Let's edit it to use whatever value we enter in the Parameter page. Follow these steps:

Go to App Workbench > Rules and open the OrderDetail (Discount Validation) rule.
In the Tables tab, click + Tables.
Click Add for the Parameter table.
Now that the Parameter table is also being displayed in the Tables tab, select the DiscountMaximum column.
Go to the Where tab. We need to edit the WHERE clause we created because we no longer want to use a hard-coded value here. Click the pencil icon to edit it.
In the Right Expression field, enter P.DiscountMaximum. App Builder will take a moment to save.
Now, revisit the Orders page preview. If you try adding a new product in the Order Details panel, you'll see that now you are allowed to give discounts of up to 30%.

If you try to give a discount above 30% now, you won't be allowed to. However, the error message still says the previous limit, 10%, because we also hard-coded that when we first created the validation rule. To avoid having to edit the message every time the limit changes, we are going to use dynamic substitution to make the message be automatically updated along with the limit.

In App Workbench > Rules, navigate to the OrderDetail (Source) rule.
This rule currently only pulls data from the OrderDetail table, so in the Tables tab, click + Table and select the Parameter table. When it appears, select the DiscountMaximum column.
Now the rule has access to the latest maximum discount and will be able to "see" when it changes. It is good practice to give it an Alias that we'll use to refer to it in the message, so enter MaxDiscount in that field. To make sure the number will be displayed correctly in the UI, go to the Columns tab and cast it as a Percent.
Next, navigate back to the error message we wrote previously by heading to the Rule panel and clicking Events. When the dialog opens, find the Save row and click Rule Event Detail. The validation we implemented is shown in the Validations panel. Click to edit it.
Finally, in the Message field, replace the hard-coded 10% with {{ MaxDiscount }}. Surrounding the alias with the double curly brackets will make App Builder apply dynamic substitution to show the current maximum discount instead of a fixed, unchangeable value.

CRUD rules

The last kind of rule we'll discuss is CRUD rules, which are used to update data in mass quantities. CRUD rules can be used to create, insert, update, or delete records, and you often need to do that in your App Builder applications. We'll look at how to work with CRUD rules and in the process, we'll see about two important functions. We'll work on the Employees page this time.

Create an Upper CRUD rule

Go to App Workbench > Rules.
Click + Rule.
Give it the name Employee (FirstName Upper Update).
In the Purpose field, select CRUD.
A new field, Action, appears. Select Update.
In the Target Layer field, select Data Layer.
In the Target field, select Employee.
Click Create.
In the Tables tab, App Builder will automatically select the EmployeeID column, which is the primary key of the Employee table. We don't need any others, so go ahead and click + Column.
When the Add Column dialog opens, enter the following syntax in the Column or Expression field: Upper(E.FirstName).

Note

This syntax invokes the Upper() database function, which takes a string as an argument to be capitalized and then returned. Several other database functions are available to use with App Builder.
In the Alias field, enter UpperFirstName.
In the Target field, select FirstName.
Click Save.

Once App Builder saves the new rule, check to see if it's working by clicking Results in the Rule panel. The Upper function takes each employee's name and rewrites it using all capital letters.

Attach the new Upper CRUD rule to an event

It's always best to place your CRUD rules on the tables, because that helps streamline and organize the events, especially if you ever need to make updates or changes in the future.

Locate the Employee (Source) rule in App Workbench > Rules and open it.
Click Events.
When the All Events dialog opens, click + Rule Event.
Give it the name Upper First Name. You can leave the other fields with their default values. Click Save.
When you are taken back to the All Events dialog, the new event you've just created should be available. Click the Rule Event Detail button.
In the Actions panel, click Register.
When the Action dialog opens, use the Rule field to select Employee (FirstName Upper Update), the CRUD rule we've previously created. Click Save.
You will then see the confirmation page, showing details about the new event:

Notice the Bindings panel. It shows which columns we've used to bind the records in the UI layer (the Source Column field) to the records in the rule (the Rule Column field). With this binding in place, the rule will only execute against records in the UI with corresponding values. Without binding, the rule would affect all rows returned in the CRUD rule.

Now that we've created an event, let's go add a button that executes it in our page.

Navigate to the Employees page edit screen, and access the list of controls in the Employees panel.
Click + Control to add a new control.
In the Control Type field, select Button.
In the Name field, enter First Name Upper. Click Next.
In the Execute an Event field, select UpperFirstName, our newly-created event.
Click Next and then Finish.

Now, access the Employees page preview and test the new button. Upon clicking it, an employee's first name should appear in all capital letters.

Practice time: Create a Lower CRUD rule

For practice, try to repeat these steps we've just followed, but this time creating a button that does the opposite process. Using the Lower() database function, create a button that changes employees' names to be listed in all lowercase letters.

Review and wrap-up

In this lesson, we have greatly expanded our knowledge of rules in App Builder, learning about default, validation, and CRUD rules, as well as application level parameters, and list retrieves. This concludes this tutorial series.

Your Northwinds application is now functional! It shows information about customers, the orders they place, employees, suppliers, and shippers in an organized and efficient way. The concepts and techniques you've learned during its construction can be applied to build any other application.