MDM Validation: This one should be easy but having trouble

[Former Member]

MDM Validation Question: As an example, certain field values will be determined firstly by the u2018Account Groupu2019 field selected and secondly the u2018Country Codeu2019 field selected.

For Account Groups u201CZ001u201D, u201CZ002u201D, and u201CZ004u201D, when Country u201CUSu201D is selected, Company Code 1001 must be selected. If Country u201CCAu201D is selected, then Company Code u201C1101u201D must be selected.

Iu2019ve created the following validations for these circumstances that work flawlessly when each country is selected:

IF (Account Group.Code ="Z001" OR "Z002" OR "Z004", IF (Country.Code="US"AND Company Code Data.Company Code.Code = "1001",TRUE,FALSE))

IF (Account Group.Code ="Z001" OR "Z002" OR "Z004", IF (Country.Code="CA"AND Company Code Data.Company Code.Code = "1101",TRUE,FALSE))

Say I add validation #1 to the u2018USu2019 Validation Group and validation #2 to a u2018CAu2019 Validation Group.

Now, as an example, when I execute the u2018USu2019 validation group for validation #1 it works fine. However, if I execute u2018CAu2019 validation group on #1 I get the error that I didnu2019t enter the Company Code for Canada.

My desire is to create a validation that will obviously not give an error when a country other than that listed in the validation is selected. (Ex. If I have UK, FR, CH, or some other country populated and we execute the u2018USu2019 validation group, we donu2019t want an error to occur because Country Code u2018USu2019 hasnu2019t been selected because obviously that doesnu2019t matter.

Is there a way to account for this in the syntax? Iu2019ve tried to set up scenarios with u2018!u2019, NOT, and other ideas to eliminate the error. Basically I want it to be u201CIf US, then u20181001u2019u201D and, if NOT US, then any company code can be populated.

Any ideas?

Thanks!

Jason

[Former Member]

Hi,

Try this....

IF (Account Group.Code = "Z001" OR Account Group.Code = "Z002" OR Account Group.Code = "Z004", IF (Country.Code="US" AND Company Code Data.Company Code.Code = "1001",TRUE, FALSE))

Similarly change for second

It should work....

Thanks,

Mandeep Saini

[Former Member]

Jason,

Try this......

IF (Account Group.Code ="Z001" OR "Z002" OR "Z004", IF (Country.Code="US", IF(Company Code Data.Company Code.Code = "1001",TRUE,FALSE), IF (Country.Code="CA",IF(Company Code Data.Company Code.Code = "1101",TRUE,FALSE))))

The idea is --> If country Code is US then You check the account group 1001 else check for country code CA, if yes then Check for account group 1101

hope this solves your issue, please feel free to revert to me!

thanks,

Alexander.

[Former Member]

Alexander, You are definately on the right track, but I provided a simplified example of just two countries. In reality we have the comprehensive list of ECC countries. As such, we really need IF US and IF NOT US type conditions in our syntax as we are not able to list every country in the validation.

[Former Member]

" Alexander, You are definately on the right track, but I provided a simplified example of just two countries. In reality we have the comprehensive list of ECC countries. As such, we really need IF US and IF NOT US type conditions in our syntax as we are not able to list every country in the validation. "

Jason,

I got it in the first place that you will be dealing wt comprehensive list of Countries....

here we go my questions are:

1. All the countries except US will be treated same in the validation rules?

2. What are all other rules involved in these validation, like : Account Group anything else???

thanks

Alexander

[Former Member]

Alexander,

Thanks for the continued effort.

1. All the countries except US will be treated same in the validation rules?

Right now, the client only handles North American Customers (Canada and US), so we are creating validations for only these countries. So we are creating two sets of validation rules, one for Canada, and one for US. Basically, these sets will be set up the same way:

In this client, is a 1:1 ratio between Country and that of Company Code, Sales Org, Delivering Plant, House Bank, Currency, and some other fields (i.e. If US, then Company Code "1001", Sales Org "US01", Delivering Plant "USMP", Currency "USD", etc.

Correspondingly, if Country Code "Canada" is selected, the canadian values for these fields are being validated.

Each one has been written separately for each field.

The Account Group, determines which fields we are validating.

For example, Z001-Sold To has all validations, but say Z004--a bill to wouldn't have a Delivering Plant, etc required. As such, common entry fields like Company Code would have both account groups in the validation ("Z001" OR "Z004") but some like Delivering Plant may only have "Z001".

So, in short Company Code would have a valiidation like

If Z001 OR Z004, IF(Country=US, then Company Code=1001)

what I want to add is the syntax for IF Country=NON US, then True, or something of the like, that will allow for other countries not to receive error messages when I run the US validation.

Thanks again for your help,

Jason

[Former Member]

Jason

I totally got ur requirement; pls find the modified expression and explanation:

IF (Account Group.Code ="Z001" OR "Z002" OR "Z004", IF (Country.Code="US", IF(Company Code Data.Company Code.Code = "1001",TRUE,FALSE), IF (Country.Code="CA",IF(Company Code Data.Company Code.Code = "1101",TRUE,FALSE),TRUE)))

Explanation of above expression:

If Account Group Then (If county = US then all conditions )else (If Country = CA then all conditions) else TRUE

Again my expression will give a direction to arrive exact solution; since you cant convey everything and i cant write exactly without looking at the system

thanks

Alexander