Locations
In FDMEE, a Location is a level where a data load is executed. In other words, users define locations to specify where to load the data and each location is assigned an Import Format, Data Load Rules and Data Load Mappings. As discussed below, users must create Import Formats before Locations are created. The same Import Format can be used for numerous Locations. To add a Location:
- Click Add
- Assign a name
- Assign the corresponding Import Format
- Assign the Target Application
- Assign the Source System type: File
- Assign the Functional Currency: Location currency
- Data Value: <Entity Currency>
- Parent Location: To share mappings between two locations, assign a Parent Location
- Logic Account Group: [NONE]
- Check Entity Group
Note: When adding a new location, a Check Entity Group must be created.
- Check Rule Group
- Click Save
Import Formats
As mentioned above, each Location must be assigned an Import Format. The Import Format defines the structure of the source file and is executed during the source file import process. File based imports are supported for companies who do not have direct connections to their source data, but have data available from their source in a text file. A corresponding Import Format must be created before creating a Location. As shown in the image below, to add an Import Format:
- Click Add
- Assign a name
- Source System: File
- File Type:
-Fixed
-Delimited: If delimited, specify the type of delimiter (comma, pipe, semicolon, etc)
- Specify the Target Application
- Add a description if needed
- Click Save
Defining an Import Format
The image below displays an example of an Import Format with a comma delimited structure.
-The Source Column should replicate what is shown under the Target column
Note: Multiple source columns of the same dimension is acceptable. For instance, there can be three Account source columns.
-The Field Number signifies the column in the source file
-When using a comma delimited structure, the following rows can be added to define the Import Format under Mappings:
-Currency Row
-Attribute Row
-Description Row
-Dimension Row: Select the arrow to view all of the dimensions
-When using a fixed structure, the following rows can be added to define the Import Format under Mappings:
-Skip Row: This option is used to indicate rows in that file should be skipped
-Currency Row
-Attribute Row
-Description Row
-Dimension Row: Select the arrow to view all of the dimensions
-Import expressions operate on the value that is read from the source file enabling FDMEE to read and parse information in the data file. Expressions include:
-Script: Click on the pencil icon to add a script
-Sign=,-: Used to manage nonstandard numeric sign conventions, such as negative numbers in a source file
-Fill=EurotoUS: Used with the Amount field to trigger a number format conversion from ., to ,.
-Fill000: Used to fill fields with trailing characters such as zeros
For example:
Account number: 203
Expression: Fill=00000
Post Import: 20300
Script Editor
The Script Editor in FDMEE uses scripts written in Jython to assist files during import processes. FDMEE supports three types of scripts: Event, Import and Custom scripts.
Event scripts are executed when a particular event runs. For instance, to execute a script before any import, choose the BefImport event script.
1. Navigate to Script Editor (Setup) under Scripts.
2. Specify the Target Application and Technology- Jython.
3. Select Event > BefImport.
4. Select New
5. Create or edit a script
6. Click Save
This following script has been applied to remove apostrophes from data files.
import os,re
if fdmContext[“LOCNAME”] == “Location Name “:
filename = fdmContext[“FILENAME”]
inbox = fdmContext[“INBOXDIR”]
fdmAPI.logInfo(filename)
fdmAPI.logInfo(inbox)
file = inbox + ‘\’ + filename
file_out = inbox + ‘\’ + filename + ‘.out’
fdmAPI.logInfo(file)
fin = open(file,’r’)
fout = open(file_out,’w’)
for line in fin:
line = line.replace(“‘”, “”)
fout.write(line)
fin.close()
fout.close()
os.remove(file)
os.rename(file_out,file)
Import scripts are created when Import Formats are defined.
1. Repeat steps 1 and 2 above
2. Select Import
3. Select New
4. Create or edit a script
5. Click Save
The following script has been applied to successfully load Plan-Periodic files.
def CheckPeriodic(strfield, strrecord):
iFind = strrecord.find(‘Periodic’)
if iFind > 0:
sValue=’Periodic’
else:
sValue=’YTD’
return sValue
To apply a script to an Import Format:
- Navigate to Import Format under Integration Setup
- Select the Import Format
- Under Mappings, select the pencil icon
- In the Add Expression window,
- Expression Type: Script
- Expression Value: Choose the script
- Select OK