Fields Manager for Minutes & Nomenclatures > How to Create Free Columns with Custom Behavior

This allows you to insert specific columns associated, or not, to a set of columns:

QDV_Field Manager with List OVH

Each row of the Fields Manager of Minutes lists the attributes of a column. These attributes define:

A.The way to assign to the column a value in the Minutes/Nomenclatures, whether manually or automatically (direct entry, selection in a preset list, formula); this is done in the General section

B.The field which the free field refers to in Progress statements

C.The behavior of groups in the column; this is done in the Grouping section

D.The visibility and allowances in the various sheets; this is done in the other sections.

Proceed as follows:

1.Make sure the Free columns tab is selected

2.To add a row, click the New field button, and the New field item

3.Select the attributes as detailed in the upcoming sections A, B and C

4.Click OK; this closes the manager.

It is recommended to enter a row completely before entering another one.

NOTE A free column can still be edited later in the Minutes/Nomenclatures.

A.The General section determines the cells content in Minutes and Nomenclatures

The way the cells are filled in the Minutes and Nomenclatures depends on the Column's type:

▪Manually you type the value (text, date), or select it in a list of items preset in Field Manager (numeric)

▪Automatically, it is based on other fields values using a formula editor set in Field Manager (numeric)

▪Manually, you establish a link to a file.

NOTE The consistency between type and values is only checked when the columns are added into Minutes/Nomenclatures.

The table below describes the attributes:

Mnemonic

Field name

Parent name

Column's type

List box data/Formula

Conditional formula

Show in totals (and header of section or collapsed group)

Default value

Show default value

Is part of cost/selling price

Ignored from db of estimate (no formula)

Replace when updating

Unique field

Internal ID

Name to show as (sub-)heading

Name of column set it belongs to (or new name to group column)

-List (Mnemonic only)

-List (Name only)

-List (Mnemonic+Name)

-List (Name+Mnemonic)

-List (Tree structure)

formula only

-Nothing

-Sum: ~ shows unless all the minutes in section show the same

-Yes

-No

-Yes

-No

-Yes

-No

-Numeric

either list or formula

-Nothing/Minimum/Maximum

-If you have entered a formula, in addition, Result of formula and Sum

-No

-Belongs to cost

-Belongs to selling price

-Yes

-No

-Yes

-No

-When blank

-Yes

-No

-Text

