Bummer on the ordering. Thanks for the quick response. I had considered your idea but the end users can update for their new department mapping during the import and I am worried they will just add the 1:1 instead of the Expense+Department but let me give it a try. Question for you what is the syntax to have the source value in the composite mapping ie A#[7*]:?? where the ?? is the source value for department? I only figured out how to include the target value in the syntax with the A#[7*].
I'll assume your 7x ranged accounts are the expense ones. You generally have it correct in your original post. The format would be:
Rule Expression: A#7*:U3#YourSourceSystemValue
Target: select one of the UD3s per your dimension hierarchy
The YourSourceSystemValue can use the pattern matching (eg. 6*) for many-to-one mappings or you can do one-to-ones depending on your needs.
Edit:
Are the 7x accounts only used with your departments in your source system? If so, the 1:1 mapping is a viable option. You only need the composite if your source system does not adhere to those rules (a common problem we have as well). Just wanted to double check.
OK then I guess I need to ask the question the other way. How can I indicate the target account starts with 7 or can I not do this? When I gave the example of A#[7*]: the 7 was the OS target account not the source account.
The OS 7x accounts should be the only one with departments but the source account numbers are not consistent. So I am trying to figure out how to make all records that have a target account <> 7* to equal 'None' and then all records that have a target account that is 7* to do the 1:1 mapping.
I'm not sure if there is a way to do a -to-many style mapping. Everything we've done is a -to-one mapping. Hopefully someone else from the community can jump in and provide suggestions.
That said, typically there is some pattern that can be followed from the source system.
I suggest for the Account <> 7* use the Mask type * to * and then use a conditional BR something like below for the UD3 transformation.
Dim account As String = args.GetTarget("A#")
Dim u3 As String = args.GetSource("U3#")
If Not account.StartsWith("7")
Return "None"
Else
Return u3
End If