11 - Advanced Topics

Top  Previous  Next

Set Up the UpdateOrder Form

For the Order Update form, we’ll place the columns from the Order table on an update form, perform an automatic lookup to the Customer table, add a BrowseBox Control template to show and edit the related detail items, calculate each line item detail, then calculate the order total.

 

Starting Point:

The LCLESSON.APP file and Application Editor (Tree) should be open.

 

Create the Orders table's data entry Form

1.

Highlight UpdateOrder in the Application Tree dialog, then press the Properties button.

2.

Select the Defaults tab, choose FORM(Add/Change/Delete), then press the Select button.

3.

Select the Window tab, and then press the Designer button to open the Window Designer.

4.

Resize the window taller by dragging its bottom middle handle. Move the three controls to the bottom of the window after resizing.

 

Place the entry controls for the Orders table

Instead of using the Populate Column IDE menu option, we’ll use the Data / Tables Pad dialog to populate multiple controls.

 

1.

In the Data / Tables Pad dialog, highlight the <ToDo> item under the Update Record on Disk, then press the Add toolbar button.

2.

Highlight the Orders table from the Select list, then press the Select button.

3.

Highlight the Orders table, and in the bottom half of the Data / Tables Pad, highlight OrderDate, then DRAG near the top left of the window, and DROP.

4.

Back in the Data / Tables Pad, highlight OrderNote, then DRAG just to the right of the entry box placed for the date.

5.

Switch to the Toolbox Pad, and if not visible, open it by selecting View  Toolbox from the IDE Menu.

6.

Choose (DRAG) the ENTRY control from the Toolbox Pad and drop it under the OrderDate entry control.

7.

With the Entry control still selected, press the F4 key to switch focus to the Properties Pad.

8.

Enter ORD:CustNumber in the Use property.

Since the ORD:CustNumber column is setup as a string in the dictionary, we need to add an entry control.

9.

Switch back to the Toolbox Pad, and choose (DRAG) the PROMPT control from the Toolbox Pad and drop it to the left of the CustNumber entry control.

10.

Note the Edit Value Smart Tag shown just after populating. Click on it, and enter &Cust Number: in the Text control:

 

 

11.

With the PROMPT control still selected, press the F4 key to switch focus to the Properties Pad.

12.

Change the Use property to ?CustomerPrompt.

 

 

Add a lookup procedure call into the customer list

1.

RIGHT-CLICK on the ORD:CustNumber entry control and select Actions from the popup menu.

The standard actions for any entry control allow you to perform data entry validation against a row in another table, either when the control is Selected (just before the user can enter data) or when the control is Accepted (right after the user has entered data).

2.

In the When the Control is Accepted group box, press the ellipsis button ( ... ) located to the right of the Lookup Key entry box.

3.

Highlight the Orders table in the Select Key dialog, then press the Add button.

4.

Highlight the Customer table in the Select list, and press the Select button.

These last two steps add the Customer table to the procedure’s Table Schematic as an automatic lookup from the Orders table. This will automatically lookup the related Customer table row for you, and the lookup is based on the table relationship set up in the data dictionary.

5.

Highlight CUS:KeyCustNumber in the Select Key dialog, then press the Select button.

This makes CUS:KeyCustNumber the key that will be used to attempt to get a matching valid row from the Customer table for the value the user enters into this control.

6.

Press the ellipsis button ( ... ) for the Lookup Field entry box. This makes CUS:CustNumber the column that must contain the matching value to the value the user enters into this control.

7.

Choose the BrowseCustomers procedure from the Lookup Procedure drop list.

This calls the BrowseCustomers procedure when the user enters an invalid customer number, so the end user can select from a list of customers.

8.

Check the Perform Lookup during Non-Stop Select and Force Window Refresh when Accepted boxes to ensure that the data displayed on screen is always valid and current.

 

 

9.

Press the OK button to close the Entry Actions.

10.

