Managing the Customer List

The "customers" table in SuperStore database keeps the customers of the Super Store Company. We want to provide functionalities in the web application to insert/update/delete the customer records in this table.

3.	Place a panel element below the title. Place two text elements into it and give values "Name" and "Email". That will form the header of the customer list.

1.	Place a "zpro > ProcessForm > ProcessBlock" element into the upper part of the "ProcessForm" and name it as "CustomersBlock". This block will be repeated for each customer existing in the customers table of the SuperStore Database.

3.	Enter "/customers/item" in the "Repeat \| For Each" property of the block. We will now define the "customers" data source.

Name	customers
Connection Name	SuperStore
Query (SQL)	SELECT * FROM customers

5.	Place a "zpro > ProcessForm > ProcessBlock > SelectionBox" element in the block and click on the Dock to Left button () on the Docks & Anchors toolbar to easily move the element to the top-left corner of the block.

6.	Place two "zpro > ProcessForm > ProcessBlock > EditBox" elements next to the selection box and name them as "NAME" and "EMAIL". You can also dock these element to the left.

The initial values of these edit boxes will be the values of table columns "NAME" and "EMAIL" of the customer records. The "$vof(%vof(name))" definitions in the value properties of the edit boxes will be resolved to "$vof(NAME)" and "$vof(EMAIL)" during compile therefore it is sufficient just to name the elements which we already did.

8.	Place a ("zpro > ProcessForm > ProcessBlock > HiddenBox") element into the "CustomersBlock" and name it as "ID". This element will keep the identity of the corresponding customer record. We have chosen a HiddenBox instead of a EditBox because we don't want to display it to the user.

By setting the "Validation" properties of EditBox elements we can avoid errors at an earlier stage.

1.	To allow the users to delete selected customers from the database we will use a RelDBUpdateProcess. So place a "mor > RelDB > RelDBUpdateProcess" element into the "CustomersBlock" and set its properties as follows.

Name	DeleteProcess
Connection Name	SuperStore
Update Query (SQL)	DELETE FROM customers WHERE ID = @vof(f:ID)

You have noticed that the @vof(f:ID) definition in the SQL does not comply to SQL syntax. That's right; That is an MScript function which will be resolved by the RelDBUpdateProcess before executing the query through the JDBC driver. The definition means the value of the block field "ID" existing in the process block.

2.	Place a "zpro > ProcessForm > ExecuteCommand" element into the ProcessForm below the "CustomersBlock" and set its properties as follows:

Value	Delete
Process List	<callProcess name="DeleteProcess" if="_maxlcc = 0" blockName="CustomersBlock" recordFilter="_selected"/>
Validate Input	false
Next Page	%vof(PAGE_NAME)

The Record Filter column of the Process List is set to "_selected" to specify that the RelDBUpdateProcess should only process the records that are selected by the user.

We want to update the corresponding records in customers table of the SuperStore database when the customer information is modified by the user on the page.

Name

UpdateProcess

Connection Name

SuperStore

Update Query (SQL)

UPDATE customers SET

NAME = '@vof(f:NAME)',

EMAIL = '@vof(f:EMAIL)'

WHERE ID = @vof(f:ID)

You've noticed that we again used MScript functions in the Update Query property. The RelDBUpdateProcess will request from the MoreMotion to resolve the @vof() functions before executing the query.

2.	Place a "zpro > ProcessForm > ExecuteCommand" element into the ProcessForm below the "CustomersBlock" and set its properties as follows:

Value	Update
Process List	<callProcess name="UpdateProcess" if="_maxlcc = 0" blockName="CustomersBlock" recordFilter="_modified"/>
Validate Input	true
Next Page	%vof(PAGE_NAME)

The Record Filter column of the Process List is set to "_modified" to specify that the RelDBUpdateProcess should only process the records that are modified by the user.

We will not provide additional input fields on the page to insert a new customer. The user will first insert an empty record to the database table and then will update it.

We need to use another RelDBUpdateProcess but we cannot place it into "CustomersBlock" since it is a repeating block. None of the Record Filters are suitable for that. Therefore we will place the process element outside the Process Block, right into the Process Form element.

If a process element is placed directly into the Process Form it is treated as if it is placed into a non-repeating process block that has no input fields. This is exactly what we want.

Name

InsertProcess

Connection Name

SuperStore

Update Query (SQL)

INSERT INTO customers

(NAME) VALUES('New Customer')

The customers table has an ID column. The value of the ID column is automatically assigned a value by Apache Derby, MySQL and MS SQL Server but Oracle database do not have auto increment feature. Therefore we have to do it with a sequence as follows.

Update Query (SQL)

INSERT INTO customers

(ID, NAME) VALUES(customers_seq.NextVal, 'New Customer')

2.	Place a "zpro > ProcessForm > ExecuteCommand" element into the ProcessForm below the "CustomersBlock" and set its properties as follows:

Value	Add New
Process List	<callProcess name="InsertProcess" if="_maxlcc = 0"/>
Validate Input	true
Next Page	%vof(PAGE_NAME)

1.	Press "Build and Test"[Shift+F9] on the Basic Toolbar. The Application Studio will open internal web browser which will show "index".