Table Joins & Interactive Extensions

Extending the App: Themes and Orders Setup

We are going to extend our application further by building onto our existing items table. We will get started on working with themes and theme classes and we’ll create our orders facility that will allow users to place orders in our store portal.

We will extend the orders table further by joining it with tables and adding property class extensions to support drill downs. We will create a drive field for a calculation and we’ll expand on the inheritance model by building out component classes and validators that can be reused throughout our application.

Join tables to Orders

Now that we have all of our tables built out, we can now add in some table joins that will link together the related tables.

Launch Mongoose and select Form > Open.

In the Filter field, type: IDOs, and select the IdoCollections form.

Click Ok.

For the IDO Name field, type: Orders and then press F4 to query the data.

With our Orders IDO selected, click on the Table Button

Then click the New Table button.

Select the Customer table, and for the Table Alias type: cus.

We want to join the the ID from the Customer table, to the CustomerID on the Orders table. In the Join Conditions section, create the following join statement:

ID = Orders(ords).CustomerID

Click Add then Ok

Click the New Table button again. This time lets create a join to the Items table.

Set the Table Name to Items and for the Table Alias field, type: itms. In the Join Conditions section, create the following join statement:

Item = Orders(ords).Item

Click Add, then Ok.

Binding joined properties

Now that we have our tables properly joined, we can bind properties from those tables onto our Orders table.

On the IDO Collections form still open, click the Tables button.

Select the Customer table row and then click the Bind Properties button.

Deselect all of the properties, except for Name and Address, then click the Massage Property Names button.

When the Massage Property Names modal opens, for the Property Prefix field, type: Cus This will prefix the two properties we are adding, Name and Address, with Cus

Click Ok. Notice now in the Property Names column, that our selected properties are now prefixed with Cus. Click Finish to create the properties on the Orders IDO.

Close the IDO Properties form that automatically opens after the properties are created, and let’s bind some properties from our Items table.

On the IDO Tables form, select the Items table name row. Then click the Bind Properties button.

Deselect all of the properties except for Cost, Description, and Picture.

Click the Massage Property Names button, and in the Property Prefix field, type: Itms

Click Ok.

Click Finish.

Upon completion, this will launch the IDO Properties form. Here we can manually add attributes to our new properties.

Add the following attributes to the following properties:

Here is an example of the Label String ID field.

• CusAddress: For the Label String ID field, type: sAddress.
• ItmsCost: For the Property Class field, select our Cost property class.
• ItmsDescription: For the Label String ID field, type: sItemDescription.
• CusName: For the Label String ID field, type: sCustomerName.
• ItmsPicture: For the Property Class field, select our Picture property class.
• CustomerID: Replace the Label String ID field with: sCustomerID.

Click Save and close the IDO Properties form. Close the IDO Tables form as well.

Derived properties

So far, all of the IDO Properties we’ve used have been bound to columns on tables, but properties can also be calculations, input to server-side update logic, or relationships to other IDOs. In this next part, we will create a derived property on the Order IDO that will perform a calculation to retrieve the order total amount.

With the IDO Collections form still open, let’s add a derived property to our Orders IDO. Click the New Property button. Select Derived and click Next.

For the Property Name field, type: DerOrderTotal.

NOTE: We consider it a Best Practice to always prefix your derived properties with Der, indicating that it is a derived property.

For the Property Class field, select our Cost property class.

In the Expression text box, type in the following statement: Count * ItmsCost

Uncheck the Inherit from Property Class options.

These expressions are evaluated as a SQL SELECT statement. Property names in these expressions are case-sensitive.

Click Finish.

Let’s add a label string id to our newly created property.

On the IDO Collections form, click the Properties button.

Navigate to the DerOrderTotal property, and notice that it is inheriting the Label String ID from the Cost property class. To override that inheritance, change the Label String ID to sOrderTotal.

Select the Read Only option at the bottom of the Formatting section (all derived fields are inherently read only).

Click Save and Close the IDO Properties form and IDOs form.