RIGHT-CLICK on the Entry control, and select Embeds… from the popup menu.

 

 

This displays a list of just the embed points associated with this one control. This is the quickest way to get to a specific control’s embed points, and it’s the second way you’ve seen so far to get to an embed point. There is a third method that’s still to come.

11.

Highlight the Selected event under Control Events then press the Insert button.

The Selected event occurs just before the control gains input focus. This embed point allows you to do any "setup" specific to this one control.

12.

Highlight Source then press the Select button.

This calls the Text Editor to allow you to write any Clarion source code you want. Notice that the floating Populate Column toolbox is present. Whenever you DOUBLE-CLICK on a column in this toolbox, it places the name of the column (including any prefix) in your code at the insertion point. This not only helps your productivity (less typing), but also ensures you spell them correctly (eliminating misspelled column name compiler errors).

Type the following code:

 

 

13.

Press the Save and Close button to return to the Embedded Source dialog.

It is "standard Windows behavior" that, if the user does not enter data into a control and just presses tab (or CLICKs the mouse) to go on to another control, an Accepted event does not happen (this is very different from the way DOS programs work). This allows users to easily tab through the window’s controls without triggering data-entry validation code on each control. However, sometimes you need to override this "Windows standard behavior" to ensure the integrity of your database.

The ?ORD:CustNumber{PROP:Touched} = TRUE statement uses the Clarion language Property Assignment syntax. By setting PROP:Touched to TRUE in the Selected event for this control, an Accepted event is always generated—whether the user has entered data or not. This forces the lookup code generated for you into the Accepted event for this control (from the information you entered on the Actions tab on the previous page) to execute. This ensures that the user either enters a valid Customer number, or the Customer list pops up to allow the user to select a Customer for the Order.

14.

Press the Save and Close button to return to the Window Designer.

 

Add a "display-only" control

1.

Switch back to the Toolbox Pad, and choose (DRAG) the STRING control from the Toolbox Pad and DROP it to the right of the customer number entry box you placed before.

2.

RIGHT-CLICK the string control you just placed, and select Properties from the popup menu.

3.

In the Properties Pad, change the IsPicture property to TRUE, the Picture property to @s30, and the Use property to CUS:Company:

 

 

Placing the Detail Table's Control Templates

The next key element in this window is a browse list box control, synchronized to the Order Number of this form. This will show all the rows in the Detail table related to the currently displayed Orders table row.

 

Add a Detail list

1.

Open the Control Templates Pad, highlight BrowseBox, then DRAG the control just below the customer number entry box you placed before, and DROP.

2.

Highlight the <ToDo> item below the File-Browsing List Box and press the Add button.

3.

Select the Detail table from the Select a Table dialog, then press the Select button.

4.

Highlight the Detail table, and press the Change button.

5.

Highlight OrderNumberKey in the Select Key from Detail dialog, then press the Select button.

6.

Highlight ProdNumber in the Columns list, then press the Select button.

7.

RIGHT-CLICK on the Browse box just populated, and select List Box format… from the popup menu.

8.

Select Center from the Data group’s DataJustification property drop list.

9.

Press the Add Field  button.

10.

Highlight Quantity in the Columns list, then press the Select button.

11.

Select Center from the Data group’s DataJustification property drop list.

12.

Press the Add Field button.

13.

Highlight ProdAmount in the Columns list, then press the Select button.

14.

Select Center from the Data group’s DataJustification property drop list.

15.

Press the Add Field button.

16.

Highlight LOCAL DATA UpdateOrder in the Select Column list, then press the New button.

This New button allows you to add a local variable without going all the way back to the Data / Tables Pad. We will use this new variable to display the total price for each line item (the quantity multiplied by the unit price).

17.

Type ItemTotal in the Name entry.

18.

Select DECIMAL from the Type drop list.

19.

Type 7 in the Characters field.

20.

Type 2 in the Places field then press the OK button.

21.

