The Excel file
The generated Excel template contains all the worksheets and fields indicated during the design step. If you have checked the Accepted Values option, an extra tab will also be generated.
The generated Excel template contains three standard rows:
Row 1: Default Value. A default value for all the fields previously selected in the design step in GCON4 MFL can be defined. If no default values have been defined, the field will be RED (#NODEFAULT#).
Row 2: Mandatory. Any mandatory fields that must contain a value appear in BLUE. Mandatory fields are automatically selected in the design step in GCON4 MFL. Optional fields are indicated in GREY (for example, Head Office). Fields that may be mandatory or optional depending on whether the data is being inserted or updated are indicated in ORANGE.
Row 3: Field Name. Name of the field being entered.
Subsequent rows: Data to be entered. In this case, each supplier will have a separated row in the Excel sheet.
The Accepted Values tab: For fields in which Field Name is BLUE (Column 4 in the Excel template), click on these fields to go to the Accepted Values tab to check the allowed values.
As you can see above, the Supplier Group Id field is BLUE. If you click it, you will go to the Accepted Values tab (GREEN) where you will be able to see all the accepted values for the supplier group; in this case:
The left table contains all the valid values for the Supplier Group Id field. The right table provides a summary of the mandatory and optional relations.
Each field in the Excel template contains a description of the field, the database field name, the length admitted, and/or an explanation on how the field should be completed, if required.
For example, for the Short name field, if you select Edit comment, you will see:
For the Sequence number field in the Address Tab, if you select Edit comment, you will see:
Once all the data you want to upload to Unit4 ERP has been entered, save the Excel sheet.
To update into an empty value in a field, set the field as <#EMPTY#>. Otherwise, if the field is optional and left empty, it will be ignored during the update process.
Note: when auto-numbering is enabled:
- For Inserts: The Excel sheet must not contain Master File IDs (for example, Supplier IDs)
- For Updates: The Excel sheet must contain the Master File ID
Color based Automatic Recognition of Attribute values
GCON4 MFL allows completing the Excel sheet easier for users by color-coding the fields accordingly. Fields containing attribute values will be checked by the tool and where the value e xists in the Accepted Values sheet, the field will be automatically coloured green. Where the attribute value does not exist, the field will be coloured red.
For example:
The user creates the Excel sheet for the Customer master file containing the Customer Group attribute values:
As the user enters the values for the Customer Group attribute, the Excel sheet will validate the data entered and match it against the list of attribute values shown above:
If the value entered exists in the Accepted Value sheet, the cell will turn GREEN. If the value entered does not exist, the cell will turn RED.
Skip attribute validation
This option removes all the validation on attribute values that GCON4 MFL performs during the loading of transactions, please note when this option is checked, the templates are retrieved with fields in red colour, example:
Note: this option works along with the skip account rule validation parameter.
Multilanguage in Attribute values text
The text for different languages for attribute values is treated in GCON4 MFL through the master file ATTRIBUTE, that lists all the attributes: system and user defined. Attribute IDs have been included helping to identifying them.
Some of the attributes listed here, have their own master file in GCON4 MFL (supplier, customer, project, personnel, etc), when one of these attributes is selected, this message is shown:
For these attributes, use their own master file to upload-insert information, and just this option in attributes to enter the text for the attribute values, for the different languages.
Creating field selection templates
GCON4 MFL lets you create field selection templates. You can save the configuration of the fields previously selected during the design step in GCON4 MFL so it can be re-used at a later stage.
For example:
You have processed all the tabs, selecting only those fields you want to update. Once all the fields have been selected, you can save the configuration by selecting Template -> Save As. The configuration template is saved in the corresponding folder. When you want to use these fields again, select Load in the Template menu.
