This content has been marked as final. Show 2 replies
This is relatively easy to achieve with a single import script associated with the Account source field (assuming AverageRate and EndRate are accounts in your application) in your import format.
Essentially your first amount say AverageRate would be set as the default field for Amount and these values would be loaded as if it were a single value file. For the second value, EndRate you would have to insert the second value directly into the FDM work table which is the temporary table populated when data is imported from a file during the import process. The example code snippet below suld gve you guidance on how this is done
'Get name of temp import work table
strWorkTableName = RES.PstrWorkTable
'Create temp table trial balance recordset
Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)
If IsNumeric(EndRateFieldValue Ref Goes Here) Then
If EndRateFieldValue Ref Goes Here <> 0 Then
' Create a new record, and supply it with its field values
rsAppend.Fields("DataView") = "YTD"
rsAppend.Fields("PartitionKey") = RES.PlngLocKey
rsAppend.Fields("CatKey") = RES.PlngCatKey
rsAppend.Fields("PeriodKey") = RES.PdtePerKey
rsAppend.Fields("CalcAcctType") = 9
rsAppend.Fields("Account") = "EndRate"
rsAppend.Fields("Amount") = EndRateFieldValue Ref
rsAppend.Fields("Entity")=DW.Utilities.fParseString(strRecord, 16, 1, ",")
rsAppend.Fields("UD1") = DW.Utilities.fParseString(strRecord, 16, 2, ",")
rsAppend.Fields("UD2") = DW.Utilities.fParseString(strRecord, 16, 3, ",")
rsAppend.Fields("UD3") = DW.Utilities.fParseString(strRecord, 16, 16, ",")
'Append the record to the collection
In addition the return value of this Import Script should be "AverageRate" i.e. name of ht eaccount associated with the first value field. The NZP expression also needs to be put on the Amount field in the import format to ensure that the EndRate Field value is always processed even if the value of AverageRate is zero.
The only issue I have with your suggested solution is that it would be slow if there are a lot of records.
My thought would be to 'map' the second Rate to an unused UD/attribute field.
Then after all the rows are processed execute a T-SQL script to clone all the records while performing an update on the cloned records to pull in the new amount and change the account name.
Would be much faster for large operations.