Unload Global Form Objects in both Runtime mode. Form->Definition->Unload Global Form Objects.

Creating more property class extensions

Next let’s create property class extensions for the Item and CustomerID property classes to enable them to have drill downs. This is the same process we went through in Part 4 so this should look familiar.

Launch the Web Designer, and open the Orders form Form -> Orders.

Select the ItemCombobox (Item) component.

From the Properties panel, expand the Inheritance section. On the Edit Property Class Extension field, click the ellipsis.

When prompted if we want to create the property class extension, click Yes.

After the Property Class Extension modal opens, expand the Behavior section, and then expand the Add/Details and Find Form section. Set the Add/Details and Find Form fields to Items. Set the Property field to Item.

Scroll down and set the Right-Click Menu field to StdDetailsAddFind, click Ok.

Next, let’s repeat this process for the CustomerID field. Select the CustomerIDCombobox component.

In the Properties panel, expand the Inheritance section. In the Edit Property Class Extension field, click the ellipsis.

When prompted if we want to create the property class extension, click Yes.

After the Property Class Extension modal opens, expand the Behavior section, and then expand the Add/Details and Find Form section. Set the Add/Details and Find Form fields to Customer. Set the Property field to ID.

Scroll down and set the Right-Click Menu field to StdDetailsAddFind.

Click Ok.

Click Save. and close the form.

Recreating the Orders form

Now that we have added more properties to the Orders IDO, we need to add these new properties to our Orders form. Rather than adding each property manually, we can use the New Form Wizard to replace our existing Order form with our new properties.

With the Web Designer still open, click the New Form Definition icon.

In the Form Wizard Selection modal:

• Category: Classic
• Wizard: Multiview

Click Ok. Once the New Form Wizard launches, for the Name field, select our existing Orders form name. Keep the default Data Source of Orders and click Next.

When prompted if we want to replace the Orders form, click Ok.

Add the following properties below and then click Next.

• OrderNumber
• CustomerID
• CusName
• CusAddress
• Item
• ItmsDescription
• ItmsCost
• ItmsPicture
• Count
• DerOrderTotal

Using the arrow buttons on the right, reorder the properties so that they appear in the order below:

• OrderNumber
• CustomerID
• CusName
• CusAddress
• Item
• ItmsDescription
• ItmsCost
• Count
• DerOrderTotal
• ItmsPicture

Click Next, and then click Finish. Say Ok to replace the form.

Remember by default, Mongoose sets a Right-Click menu definition at the component level. Let’s remove it so it doesn’t override the menu set at the Property Class extension level.

In Design Mode, open your updated Orders form, select your Customer ID combo box.

In the Properties panel, expand the Behavior section and remove the Right-Click Menu of StdDefault.

Do the same for the Item combo box.

In the Properties panel, expand the Behavior section and remove the Right-Click Menu of StdDefault.

Click Save.

Testing the property class extensions

When a property class extension is created, it is always attached to its associated property class. That means that because we have our property classes assigned on the IDO properties, they will automatically be inherited on our new Orders form.

Switch to the Runtime Mode tab, and press F4 to query the demo data we entered earlier.

Right-click on the Customer ID and Item fields, and you will see that the right-click menus defined on our property class extensions are still working.

Verify that our DerOrderTotal calculation is working properly. Change the Item or the Count field and save the record. Upon save, you will see the Order Total field will update accordingly.

Example After Save.

Displaying binary images

Rather than displaying the binary value of our Picture property, we want to only display the image itself.

Switch back to the Orders form in the Web Designer. Select the ItmsPictureStatic component and delete it by pressing the Delete key.

Select the ItmsPictureEdit component.

In the Properties panel, change the Type to Static.

Clear out the Caption field, under the Miscellaneous -> Specific Attributes section set No Colon to True.

Move the ItmsPictureEdit to the right and click and drag the corners out to increase the size.

Regenerate Form. Some form changes require you to save, close, and reopen the form to see those changes. You can use the Regenerate Form button on the toolbar to do that.

