Forum Discussion

mpavan's avatar
mpavan
New Contributor II
2 years ago

Derive account based on Data Value in Transformation Rules.

Hello Experts,    We have a requirement to derive account based on Data value. (If data value is Positive Asset Account , if negative Liability account) This has to be done for range of accounts. ...
  • LeeB's avatar
    LeeB
    2 years ago

    One commonly used technique for value-based mapping is to bypass the original Account and create 2 Derivative Rules that prefix/append DR or CR depending on the value (use the Create If option).  You can then map the Accounts created by the Derivative Rule accordingly.

  • mpavan's avatar
    mpavan
    2 years ago

    Hello Lee, 

    Sorry for the delay.

    I had started to work on it but unfortunately account hierarchy is little messy and we do not have a defined range. Due to missing range Derivate rules are becoming complex. We went with the Event Handler rule that runs after transformation,

    For Each row As DataRow In objTransformer.DataCache.CurrentPage.PageDataTable.Rows
    Dim targetAccount As String = row(accountColIndex + StageConstants.TransformationColumnIncrements.Target)
    If targetAccount.Length > 0 Then
    Select Case targetAccount
    'Check for credit based accounts
    Case Is = "Trade Account"
    'We are on a credit account, flip the sign
    row.BeginEdit
    row(stageConstants.MasterKeyNames.FlipSign) = "True"
    Row.EndEdit
    End Select

    Thanks,

    Manoj

     

LeeB's avatar
LeeB
Contributor II

One commonly used technique for value-based mapping is to bypass the original Account and create 2 Derivative Rules that prefix/append DR or CR depending on the value (use the Create If option).  You can then map the Accounts created by the Derivative Rule accordingly.

mpavan's avatar
mpavan
New Contributor II
2 years ago

Hello Lee, 

Sorry for the delay.

I had started to work on it but unfortunately account hierarchy is little messy and we do not have a defined range. Due to missing range Derivate rules are becoming complex. We went with the Event Handler rule that runs after transformation,

For Each row As DataRow In objTransformer.DataCache.CurrentPage.PageDataTable.Rows
Dim targetAccount As String = row(accountColIndex + StageConstants.TransformationColumnIncrements.Target)
If targetAccount.Length > 0 Then
Select Case targetAccount
'Check for credit based accounts
Case Is = "Trade Account"
'We are on a credit account, flip the sign
row.BeginEdit
row(stageConstants.MasterKeyNames.FlipSign) = "True"
Row.EndEdit
End Select

Thanks,

Manoj