The PCTradeOffice sample demonstrates how a complex application might use Data Abstract for Delphi to provide access to the PCTrade database (accessed through the PCTrade schema) and allow the user to browse and edit the content much like a "real" client application would.
The sample is typically located in
C:\Program Files (x86)\RemObjects Software\Data Abstract for Delphi\Samples\PCTradeOffice, though you may have installed the Data Abstract for Delphi and it's samples in another location.
To build it, you will need a version of Delphi RAD Studio and like all the samples provided with Data Abstract for Delphi you will need to be running Relativity Server with the
DASamples Domain and the
PCTrade Schema available.
Running the Sample
This sample allows you to view & alter the content of a number of different tables, from the point of view of users who have different levels of access. When the sample first starts it presents the main UI and a login dialog with three users that can be selected.
Each of three users have different access levels which provide different abilities:
- Mollie Bennet the Managing Director of the fictional company. She has access to all of the data and can view all of of the available views. In the "Orders" view she has access to all of the data unlike the other two users. She also can view the special "Sales Report" data.
- Gale Dalton who is the Income Manager, she is only able to view the "Orders", "Price List" and "Providers". Her "Orders" table is filtered to only show the orders that she has originated.
- Sandy Manning who is the Sales Manager, she is can only view the "Orders" and "Clients" tables.
Simply click on the user you want, which will populate the login fields and then press the "Login" button. Once logged in you will have a number of the tab like views available. Each one presents data retrieved from multiple tables in the remote schema. Note not all of those views are available to every user in the system.
- The Users tab provides a means to switch to different users, as well as see the access rights for that user. When you press the "Change user" button the login dialog is again presented.
- The Orders view displays data retrieved from the "Orders", "OrderDetails", "PriceList" and "RestReport" tables. Many of the fields on display are added using Lookup and Calculated fields. The data returned from the server is filtered based on the user accessing the server using Business Rules Scripting and Dynamic Where.
- The PriceList view takes data from the "Products" and "PriceList" tables are presents the data in a tree like structure, and demonstrates using
NSPredicates to filter DADataTable objects.
- The Clients view displays a table of all of the clients in the "Clients" table, and allows the user to add, edit or delete them.
- The Providers view similarly displays a table of all of the stock providers in the "Providers" table and allows the user to add, edit or delete them.
- The Reports view is only available to the managing director user, it uses a special "SalesReport" table in the PCTrade schema that uses custom SQL to pull together data from multiple schema tables and return that. The "SalesReport" table can be passed two parameters (a start and end date) that then filters the data based on those dates.
Examining the Code
This section gives an overview of the classes that makes up this sample as well as specific details related to using the classes provided by Data Abstract for Delphi.
The sample is built around many different classes though the principle class is
ClientDataModule as it handles the interaction with the instance of Relativity Server including retrieving the initial data, logging into and out of the server, providing the calculated fields and loading data from a briefcase if it is available.
ClientForm defines the principle user interface showing only the appropriate tabs for currently logged in user. It handles the events related to clicking on any of the add, edit and other buttons available in the tabs.
ClientEditForm handles editing a new or existing record in the
LogOnForm handles the login dialog where you can choose which user to use.
OrderEditForm handles editing a new or existing records in the
ProviderEditForm handles editing a new or existing record in the
SelectProduct handles adding / editing products on an order. The form is displayed when the "Add Product" button is pressed on the dialog for editing orders.
UserInfo displays information about the user, the check boxes are set according to the values in the fields.
The sample first tries to extract a table (
Vendors) from from a Briefcase, if that fails then each table is retrieved from the schema and then added to the briefcase. Finally it is written to disk so it is available the next time the sample loads.
procedure TClientDataModule.LoadCatalog(); var lbr:TDAFileBriefcase; begin lbr :=TDAFileBriefcase.Create('Catalog.daBriefcase', False, False); try try lbr.Fill([tbl_Vendors]); except lbr.Clear; tbl_Vendors.Open; lbr.AddTable(tbl_Vendors, False); tbl_ProductGroups.Open; lbr.AddTable(tbl_ProductGroups, False); tbl_OrderType.Open; lbr.AddTable(tbl_OrderType, False); tbl_OrderStatus.Open; lbr.AddTable(tbl_OrderStatus, False); tbl_Products.Open; lbr.AddTable(tbl_Products, False); lbr.WriteBriefcase; end; finally lbr.Free; end; end;
Adding or Editing a row & Applying Changes to the server
Making changes to the tables, be it editing an existing row or adding a brand new row and applying those changes to the server are all handled in the ClientForm class. When the "Add" or "Edit" buttons are pressed on a tab it goes through the following typical flow:
- Either a new row is added, or the currently selected row is marked as the active row.
- The appropriate form is opened and the fields populated as required.
- If the user clicks "Save" the changes are saved and applied to the server.
- If the user clicks "Cancel" then the changes are thrown away and the table reset to how it was before.
A concrete example of this can be seen below:
procedure TClientForm.tbAddClientClick(Sender: TObject); begin ClientDataModule.tbl_Clients.Append; if ClientEditDialog() then ClientDataModule.tbl_Clients.ApplyUpdates() else ClientDataModule.tbl_Clients.CancelUpdates(True); end;
First a new record is added to the table using the
Append procedure. Then the
ClientEditForm is opened and the fields linked to the current record. When the user presses the "Save" button on the dialog it returns
True which causes the
ApplyUpdates function to be called. If the user instead presses "Cancel" then
False is returned and the
CancelUpdates function is called to throw away the changes and return the data to how it was before.
Using a Calculated Field
There are three parts to using a calculated field:
First the field is added to the table in the design view and configured as needed, the
Calculated property needs to be set to
Next a reference to the procedure that will provide the calculated values is passed to the
OnCalcFields. This procedure will be called any time the fields value needs to be updated.
The final step is to implement the procedure that does the calculations. If there are more than one calculated field in a single table, then this method needs to handle all of the calculations.
In the example below the
Sum field is added to the
OrderDetails table, indicating that it will take a float, should have a display width of 8, have a display format of
0.00 as its representing currency.
OnCalcFields property is set to
tbl_OrderDetailsCalcFields which is a procedure available in
Then the actual implementation of
tbl_OrderDetailsCalcFields takes the passed in
TDADataTable, finds the required field and assigns it a value which is the result of multiplying the values in the
Price fields of the current record.
// fClientDataModule.dfm item Name = 'Sum' DataType = datFloat LogChanges = False DisplayWidth = 8 DisplayFormat = '0.00' Alignment = taRightJustify Calculated = True end ... snipped code ... OnCalcFields = tbl_OrderDetailsCalcFields // fClientDataModule.pas procedure TClientDataModule.tbl_OrderDetailsCalcFields(DataTable: TDADataTable); begin DataTable.FieldByName('Sum').AsFloat := DataTable.FieldByName('Amount').AsInteger*DataTable.FieldByName('Price').AsFloat; end;