The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.

Forum Discussion

mariavalenv19's avatar
mariavalenv19
New Contributor II
3 years ago
Solved

VLookUP Table

Hi,  I'm trying to look up values from a VLookUp table (previously made in the Transformation Rules) within a Complex Rule in my Data Source, MMomentoff how could I this? Thank you very much in adv...
Methodology
  • KarlT's avatar
    KarlT
    3 years ago

    Hi,

    You can use the function:

    BRApi.Utilities.TransformText(si, "LookupItem", "LookupGroupName", Boolean)

    where "lookupitem" is the item you wish to look up and the "lookupgroupname" is the name of the name of the lookup transformation group. the boolean determines how to handle in the event the lookup item is not found. (see the intellisense information in OS)

    If you will need to look up the same item repeatedly, you may want to consider using the global objects to store the results of previous lookups to avoid repeated calls of the BRAPI.

    Regards,

KarlT's avatar
KarlT
Icon for OneStream Employee rankOneStream Employee
3 years ago

Hi,

You can use the function:

BRApi.Utilities.TransformText(si, "LookupItem", "LookupGroupName", Boolean)

where "lookupitem" is the item you wish to look up and the "lookupgroupname" is the name of the name of the lookup transformation group. the boolean determines how to handle in the event the lookup item is not found. (see the intellisense information in OS)

If you will need to look up the same item repeatedly, you may want to consider using the global objects to store the results of previous lookups to avoid repeated calls of the BRAPI.

Regards,

  • mariavalenv19's avatar
    mariavalenv19
    New Contributor II
    3 years ago

    Hi Karl,

    This worked perfectly, now we're trying to figure out how to make an error message pop when there is no value in the table. Thank you very much! 

    Best Regards,

    Nieves

    • KarlT's avatar
      KarlT
      Icon for OneStream Employee rankOneStream Employee
      3 years ago

      Hi Nieves, Rather than throw an error I would try an "import>Workspace" type workflow so you can review the "untransformed" items after import and maybe prevent workspace completion if this list isn't empty.

    • BernardoN's avatar
      BernardoN
      New Contributor II
      3 years ago

      Hi, are you using the TransformText on a Transformation Rule Group ? I tried to get it to work but it always returns the same Item I'm using as a second parameter (after SI), isn't this supposed to return the Target on the table instead of the source ?

      Dim s_TargetAccount As String = args.GetTarget("A#")
      Dim s_D365TF As String = "Dynamics_MainUD2"
      Dim s_TargetProduct As String = ""
      brapi.ErrorLog.LogMessage(si, "s_TargetAccount: " + s_TargetAccount)

      s_TargetProduct = BRApi.Utilities.TransformText(si, s_TargetAccount, s_D365TF, True)

      brapi.ErrorLog.LogMessage(si, "s_TargetProduct: " + s_TargetProduct)

      Maybe I'm doing something wrong :S

       

      • KarlT's avatar
        KarlT
        Icon for OneStream Employee rankOneStream Employee
        3 years ago

        Hi, with the "true" setting in the function, i believe it will return the source if it cannot find a mapping for it.

        Have you logged the source account to ensure that it is correct and checked that a mapping exists in the transformation group for it?