Simple Movements And Shapes

Continuation of the article “Production: accounting and control”

The basis of the program is accounting for the movement of commodity units along a certain chain of counterparties. Before proceeding to the implementation of forms, it is necessary to clarify business processes and flow patterns, which may differ depending on the chosen accounting policy at the enterprise.

Movements

Regardless of the scale and type of production, the following scheme for the movement of material assets is used:

1. Purchase of material from a supplier
2. Write-off of material for production
3. Receipt of finished products
4. Shipment of finished products to the buyer

In some cases, additional movements are used:

• Returning material to the supplier
• Returning material to scrap
• Returning finished products from the buyer
• Returning finished products to scrap
• Moving material or finished product from one storage location to another

Each movement is a separate entry in the oper table, which indicates the item batch (id_pitem), quantity (qty) and amount ( amount). Information about the direction of movement is located in the title of the document with which the operation is associated (id_operDoc).

The header (table operDoc) contains information about the date of the operation (docDate), document number (docNum), as well as information about the direction of movement: from where (id_contr) and where (id_contr1) the material assets are moved.

Information about the amount when moving is only relevant in three cases:

• Purchasing material from a supplier
• Receiving finished goods
• Shipping finished goods to a customer

Purchasing and posting determines the purchase price of a batch of goods, and shipment determines the selling price, which allows you to determine the profit from the sale. All other transfers are made at the purchase (posting) price, including write-offs. Thus, scrap losses are always taken into account at the cost of the purchase.

For correct accounting, it is necessary that the amount of materials written off for production be equal to the amount of posting of finished products. In the simplest case, control is carried out manually, but in the future a special mechanism will be created to ensure the precise implementation of this rule.

Structure Adjustment

After some thought, I decided to add one more table: unit (units of measurement). It does not affect the calculations in any way, but adds the ability to specify units of measurement: meters, kilograms, liters, etc. This may be useful in the future when generating printed reports.

Forms

For any kind of movement, there must be appropriate forms – any choreographer will agree with me on this. So we need the forms of directories and the form of the journal of documents – tabular forms of data presentation. We also need edit forms – one for each tabular form.

Usually, the minimum set of shapes can be calculated using the formula:

<Number of Forms> = <Number of editable tables> *2

This should be taken into account when estimating development time. A more accurate estimate is based on the fact that the time it takes to create a form is proportional to the number of fields in the table for which the form is being created. At the same time, it should be taken into account that during the development process, the complexity of the work (time spent) may increase due to the addition of calculated fields or the need to write complex queries to select data.

Computed fields speed up application development, because when writing a SQL query, you need to take care of extracting data for only one field. However, when My Visual Database starts using calculated fields to form its internal data queries to display tables or edit forms, system performance can suffer. This is not scary at the prototyping stage when you need to quickly create a working application. But in the future, when the amount of data stored in the database grows, it is necessary to revise and replace the use of calculated fields with full-fledged SQL queries for selecting tabular data.

Main form and menu

This project uses the MDI (Multi Document Interface) principle – all table view forms are displayed inside the main form. And to display the desired form, the main menu items are used.

To implement this solution, add a panWork panel to the main form frmMain, stretch it over the entire client part and set the Anchors property in such a way that the panel has been anchored to all sides of the parent form.

To work with the main menu, let’s add a menu item adding function Menu_Add() and a menu item selection handler procedure Menu_ItemOnClick().

function Menu_Add( AName:string; ACaption: string; AParentItem:TMenuItem; AIndex:integer = -1; AOnClick:string = 'Menu_ItemOnClick'; ):TMenuItem;
// adding a menu item
// AName - item name; action and parameter are set: <action>_<parameter> ; actions: Show - display the form on the working panel of the main form, parameter - form name
// ACaption - the displayed name of the menu item
// AParentItem - parent element; if nil, then the menu item is added to the top level
// AOnClick - handler
var
  tmpForm:TForm;
begin
  tmpForm := MainForm; // work with the menu on the main form
  Result := TMenuItem.Create( tmpForm );
  // if the name is specified, then we format it according to the naming standard
  if AName <> '' then
    Result.Name := T_MENU_ITEM + AName; // add class prefix
  Result.Caption := ACaption;
  // if the handler is not disabled, then add it
  if AOnClick <> '-' then
    Result.OnClick := AOnClick;
  // if the parent element is not specified, then
  if AParentItem = nil then
  begin // add a menu item to the top level
    if AIndex = -1 then
      tmpForm.Menu.Items.Add(Result)
    else // or insert at the position specified in the parameter
      tmpForm.Menu.Items.Insert(AIndex,Result);
  end
  else // if specified, then
  begin // add as a child
    if AIndex = -1 then
      AParentItem.Add(Result)
    else // or insert at the position specified in the parameter
      AParentItem.Insert(AIndex,Result);
  end;
end;

procedure Menu_ItemOnClick(Sender:TObject);
// click on the menu item
var
  tmpItem:TMenuItem;
  tmpAction:string;
  tmpParam:string;
  tmpForm:TForm;
  tmpShowForm:TForm;
  tmpPanel:TdbPanel;