Press the Cancel button to close the Local Data entry process and return to the List Box Formatter.

22.

In the ItemTotal column, select Center from the Data group’s DataJustification property drop list.

23.

Press the Add Field button.

24.

Highlight the Detail table item below the File-Browsing List Box and press the Add button.

25.

Select the Products table from the Select dialog, then press the Select button.

This adds the Products table to the Control template’s table schematic as a lookup table. The related row from the Products table is automatically retrieved for you so you can display the product description in the list.

26.

Highlight ProdDesc in the Columns list, then press the Select button.

27.

Resize the columns and adjust the display formatting as you want (you’ve done this a couple of times already). Use the diagram below as a guide.

28.

Press the OK button to close the List Box Formatter.

29.

Resize the list box to display all the columns you populated into it.

 

 

Synchronize the browse list box

We want this list to only display the Details table rows that are related to the Customer table row currently being edited. Therefore, we need to specify a Range Limit.

1.

RIGHT-CLICK the list box you just placed, and select Actions from the popup menu.

2.

Press the ellipsis ( ... ) button for the Range Limit Field.

3.

The DTL:OrderNumber column is automatically populated.

4.

Choose File Relationship from the Range Limit Type drop list.

5.

Type Orders in the Related File column, or use the ellipsis to select it from a selection dialog.

 

Add an order invoice total calculation

Now we want to calculate the order total and save it in the Orders table.

1.

Select the Totaling tab.

2.

Press the Insert button.

3.

Press the ellipsis ( ... ) button for the Total Target Field.

4.

Highlight the Orders table in the Tables list, select ORD:InvoiceAmount from the Columns list, then press the Select button.

This is the column that will receive the result of the total calculation.

5.

Choose Sum from the Total Type drop list.

6.

Press the expression ( E ) button for the Field to Total.

7.

In the Expression Editor dialog, DOUBLE-CLICK on ItemTotal from the Columns list, then press the OK button.

This is the column whose contents will be used in the total calculation. So far we’ve only declared this column and not done anything to put any value into it, but we’ll get to that very soon.

8.

Choose Each Record Read from the Total Based On drop list.

9.

Press OK to close the Browse Totaling dialog.

 

Change the object name

Now we want to change the name of the browse object to make our code more readable (you’ll see why a little later).

1.

Select the Classes tab.

2.

Type BrowseDTL in the Object Name column.

3.

Press the OK button to close the Actions dialog and return to the Window Designer.

 

Add horizontal and vertical scroll bars

1.

With the list box still selected, press F4 to switch focus to the Properties Pad.

2.

Set the Horizontal and Vertical properties to TRUE.

3.

Press the Save and Close button to save your changes, and then let’s continue and re-enter the Designer by pressing the Designer button.

 

Add the standard table update buttons

1.

In the Control Templates Pad dialog, DRAG the BrowseUpdateButtons control template, then DROP below the list box.

The Insert, Change, and Delete buttons all appear together.

2.

RIGHT-CLICK on the Delete button only, then choose Actions from the popup menu.

3.

Check the Use Edit in place box.

Checking this box for one button in the Control template checks it for all three. We will be using the Edit in Place technique to update the Details table rows instead of an update Form procedure. This will allow us to demonstrate some fairly advanced programming techniques and show just how easy they are to perform within the Application Generator.

4.

Press the OK button.

 

Add a "display-only" control for the invoice total

1.

Switch back to the Toolbox Pad, and choose (DRAG) the STRING control from the Toolbox Pad and DROP it below the bottom right corner of the list box.

2.

RIGHT-CLICK the string control you just placed, and select Properties from the popup menu.

3.

In the Properties Pad, change the IsPicture property to TRUE, the Picture property to @n$10.2, and the Use property to ORD:InvoiceAmount:

This specifies the control will display data from a variable, not just a string constant.

 

Change the form window caption and exit the Window Designer

1.

CLICK on the caption bar of the sa