Switch back to the Runtime Mode tab and reopen the Orders form, and press the Refresh button or F4 to query the data. Our Picture property now displays the Item’s image. Select a different record on the Orders form, and you will see that the picture will update each time you switch records.

Validators

Validators are another type of global object that can be used in multiple ways. They can be set up to validate a value as well as return output values to the form for functions, like displaying associated descriptions or setting down-stream component defaults. These can be run immediately on data change or can be run upon form save. In this next part, we will create a validator that will populate the customer Name and Address fields immediately after selection of a customer.

Switch back to the Web Designer and select the Edit > Validator menu item. Once the Validators modal opens, click the + button in the bottom left corner to add a new validator. For the Name, type: CustomerID and click Ok.

Set the Type to In Collection, and in the Parameters field, click the ellipsis.

When the Edit In Collection Validator Parameters modal opens, set the IDO field to Customer and in the Collection Only sections. Use the ellipses to set the Property value to ID.

Then click the Set Prop/Var/Comp ellipsis.

On the Edit Set Values modal, click the Properties ellipsis to specify what properties we want to set.

On the Edit Set Property Values modal, click the New button.

For the Target field, type: %1. Set the Value field to Name from the drop list and keep the Collection for Target set to primary collection. Click Ok.

On the Edit Set Property Values modal, click the New button to add another property.

For the Target field, type: %2. Set the Value field to Address from the drop list and keep the Collection for Target set to primary collection.

Click Ok until you get back to the Global Objects window. For the Error Message field, select the standard error message mIsNotAValid.

Click Ok and Save.

Component classes

Component classes are another type of global object that lets you bundle together sets of component attributes that can be reused throughout an application.

Based on the inheritance model, component classes will override any attributes set at the property class or property class extension levels.

With the Web Designer still open, select the Edit > Component Class menu item.

Click the + button (New) in the bottom left corner.

Give it a Name of: CustomerID and click Ok.

Within the Global Objects modal, in the Properties panel, expand the Data Source section. Expand the Validation section and click the Validators ellipsis.

On the Validators modal, click the + button to add a validator to our component class. On the New modal, select our CustomerID validator and click Ok.

On the Validator modal, click on the CustomerID validator. Then click on the Parameters icon on the bottom.

Once the Parameters modal opens, type: %1,%2 into the first field and then click Ok.

Click Ok to close the Validators modal. On the Global Objects window, under the Data Source section, set the Validate Immediately field to True.

Click the List Source ellipsis.

On the Edit List Source Specifications modal, set the Type field to IDO Collection from the drop down list.

For the IDO field, select Customer and then click the Properties ellipsis.

On the Edit Property Specifications modal, use the Add button to add the ID, Name, and Address properties. Then click Ok.

Click the Columns to Display ellipsis and hold Shift to select ID and Name.

Click Ok.

Click Ok until you return back to the home screen.

Click Save.

Unload Global Form Objects in both Runtime Mode and Design Mode. Form->Definition->Unload Global Form Objects.

Open the Orders form and select the CustomerID Combobox component.

In the Properties panel, expand the Inheritance section, and set the Component Class field to CustomerID.

Next, we need to pass in parameters for the validator with the properties we want to populate on our form.

In the Component Class Parameters field type in the name CusName, CusAddress. Which will receive our Customer Name and Customer Address properties.

There is also a grid column component bound to the CustomerID property, let’s apply our reusable component class there as well.

Select the FormCollectionGrid component and click the Edit Grid Column(s) button in the top left corner.

From the Contained Components modal, select the CustomerIDGridCol.

Expand the Inheritance section and set the Component Class field to CustomerID. In the Component Class Parameters field, type: CusName, CusAddress.

Click Ok and Save your form.

Switch back to the Runtime Mode tab and reopen the Orders form. Test out our component class and validator by selecting a different Customer ID. After selection, the Customer Name and Address fields will now update immediately.

Now when you select a different customer, the customer name and address will immediately output thanks to our validator.