begin
  CForm(Sender,tmpForm);
  tmpItem := TMenuItem(Sender);
  // extract the action from the name
  tmpAction := DeleteSuffix(DeleteClassName(tmpItem.Name));
  // extract the parameter from the name
  tmpParam := Trim(GetSuffix(tmpItem.Name));
  // this part can be further expanded
  case tmpAction of
  'Show': begin // show the form on the main form
    FindC(tmpForm,'panWork',tmpPanel);
    tmpShowForm := App_GetFormByName(tmpParam); // find the form to display
    if tmpShowForm = nil then
      RaiseException('Menu_ItemOnClick() form not found: '+tmpParam);
    Form_ShowOnWinControl(tmpShowForm,tmpPanel); // display the form on the panel
    // include the name of the displayed form in the title of the main form
    tmpForm.Caption := APP_NAME + ' ' + APP_VERSION + ' - ' + tmpShowForm.Caption;
  end;
  else RaiseException('Menu_ItemOnClick() unknown command: '+tmpAction)
  end;
end;Code language: Delphi (delphi)

A key feature of the menu mechanism is the storage of an action and its parameter in the name. At the moment, there is an implementation of only one action – displaying the specified form inside the main form of the application, and the name of the displayed form is passed as a parameter.

Description of functions CForm(), DeleteSuffix(), GetSuffix(),  App_GetFormByName(), Form_ShowOnWinControl() and constants APP_NAME, APP_VERSION can be found in "ClassExplorer".

Tabular Forms

There are three types of tabular forms:

• Simple
• Detailed
• Subordinate

Each form has a table view, a panel with buttons configured for editing data (btnNew, btnEdit, btnDelete). Images for buttons will be loaded

Let’s consider each type in more detail.

Simple shapes

Simple forms are designed for maintaining directories, they consist of a table view and a toolbar with three buttons for adding (Action = [NEW RECORD]), editing (Action=[SHOW RECORD]) and deleting (Action=[DELETE RECORDS]).

You can read more about customizing buttons and table view in my book "Creating Apps in My Visual Database.Beginner".

Simple forms in our application include:

• frmContr – directory of counterparties
• frmItemType – reference of item types
• frmUnit – unit reference

The names of the forms correspond to the names of the tables they are intended for editing.

Shapes with detail

In some cases, it is necessary to simultaneously see not only the table being edited, but also the data subordinate to it. To do this, in addition to the table view and the toolbar with editing buttons, the form has a multi-page tab and a special button designed to update the details when moving around the main table.

The btnUpdateDetal button has the property ACTION = [SQL query], but there is no query in the button settings, only an event handler in which the data in dependent tables is updated. This is done so that this button can be specified in the Increm, Search property of the tgrMain table. This setting allows you to process a change to the selected record in the table, regardless of whether the change is caused by a mouse click or by pressing keys on the keyboard.

Here is an example of such processing for a document and its detailing:

procedure frmOperDoc_btnUpdateDetail_OnClick(Sender: TObject; var Cancel: boolean);
begin
  // pass the parameter to the dependent form
  frmOper.edtIDMaster.Value := frmOperDoc.tgrMain.dbItemID;
  // update the data
  frmOper.btnUpdate.Click;
  // cancel button action
  Cancel := True;
end;
Code language: Delphi (delphi)

The Form_ShowOnWinControl() procedure, which is called in the UserApp_InitForm() application’s custom form initialization procedure, is used to place the detail form on the corresponding multipage tab. Sample call for document detailing:

 Form_ShowOnWinControl( frmOper, frmOperDoc.tshOper );Code language: CSS (css)

Detail forms in this application:

• frmItem – item reference book
• frmOperDoc – document journal

Subforms

Subforms basically also have a table view and edit buttons, but the table content is built using a btnUpdate button with a [SEARCH] action. The text editing element edtIDMaster.

While a drop-down list (TdbComboBox) is usually used to filter data, in this case a text input box (TdbEdit) is preferable. This is due to the fact that each drop-down list is automatically updated both when the program starts and when editing the table to which it belongs, which in turn slows down the application and wastes computer resources.

Application Subforms:

• frmOper – movement operations (document detailing)
• frmPItem – item batches
• frmProductItem – composition of the product

Editing Forms

Each tabular form has a corresponding edit form:

• efmCont
• efmItem
• efmItemType
• efmOper
• efmOperDoc
• efmPItem
• efmProductItem
• efmUnit

For all forms, a common design has been chosen: in the central part of the form there are editing elements, and at the bottom – a control panel, on which there are buttons for saving btnSave and canceling btnCancel.

Subform editing forms have an additional invisible edtIDMaster element, which stores the relationship with the main table.

This link is established when a new record is created using a script that is called when the edit form is opened. For example, as when opening the form for editing the composition of the product:

procedure efmProductItem_OnShow(Sender: TObject; Action: string);
begin
  if Action = 'NewRecord' then
  begin
    efmProductItem.edtIDMaster.Value := frmItem.tgrMain.dbItemID;
  end;
end;
Code language: Delphi (delphi)

Since it may be necessary to create a new batch when registering a purchase (and such a need arises every time the purchase price changes), the efmOper form has a special button for adding a new batch btnNew_PItem.

You should not configure this button directly to add data ( Action = [NEW RECORD] ). The fact is that according to the internal logic of MVDB , when pressed, such a button will try to save the current record. This logic works well for editing detail tables, but is not suitable in our case. Therefore, a regular button and a small script are used here:

procedure efmOper_btnNew_PItem_OnClick(Sender: TObject; var Cancel: boolean);
begin
  // this is necessary so that the record ID is not reset after saving
  efmPItem.btnSave.dbDontResetID := True;
  // open edit form in data adding mode
  efmPItem.NewRecord('pitem');
  // select created element
  efmOper.cmbPItem.dbItemID := efmPItem.btnSave.dbGeneralTableId;
end;
Code language: Delphi (delphi)

To be continued