MapFieldNamesFromTable
- 3 Minutes to read
- Print
- DarkLight
- PDF
MapFieldNamesFromTable
- 3 Minutes to read
- Print
- DarkLight
- PDF
Article summary
Did you find this summary helpful?
Thank you for your feedback
Renames fields on a specified table according to a list of source and target names derived from 2 columns in a mapping table
| Key | Value(s) | Description | ||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| method | "MapFieldNamesFromTable" | Renames fields on a specified table according to a list of source and target names derived from 2 columns in a mapping table | ||||||||||||||||||||
| project | "ProjectName" | Project Name | ||||||||||||||||||||
| description | "Description text" | The text to display in the Script Editor window | ||||||||||||||||||||
| targetTable | "TargetTableName" | The table to be modified. | ||||||||||||||||||||
| sourceTable | "MappingTableName" | Mapping Table. The table containing sourceField, targetField and searchField. The table which contains the mappings – i.e., which defines the relationship between different sets of field names. At minimum this should contain a matched list of search items and field name. There are 2 options - A and B: Option A Search item is the same as existing field name and is stored in DataModel:
Option B Search Field differs from source Field:
| ||||||||||||||||||||
| sourceField | "SourceFieldShortName" | Short name of the field on the sourceTable (i.e., mapping table) that contains the original names of the fields on the targetTable. Empty names in the sourceField will be ignored. This can be thought of as the FROM field when naming a field FROM X TO Y | ||||||||||||||||||||
| searchField | "SearchFieldShortName" | The short fieldname on sourceTable (i.e., Mapping Table) that contains the search list. A search will be made for each sourcefield that is mapped to the items in this search list. Option A
In this instance, searchField would resolve to [sourceTable.DataModel]. Because in this case sourceField also resolves to [sourceTable.DataModel], MapFieldNamesFromTable will search [targetTable] for fields called dmPRICE, dmPRODUCTNAME and dmINGREDIENT
Option B
Because sourceField resolves to [01_DATA_MODEL.DataModel], MapFieldNamesFromTable will search [ETL_Table] for fields called dmPRICE and dmINGREDIENT | ||||||||||||||||||||
| targetField | "TargetFieldShortName" | Short name of the field on the sourceTable (i.e., mapping table) that contains the new names of the fields on the targetTable. | ||||||||||||||||||||
| makeValidFieldNames | true/false | Optional. Default = false. If true, sourceField names will be adjusted to fit DataJet fieldname rules before the search is made. |
Use this method to standardize variable table field names to a fixed data model.
Renames a set of sourceFields, as found on a targetable, according to values that are read from a mapping table.
Used when mapping to and from a fixed DataModel. This can be necessary if fieldnames for DATA IN to a process are variable and/or may be missing in an input file but the script needs to process the file anyway. It can also be used to map FROM the DATAMODEL TO an expected Export format.
The method assumes that a mapping table has been created that has a list of expected standardised field names (the DATAMODEL) which must exist for some part of the script to work (stored in this example in the [01_DATA_MAPPING].[DataModel] field, and referenced in the method below as “searchField”).
{
"method": "MapFieldNamesFromTable",
"description": "EXPORT Mappings",
"sourceTable": "01_DATA_MODEL",
"searchField": "DataModel",
"sourceField": "DataModel",
"targetField": "Template 0",
"targetTable": "ETL_Table",
"makeValidFieldNames": true,
"project": "TestScript5"
}
Example - Option A
The following shows 3 tables:
- Source Table (Mapping Table = 01_DATA_MODEL)
- targetTable before change ( ETL_Table)
- targetTable after change ( ETL_Table)
sourceTable: 01_DATA_MODEL
DataModel | Code | Export Template |
dmPRICE | dm003 | Sales Price |
dmPRODUCTNAME |
| Product Name |
dmINGREDIENT | dm009 | Ingredients List |
targetable: ETL_Table
dmPRICE | dmPRODUCTNAME | dmINGREDIENT |
17.22 | Fresh Cook Lasagna | Flour, eggs, beef, milk, tomatoes |
5.99 | Raspberry Cheesecake | Milk, Sugar, Wheat… |
Required output: (modified ETL_Table with renamed fields)
Sales Price | Product Name | Ingredients list |
17.22 | Fresh Cook Lasagna | Flour, eggs, beef, milk, tomatoes |
5.99 | Raspberry Cheesecake | Milk, Sugar, Wheat… |
Required JSON:
{
"method": "MapFieldNamesFromTable",
"description": "EXPORT Mappings",
"sourceTable": "01_DATA_MODEL",
"searchField": "DataModel",
"sourceField": "DataModel",
"targetField": "Export Template",
"targetTable": "ETL_Table",
"makeValidFieldNames": true,
"project": "TestScript5"
}
Example - Option B
The following shows 3 tables:
- Source Table (Mapping Table = 01_DATA_MODEL)
- targetTable before change ( ETL_Table)
- targetTable after change ( ETL_Table)
sourceTable: 01_DATA_MODEL
DataModel | Code | Export Template |
dmPRICE | dm003 | Sales Price |
dmPRODUCTNAME |
| Product Name |
dmINGREDIENT | dm009 | Ingredients List |
targetable: ETL_Table
dmPRICE | dmPRODUCTNAME | dmINGREDIENT |
17.22 | Fresh Cook Lasagna | Flour, eggs, beef, milk, tomatoes |
5.99 | Raspberry Cheesecake | Milk, Sugar, Wheat… |
Required output: (modified ETL_Table with renamed fields)
Sales Price | dmPRODUCTNAME | Ingredients list |
17.22 | Fresh Cook Lasagna | Flour, eggs, beef, milk, tomatoes |
5.99 | Raspberry Cheesecake | Milk, Sugar, Wheat… |
Required JSON:
{
"method": "MapFieldNamesFromTable",
"description": "EXPORT Mappings",
"sourceTable": "01_DATA_MODEL",
"searchField": "Code",
"sourceField": "DataModel",
"targetField": "Export Template",
"targetTable": "ETL_Table",
"makeValidFieldNames": true,
"project": "TestScript5"
}Function Logic
For each X in sourceTable.searchField
Find targetable.sourceField
Rename Y targetTable.sourceField to be sourceTable.targetField
Was this article helpful?