We can also test this in our grid. Click on the grid splitter and drag it to expand.

You will notice once we change the ID that both the Customer Name and Address will update there as well.

Create an Item component class and validator

In this next part, we will create another component class and validator that will be used on our Items field. This will be used to populate the item description and cost immediately after selection.

Switch back to the Web Designer and click on the Edit > Validator menu item. On the Global Objects window, click the + button at the bottom. Set the Name field to Item and click Ok.

Select the validator, and in the Properties panel on the right, set the Type field to In Collection.

Click the Parameters field ellipsis.

Set the IDO field to Items. Set the Property field to Item.

Click on the Set Prop/Var/Comp ellipsis.

On the Edit Set Values modal, click the Properties ellipsis to specify what properties we want to set.

On the Edit Set Property Values modal, click the New button.

For the Target field, type: %1. Set the Value field to Description from the drop list and keep the Collection for Target set to primary collection. Click Ok.

On the Edit Set Property Values modal, click the New button to add another property.

For the Target field, type: %2. Set the Value field to Cost from the drop list and keep the Collection for Target set to primary collection.

Click Ok until you get back to the Global Objects window. For the Error Message field, select the standard error message mIsNotAValid.

In the Properties panel, set the Error Message field to mIsNotAValid and click Ok.

Next let’s create a component class that will use this validator and be reusable.

Click on the Edit > Component Class menu item. On the Global Objects modal, click the + button on the bottom left. Set the Name field to Item, and then click Ok.

From the Global Objects modal, in the Properties panel, expand the Data Source section and then expand the Validation section. Click the Validators ellipsis.

On the Validators modal, Click the + button and for the Name field, select the Item validator.

Select the Item validator on the left, and then click the Parameters button at the bottom.

In the Parameters modal, type: %1,%2 in the first field and then click Ok.

Click Ok until you get back to the Global Objects modal. In the Properties panel, under the Data Source section, set the Validate Immediately field to True.

Click the List Source ellipsis.

On the Edit List Source Specifications modal, from the drop down set the Type field to IDO Collection. For the IDO field, select Items and then click the Properties ellipsis.

On the Edit Property Specifications modal, use the Add button to add the Item, Description and Cost properties. Then click Ok.

Click the Columns to Display ellipsis and hold Shift to select Item and Description and click Ok.

Click Ok

Click Ok again to close the Global Objects modal.

From the Form, select the Item Combobox component.

In the Properties panel, under the Inheritance section, set the Component Class field to Item.

In the Component Class Parameters field, type: ItmsDescription, ItmsCost.

Click the FormCollectionGrid component, and then click the Edit Grid Column(s) button in the top left corner.

In the Contained Components modal, select the ItemGridCol component. In the Properties panel, under Inheritance, set the Component Class field to Item.

In the Component Class Parameters field, type: ItmsDescription, ItmsCost.

Click Ok and Save.

Unload Global Form Objects in both Runtime mode and Design Mode. Form->Definition->Unload Global Form Objects.

Switch to back to the Runtime Mode tab and reopen the Orders form. Change the item on the form or in the grid to see the component class now populating the item Description and Cost immediately.

Select an item from the list and notice that the Item Description and Cost will immediately update.

Test Grid column. Change the item and you’ll notice that the Item Description and Cost change here as well.

Summary:

We expanded on our orders facility by joining it with our items and customers tables, this allowed us to create join properties from those tables onto the orders IDO. We also created our first derived property which is an unbound sql expression used to perform calculations, sub queries or calls to sql functions, we use this to calculate our total order cost. We created right-click menus for drilling into the customers and items forms using property class extensions, these extensions will inherit anywhere that property class is being used. We introduce validators and how they can be used to not only validate the values of a component but to also set the customer name and address immediately upon data change and we followed that up by introducing component classes another type of global object that lets us bundle together sets of attributes this allowed us to package up our validator along with a list source into a reusable component class.

Here is the Part 5 video showing the detailed steps to complete the previous tasks.