Database Designer for Windows: designer data field master detail project expenses

Organizer Advantage is powerful yet easy to use database manager for Windows.
Advantage Designer lets you modify our solutions or easily create new ones.
Take control of your data with our simple or buinsess database software solutions.

| Organizer Advantage HowTo/Help | Designer HowTo/Help |

Designer Help > Data Fields, Controls > MasterDetail (Advanced) > MasterDetail, Project Expenses Sample

easy-to-use database manager and database designer for Windows

How to implement Master Detail field in the Project Cost Management Solution?

TABLES

P_PROJECT; master table:

Field Name	Field Type	Notes
ID	Integer	unique record identification number
PROJECTNAME	Text	enter unique project name, this field will be used as a common field
BUDGET	Numeric	enter estimated project cost
TOTALCOST	Numeric	displayes the current total project cost from the detail table
COSTTABLE	MasterDetail	displays project expenses for the selected project; allows to add new or modify old expenses
		the table could include more project expense related data fields

P_COSTS; detail table:

Field Name	Field Type	Notes
ID	Integer	unique record identification number
EXPENSE	Text	enter description of the project expense
PROJECT	Text	stores a project name; the value is entered automatically by the program; this field will be used as a common field
AMOUNT	Numeric	enter expense
		the table could include more project expense related data fields

Designer, Layout Form.

When you insert the master detail data field (1), the designer displays the Select MasterDetail Field Options (2) window. It will help you to choose the detail table (3), the common field from the detail table (4), and the common field from the master table (5). Based on your selection in the Select MasterDetail Field Options window the Designer will create values for two attributes: SQL Query and MasterDetail1 (what will be displayed and through which fields records will be related).

master detail field, project costs sample, define options

Click on the COSTABLE box (1) to select it. Click on the Modify Control Attributes (2) to review the field's attributes.

master detail field, project costs sample, layout form

Important attributes for the COSTTABLE data field. The SQL Query and MasterField1 attributes were created by defining options the Select MasterDetail Field Options window (as explained above).

SQL Query: SELECT * FROM P_COSTS WHERE PROJECT=’%1’
This attribute defines what will be displayed in the master detail data field. In this case, it will display all project expense transactions where PROJECT and PROJECTNAME values match.
MasterField1: PROJECTNAME
This field defines the common field in the master table.
Total Columns: AMOUNT;
This attribute defines which columns in the master detail field should be totalled. Important: enter field names and separate them with a semicolon. Also, end the list with a semicolon.

master detail field, project costs sample, attributes

Result

P_PROJECTS, master table:

master detail field, project costs sample, master table

Explanation:
The PROJECTNAME value for the selected record is "November Project 2019/11", this value is used in the query.
SELECT * FROM P_COSTS WHERE PROJECT=’November Project 2019/11’
There are four expense entries for the "November Project 2019/11"
They are listed in the COSTABLE field.

P_COSTS, detail table:

master detail field, project costs sample, detail table

Add project expense transaction.

master detail field, project costs sample, add, edit detail data

Organizer Advantage, Simple Database Solutions for Windows PC

Organizer Advantage, Business Database Solutions for Windows PC

Organizer Advantage Free Service

Organizer Advantage Support