PCTradeOffice sample (Delphi)

The PCTrade Office 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.

Getting Started

The sample is located in C:\Users\Public\Documents\RemObjects Samples\Data Abstract for Delphi\PCTradeOffice.

To build it, you will need a 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.

You will also need to have the TeeChart package installed. It is available from Steema, the trial version is sufficient for the sample to run.

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.

App Structure

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.

The 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.

The ClientEditForm handles editing a new or existing record in the Clients table.

The LogOnForm handles the login dialog where you can choose which user to use.

The OrderEditForm handles editing a new or existing records in the Orders and OrderDetails tables.

The ProviderEditForm handles editing a new or existing record in the Providers table.

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.

The UserInfo displays information about the user, the check boxes are set according to the values in the fields.

Retrieving Data

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 True.

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.

The OnCalcFields property is set to tbl_OrderDetailsCalcFields which is a procedure available in fClientDataModule.

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 Amount & 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;