either list or formula (constant string wrapped by """", for concatenation use operator &)

-Nothing

-Sum: ~ shows unless all the minutes in section show the same

-Result of (preceding) formula: TASK HEADER shows if nothing available

-Yes

-No

-Yes

-No

-Yes

-No

-When blank

-Yes

-No

-Date

formula based on Article/Alter date

-Nothing, Minimum, Maximum, Result of formula

-Yes

-No

-When blank

-Yes

-No

-Word doc

-Other file

-Yes

-No

-When blank

-Yes

-No

Mandatory

Alphanum. chars.

Mandatory

No / \ [ ] % & allowed

Option

The list allows selection in the Minutes. Formats:

-<ID>

-<ID>[<item name>]

-<item name>[<ID>]

For a text list, the Fill the list ... option fills the list with predefined units.

Mutually exclusive.

Click the cell twice to open the editor (or right-click to Formula editor). Use the mnemonic. The operands are separated with ; in the editor and , (comma) in the viewer. Global variables are allowed in the formula.

For a textual list, the option Fill dropdown ... populates the list with predefined units.

Used to highlight value (entered or calculated) based on a test.
Use pattern when rows are colored.
Up to 3 tests (rules) by field

Syntax without IF:
=<Intern.ID><operator><operand>

Operators are >, <, =

Wrap text in double quote

To enable it, select a formula in preceding cell. See here.
- Nothing: the cell in all total rows is blank, the field can not be inserted as WBS column, it is not listed in Other custom columns (breakdown by F12)

- Result of formula: used when column contains average, ratio..

0 shows as text, not as numeric.

Pressing Del restores default value IF next attribute = No

Formula: always No.

If No, a zero is rendered as 0

Populates the OVH >Custom Columns.

See Description. In the Minutes, the numeric value will be automatically added to total cost per unit / line selling price per unit; the currency from the overhead shows in the column header

Only when there is a database of the estimate

This derogation is explained in Database of the Estimate.

It is mainly used for lists used as keys in Spread Sheets.

'No' inhibits replacement when updating by menu Insert reference or F8 in database viewer. Mainly for Description/Unit.

See below.

Has priority over preceding attribute

The created columns of type NUMERIC populate the sheet 'Custom Columns' in the Overhead workbook. Refer to Custom Columns Sheet.

If such a field is part of the selling price, there are restrictions in the choice of the calculation modes (How to Compute the Selling Prices) and in the Forced Prices.

Each creation of a column of type LIST creates a specific sheet <Parent name>_<Shown column name> in the Overhead workbook. It is a special kind of list, similar to Kind ID, as it allows to return factors. Refer to List for Overhead. Three formats can be used in the Minutes, just as Kind ID can be replaced with the native column Kind ID and Name, or Name and Kind ID.

The Text type is also used for Multilingualism.

FORMULAS

A formula is typically used to multiply a quantity by a unit value. The operators are the same as in Excel™ (* for a multiplication). Select the operands in the list of field mnemonics. It includes the freely created fields. The operands are represented with the syntax [<Parent name>_<Mnemonic>] or just [<Mnemonic>] if there is no Parent name.

To enter formulas, double-click the cell; this opens the built-in formula editor. Enter formulas as in Microsoft Excel. Operators for char strings: &, left, right, etc

Pick up the operands in the list as illustrated:

editor

NOTE In the fields lists, QDV7 adds the / prefix to the orphans designation so that they show first in the list sorted by Designation.

In addition to invoking listed fields, a formula can reference a cell or range of the overhead sheets using this syntax:

{Overhead}<Name>

Example of formula with a range:

=VLOOKUP({Overhead}index;{Overhead}range;2)

When the name is defined in Excel, use the following syntax if the scope is a sheet:

{Overhead}<Sheet>!<Name> e.g., {Overhead}’My Sheet’!MyRef

The syntax is case-insensitive. The field attribute 'Belongs to cost or selling price' must be set to No.

Example:

Fields Manager view: for the free field TTLW, the attribute 'Show in totals' is Sum.

free_columns

Minutes view: therefore the sum (12.20) shows in the relevant column in the Total line and 48.80 in the task row

somme

SYNCHRONIZATION IN THE MINUTES VIEW

You can qualify some fields as unique; then the values of these fields in the rows with the same reference follow one another, IF the common native field "Synchronize when unique" is set to 1 for these rows in the Minutes view. The first row which receives the boolean 1 (hereunder row 2) imposes its values (hereunder Y) to the others:

	Reference	Synchronize when unique	Field whose attribute Unique field is set to Yes		Synchronize when unique	Field whose attribute Unique field is set to Yes
row 1	R		X	→	1	Y
row 2	R	1	Y	→	1	Y

Some fields cannot be qualified as unique:

▪Fields with a formula

▪Forced selling price (this poses a risk of conflict when editing on a group header)

▪Fields whose attribute “Copy inside the set” is set to yes (this poses a risk of conflict when editing on a group header)

▪Fields whose entry is allowed in WBS only

▪Fields "defined as output to row” in an in-row workbook or a Minute’s workbook

B.The Progress section

Refer to How to Compare Several Versions (Minutes).

C.The Grouping section

A group allows you to gather several minutes in one row; thus you can adjust the details level in your estimate and hide that part of it you do not want to show to the main contractor or the owner. This provides the flexibility and capacity to consolidate several minutes. Refer to How to Group Rows in the Minutes. The groups are displayed collapsed in the Reports, regardless of their appearance in the Minutes view.

In a set, the quantities in the sub-rows are linked to the quantity in the master row (header). Refer to How to Create Sets of Minutes.

The attributes determine what the group header displays in the Minutes/Nomenclatures view in the relevant column: nothing, the sum of the minute values, a formula result or the header value.

Show rows grouped (collapsed)

Copy inside the group

Copy inside the set

-No

-Sum (of minutes)

-Formula (of part A)

-Special formula -->

-Show value

-Show & edit value

Special formula when grouped

-Yes

-No

-Yes

-No

When the group/set is collapsed, what is shown?

If, in A, Show in totals

= Sum, it is forced to Sum

= Minimum, it is forced to Minimum

= Maximum, it is forced to Maximum

Click the cell twice to open the editor (or right click and select Formula editor) and select the operands. Operators as in ExcelTM

As the column can be edited later, to force the propagation at various times, check either box File>Options>Calculations >Propagate ....

It applies also when importing a set from a set database. This does not apply to values 0 and "". See Appearance of an Inserted Set

Use special formula to define a formula to apply only to the cell at the intersection free column - group header row, in grouped mode (i.e. collapsed). The 'Copy inside the group/set' attribute allows you to extend the formula to the groups/sets. The following attributes:

	Grouping
Mnemonic	Show rows grouped (collapsed)	Special formula when grouped	Copy inside the set
WPU	Special formula	=[TTLW]/[Quantity]	Yes
TTLW	Sum		No

lead, for the following set

copy_set

to, in the collapsed view

set_total_collapsed

NOTE It is not necessary to transform the set into a group.

The formula can be created automatically, as follows.

PAIRS OF FIELDS LINKED BY QUANTITY (PER UNIT and TOTAL)

If the total field shows the sum in the totals, at selling prices computation QDV7 inserts a special formula for grouping in the unit field. The next table, taken from the first sample estimate, shows in bold typeface, the values to be entered:

	General		Grouping
Mnemonic	Formula	Show in totals	Show rows grouped (collapsed)	Special formula when grouped
WPU		Nothing	Special formula	={AutoAdapt}IF([Quantity] = 0; 0; [TTLW]/[Quantity])
TTLW	=[WPU]*[Quantity]	Sum	Sum

In the Minutes view and a report of type Minutes, insert both columns. Set the attribute "Show in totals" of the total column to “Sum”; then, when the group's content is hidden, the header unit value is derived from the total with the formula (and not the other way round).

Note: The tag {AutoAdapt} in a formula is just a clue that QDV7 has automatically created the formula due to the 'Formula for quantity' special field and can automatically adapt it or remove it if it does not exist any more. You can still adapt such formulas manually.

D.The Visibility/Allowance section and the Nomenclatures section

Some attributes override others depending on the selection:

(Insertion) Allowed	Entry allowed	Overriding direction	For Nomenclatures only
	No	No overriding
	Minutes & Nomenclatures	No overriding
Nomenclatures only	Nomenclatures only (except if calculated)	←	Yes
	Others	→	No
Reports only	→		No

So, if the ‘For Nomenclatures only’ attribute is set to Yes, the allowances are forcefully set to 'Nomenclatures only' (or 'No'):

Visible in estimate	Visible in progress statement	Allowed	Entry allowed	Visible in minutes/ database of estimate/overhead minutes	For Nomenclatures only	Differentiator for articles	Shared
-Yes -No	-Yes -No	-Nomenclatures only	-Nomenclatures only	irrelevant	-Yes	-Yes (except if calculated)	-No
-Yes -No	-Yes -No	-Both -Minutes only -Nomenclatures only -Minutes - for display only -No	-Minutes and Nomenclatures -Minutes only -Nomenclatures only -WBS and Minutes (used in Minutes scenarios) -WBS and Minutes (no propagation) The following values are not compatible with field belonging to cost/selling price: -WBS only* -WBS only (no propagation)* -WBS with priority to Minutes -WBS with priority to Minutes (no propagation)	-Yes -No	-No	-Yes -No	-Yes -No
-Yes	-Yes	-Reports only	-No	irrelevant	-No	-No	-No

In Minutes edit mode, all columns are visible	How to Compare Several Versions (Minutes)	Where can the column be inserted? Can depend on 'For Nomenclatures only' attribute Reports only, if the field is: - The result of a formula - A return field from OVH - An output field from an in-row workbook The User Profiles can make the reports-only column hidden to some users Minutes - for display only: if the field is used in a formula, the result must also be set so.	Is a value allowed? Forcefully put to No by formula or after declaration as a Return factor (refer to Defining Return Values) Entry is allowed also in the collapsed set header. If no propagation, assignments are independent **. WBS with priority to Minutes: in case of difference, WBS value in red on yellow, Minutes conflicting values in red (hit Compute all); the latter will not follow changes in the WBS, unless in the WBS you right click and select Remove all derogations. Only WBS only (no propagation) and WBS and Minutes (no propagation) fields can feature a default value.		Yes for quicker entry of allowances. Makes the column invisible in the Minutes view	Articles differing in the field’s values are not merged in Nomenclatures. Incompatible with a formula	Usually price per unit visible only in Minutes & Nomenclatures. Not more than 1 list. Refer to Task Sharing. QDV7 adds the prefix _ to the ID, so that it shows first in the fields list sorted by ID

* Caveats in Links Between Tasks and Database of the Estimate.

** The propagation is NOT recommended, unless you are sure that all users use the latest version of QDV7 or that they cannot use the field manager (no administrator profile).