Complex Expression in Transformation Rule - How to exit rule?

[Beeswing77]

So we have a UD2 mapping set up in the List section.

What I want is to be able to set up a Complex Expression in the UD2 Composite section so that for certain accounts it does nothing and so moves on to the List section, but for everything else we have a hard coded UD2 (e.g. None).

I have no problem with setting the criteria in the Complex Expression, but I don't know if it is even possible to write some code that forces it to skip on out and look at the List mappings?

I realise this is maybe a bit back to front compared to how the Composite mappings work, but we have a very tight list of accounts where we need to do the List UD2 mapping,  and everything else (which can be any other account) should just go to None.

[Henning]

Hi, whatever is not covered through your Rule Expression in the Composite mapping is then mapped by List, Range or Mask mappings. So as long as your composite filter does not include the account members you wish to map via List or Map, this should work.

[Beeswing77]

The composite filter captures quite a wide range of accounts.  I can't see any way to refine this filter without some kind of 'Not like' function.

I can do way more robust filtering in the Complex Expression,  but by that point I can't find a way to go back out and try the next mapping type (i.e., List)

[Henning]

The Complex Expression will run through all members that are within the filter of your Composite mapping. This means that even if you get a function that says within your complex expression "when account = xyz, then move to list", it does not give you much of a benefit over just mapping this member in the complex expression as well since it is already being checked.

Here is a post on IdeaStream asking to add something along the lines you are asking here for if I understand you correctly. Consider leaving a kudo to support this request if you agree:

JIRA Ticket: PF-21104 (onestreamsoftware.com)

(unless this has been added in the meantime and I missed it)

[FredLucas]

Hi @Beeswing77,

Just to add to what @Henning explains above.

It sounds like you might be looking for a Mask type rule that maps all accounts that have not been mapped through your other mappings to None.

It should look something like this:

[Beeswing77]

We already have that mask in place.  However,  I don't want just any accounts having UD2's applied by the List mappings,  only some.  If I just leave the List mapping in place for all accounts we could potentially get UD2 info on accounts where we don't want it.

[jwagner]

If you have a specified list of accounts you would like to be skipped in the Composite rule, you could use 'Globals.Bypass = True' in your rule, pseudocode below.

Dim accountsToSkip As List(Of String) = {<accounts to skip here>}
Dim sAccount As String = args.Value

If accountsToSkip.Contains(sAccount) Then
     Globals.Bypass = True
Else
	<Composite Rule logic>
End If

[Beeswing77]

That sounds like what I want, but I can't seem to get it to work for me.  This is what I have now:

If args.GetTarget("A#") = "BS11200"
    Globals.Bypass = True
Else
    Return "None"
End If

It just returns 0's in UD2 for account BS11200

[jwagner]

Couldn't you just shift the list logic into the Complex Expression?

[Beeswing77]

Yes, in theory, but there are a lot of line in the List mapping.  I'd also have to combine them with the source accounts, and there are many of those and they don't run in nice neat ranges.  It wouldn't really be sustainable.

[jwagner]

Hmm, I wouldn't have expected the Globals.Bypass to be returning 0. Are you able to send a screenshot of an example of a composite and list rule?

[Beeswing77]

This is the Composite:

That has this Complex Expression:

Here is the List:

If I remove the Complex Expression completely then the List and Mask work fine.

[jwagner]

I'm not sure if that Composite Rule is even picking up account BS11200, try removing the A#[1*] and just using A#[*]

[Beeswing77]

It's definitely picking it up.  This populated the UD2 as Test1, as expected:

[DanielWillis]

I don't think this will work. I think your incoming row will find a match on your complex expression and then stop there, not looking up any further mappings (e.g., on the list tab).

I understand why you wouldn't want to put your list in the code. What you could do is have your complex expression code look up the mapping from the list.

[Henning]

I agree, though I am curiously waiting to see whether there is a way to achieve this, I do not believe there is. If there is nothing returned from the rule, the assigned target value from the Composite rule should be returned.

If you do not want to put your list mapping into the Composite complex expression (which is a valid hesitation!), one could also use lookup tables to refer to and store the list mapping used in the complex expression (and keep the lookup table in a global variable).

List mappings with a complex expression should work as well as per @DanielWillis suggestion. 

If your only purpose to apply those two case mappings to your BS accounts, Mask mapping with a complex expression or business rule might be another way to go about it. Up to you to determine which solution suits the requirements best.

[DanielWillis]

Yes, definitely also use a global variable rather than looking up the list each time.

[Beeswing77]

I like the idea of having the complex expression code look up the mapping from the List.  Can that be done using the existing List,  and if so, how? Or would I have to move it all into a Lookup?

Thanks!

[DanielWillis]

Yes it can be done from the list. If you use a lookup table there are built-in functions you can use rather than a database query although I think a database query dumping a datatable / dictionary of the list mappings or lookup table into memory would probably be better.

You can find some SQL to get you started in https://community.onestreamsoftware.com/t5/Workflow-and-Data-Integration/Transformation-rules-mappin...

[DanielWillis]

. (replied to wrong comment)