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
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
    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

     

  • Samai's avatar
    Samai
    New Contributor
    8 months ago

    Hi, I tried this in some variations now. First approach was 

    ... with Math Value = 0 (well it should be positive or negative). But then nothing happened. No additional lines were generated. Trying back an forth I finally managed to get the lines in stage with the following setting

    •  Create if < x with MathValue = 1
    • Create if > x with MathValue = -1

    Unfortunately this is overlapping, but also MathValue = 1 for both lines only generated the negative-accounts. Ok, not relevant for this case (Korean Wong) but not reliable for other currencies.

    However, is this behaviour by design or a bug?

    Thanks

    Michael