MDM validation

[Former Member]

Dear MDM specialist

I need some help to create validations for our new development for bank repository.

I am on the way to create a new repository for banks, I created the main table and I also created so called controlling table called u201CCountriesu201D.

In that countries table I have some example data:

Bank country codeu2026. US...PL...DE...SE...SE..........................NL

Bank number length u20269..... 6......4......3......4....(no value for that country)

Comment

I need to create validations in the main table which will assure that, if user would like to add the new bank for example for the country US the validation need to check that for US bank number length is 9 digits and allowed to save the new entry only if user will type bank number with length 9 for example: 123456789 if user will type only 1234567 or 12343567891011 then validation need to inform about mistake.

That same need to be validated for example for country PL. If user would like to add the bank with bank number length 123456 is ok but is not ok for 123 (as the bank number length must be 6 digits). For some countries we have 2 values possible like for SE bunk number 123 and 1234 are OK. And the last option some countries have no values as for example NL.

I tried with Len(), but I cannot make it work, can you please guide with validation code ??

Thank you very much for help.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

So as per the query u have Country table with fields Bank Country code and Bank Number Length.

In main table you have field Bank Number and Country

Now your validation expression should go like this:

Length(Bank Number) = Country.Bank Number Length OR IF(Country.Bank Country code = Country[NL],TRUE,FALSE)

Regards,

Neethu Joy

Edited by: Neethu joy on Feb 8, 2012 12:38 PM

[Former Member · Accepted solution]

Dear Neethu joy

Thank you very much for your replay. It is working perfect. I just tried and that code is enough:

Length(Bank Number) = Country.Bank Number Length

it is because in Country table I do have an entry:

for example:

coutry code:..................NL

bank number length:....(empty space)

On that way all works fine.

There is only one problem I can see. For country with two values:

exapmle:

coutry code:.....................SE....SE

bank number length:......3.......4

In that case if I want validation to works correctly during creating of the bank in the main table, I need to choose correct value for country code for example, if I choose first SE and my bank number is 1234 I will have validation error, but if I will choose other one then there is no validation error.

Do you know any smart solution for that? I am a bit worry that we can have problem when we start uploading data by using Import Manager.

Thank you for your help.

BR

Rafal

[Former Member · Accepted solution]

Hi,

If you are saying that you have two valid bank number lengths(4 and 3) for the same country SE then you can use the below expression to achieve the results.

country[record]=country[SE] and len(bank details. bank account number)=4 or country[record]=country[SE] and len(bank details. bank account number)=3

I have used direct values, you can use the Bank number length field,

Assuming that you are maintaining 2 Bank number fields in Countries table.

Regards,

Prasad.

Edited by: Prasad Tuttagunta on Feb 9, 2012 4:39 AM

[Former Member · Accepted solution]

Dear Prasad

I am trying to make it works, but I have some problems on the way. I tried your expression but it was not working fully so I modified it a bit.

I connected both expressions (from post above) and a bit modified yours, here is the result:

len(bank number)= bank country.bank number length

and

if(bank country.country=SE and len(bank number)=4, false,true)

ps.

I will test it first and lated will add value for sweeden for 3 digits.

Expression works perfect for all countries except Sweeden, Every time I choose SE then I have validation error. I am not sure why.

In the Country table I have SE and value 4.

Can you please take a look on that and help, solution is almost working

Thank you.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Can you mention the fields in your country table. Below are the fields i have in my Country table

1. Name (DF)

2. ISO Code

3. Bank Number length

As i have only one "Bank Number length" Field i can maintain only one value(you can have multiple values if you have multiple fields eg, bank number length1, bank number length2... and so on) say i have maintained a value "5" in bank number length field for country "sweden"

As in my main table record in the Field country i can see the country names (since its my Display field) hence ill write the below expression to ensure that the bank account number length is exactly 5 if my country is Sweden.

Country (record) = country (Sweden) and Len(bank details.Bank account number)= Country.bank numberr length

If you want to have multiple valid bank account number length's then you can directly write the expression as below(here you dont need to have any "bank number length" field in Countries table.

Country (record) = country (Sweden) and Len(bank details.Bank account number)= "4" OR Country (record) = country (Sweden) and Len(bank details.Bank account number)= "5"

If you are using a Bank details table of type Qualified flat multivalued and you are trying to ensure that the Country value(No Qualifier) in Bank details table and the corrosponding Bank account number length (Yes qualifier)are as per the business rules then you have to mention the Country lookup field in the bank details table as below.

Create a Validation Group "XYZ"

write two exp as below

Exp 1

if(bank details. Country(record)=country(sweden),true,false)

Exp2

Len(bank details.bank account number)=4

Group the above two validations with "XYZ group"

Run the validation.

Hope this helps

Regards,

Prasad.

Edited by: Prasad Tuttagunta on Feb 9, 2012 9:46 AM

[Former Member · Accepted solution]

Hi Prasad

Until now everything seems to be working fine.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

You are right creating duplicate entries is not a good way to maintain Lookup record.

I have a sugesstion for Country Lookup table create fields:

1. Country Code text field

2.Bank Country length: Lookup[Flat](multivalued)

Validation in this case would go like:

Has AnyValue(Country.Bank Country Length(Record),Length(Bank Number) OR (the if statement for NL values)

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Neethu Joy

I think that is very nice solution, actually I have been wondering about flat multivalued. In that case it will be easier for user maintain data on the Portal.

I will try your solution probably on Monday.

Thank you for help.

ps.

I can not give you 6 points for help, as that option is gray out, so I can only give 2

BR

Rafal

[Former Member · Accepted solution]

Hej Neethu Joy

I just created a new table: Bank Country Length as a text table in repository, then I added values 1,2,3,4...10

Then I added that table to Countries table as a multivalued.

Unthil here all works really nice.

I only have problem with validation, that one suggested by you is not really working, I tried on few countries.

Do you have any other idea for validation?

Thank you in advance for help.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

The validation I suggested is for a different structure .

My bad as i was not clear in making you understand.So the structure goe like:

Main table fields:

Bank Account Number:(Text or Integer)

Bank Country:(Single Valued Lookup Field referring to table Countries)

Countries Lookup Table fields:

Bank Country :(Text)

Bank Country length :(Multi valued Lookup field referring to table Lengths)

Lengths Lookup Table Fields:

Length:(Text).

The Validation which I suggested will work for the above structure .Let me know the structure which you are using so will suggest accordingly.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hi Rafal,

Are you writing the validations for Qualified Flat multi valued table "Bank details"?

Regards,

Prasad

[Former Member · Accepted solution]

Hej Neethu Joy

I think we are thinking about similar solution, here is what I have:

Lookups table:

- Bank Country Length (with values 1,2,3,4,5...10)

Country Flat table:

with values:

- Country (values US,DK,PL...)

- Bank Country Length (look up to Bank Country Length - mentioned above) - multivalued

Main table:

- Bank Country (lookup to Country Flata table - mentioned above)

- Bank Name

- Bank Adress

- Bank Number

The idea is at for some countries bank number length is 4 digits fx. 1234, but some countries as Sweeden can have 2 values possible 3 and 4 digits fx. 123 and 1234 - both OK. And last group of banks is not defined how long bank number must be.

I am using only flat tables and lookup flat.

Thank you in advance ))

BR

Rafal

[Former Member · Accepted solution]

Hej Prasad

I do not have any quelifield tables, I am only using flat tables, and one flat multivalued. I mentioned the structure above.

BR

Rafal

[Former Member · Accepted solution]

Hello Rafal,

In the Table "Bank Country length" Make the Field type as Integer for "Bank Length" field and in the countries table assign the values for the Multi valued field(Say 3 and 4 for Country Sweden) then use the below expression

Has_Any_Values(Country.bank country length.Bank length,Len(Bank Number))

Regards,

Prasad

Edited by: Prasad Tuttagunta on Feb 14, 2012 5:13 AM

Edited by: Prasad Tuttagunta on Feb 14, 2012 5:15 AM

[Former Member · Accepted solution]

Hi Rafal,

Make the following changes in the Data Model and Validation:

1. Make the Type of field in Bank Country Length Lookup table as Integer as suggested by Prasad.

So now you have Bank Country Length Multivalud lookup table with field Bank Length of type Integer.

2.Make the following changes in the validation:

Has_Any_Values(Mid(Country.bank country length.Bank length,3),Len(Bank Number))

It should work for all cases.

Regards,

Neethu Joy

Edited by: Neethu joy on Feb 14, 2012 8:10 AM

[Former Member · Accepted solution]

Hi Prasad

I just did according your suggestion (actually I did so before buf I thought I am doing something wrong, as I got some validations errors).

I defined all tables as you mentioned, then I added values, fx.

country....bank number length

DK..................4

US..................0

NL..................(not defined)

FI....................7

Here is the simple list of errors where validation is not working as expected:

1) no metter which country I choose (except NL) if I put single value fx. 1 then validation do not show error. Fx. for country DK it accept value 1 but of courseit is not correct, as it should be for example: 1234 or 5678

2) for not deffined countries as fx. NL I always have validation error.

Do you have any idea how to improve that small problems ?

Many many thanks

BR

Rafal

[Former Member · Accepted solution]

Hej Neethu Joy

Hm it can work , one question function MID need 3 values in that sence: MID(,,)

Can you help me defined all values I should puut there:

MID(Country.bank country length.Bank length,,)

Thank you ))

BR

Rafal

[Former Member · Accepted solution]

Hej Neethu Joy

I think it works for most of the countries, except for countries with not deffined value example:

for country NL bank number is not deffined and validation should accept all values.

Can you make it also in a smart way?

If so it will be esy for users to maintain countries table.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Lets take an eg you have values in Country table as:

Country.....................Bank Country Length

DK..............................4

US..............................6

IN----

---

9;10

NL----

---

0

Now the field Country.BankcountryLength.BankLength will be represented in MDM as 1:4 for DK,1:6 for US and 2:9;10 for IN

where value before ":" indicates the total number of values in a multivalues field

That is why earlier the validation was not working due to the representation

But now Using MID function we are considering values after ":" i.e. from 3rd character.

For NL use the if statement with OR logic given earlier which will not match length it will simply assign TRUE if country is NL.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Neethu Joy

Thank you very much for all that help. Actually if only possible I would like to have a validation, where in the future we do not need to add more exceptions per country. I think way around is simply for not defined country add all possibles values, then your validation will accept it

Until now all works really perfect.

Briliant idea, thank you and Prasad for great help.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Below are the steps

1. created a Flat table "Bank country length" which has one field "Bank length" of type integer

2. In Countries table Created a field of type "Lookup flat multi valued" Multivalued option as Yes and selected the lookup Table as Bank country length

3. Now in data manager i created the values 4, 5, 6, 7 etc for table "Bank country length

4. Then in countries table i selected the "bank country length" field for different countries (For Some countries multiple values and for some countries single)

5. then an expression as has any valuse(country.bank country length.Bank length, len(bank number))

In my case the multi valued field " Bank country length" in countries table is represented with a ";" in between values (Eg: for country sweden i have two valid lengths 4;6)

it works fine for all the records, if i do not choose a value for a country(say i leave the "Bank country Lenght" field Blank then my record will throw an error in case i run the validation, so in that case u can use an OR IF clause as if bank country length is null then true.

Regards,

Prasad.

Edited by: Prasad Tuttagunta on Feb 14, 2012 9:15 AM

[Former Member · Accepted solution]

Hi Prasad

Thank you for info I used that one above with MID, and seems to be working fine. For not defined values I am adding all possible values to the country. It is not soo many max 15. so that is OK, and users will be able to maintain that from the portal. So perfect

Right now I am working on similar solution to so called SWIFT code. I created similar to bank number length tables and multiple values as also suggested by you above (just change to swift insteed of bank length).

I have similar validation for that as for bank length:

has any values(mid(bank country swift.value,3), len(bank number))

It works fine, except that it also accept single value for exampe: 1 or 2 or 4

I have defined:

country....swift

DK..............11

IN...............11,0

US..............11

so normally, it should only pop up with an error when swift code is shorter than 11, and for US shorter than 11 or empty.

But with unknown reason it also accept single value fx. 1

Can we change a bit validation that will not accept single value unless defined?

I of course can add to validation "and len(swift code) <>1" but that is rather dirty trck, and can give problems in the future.

Do you have any ideas how to solve that small problem ???

Thank you in advance.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Try this for Countries which donot have any length specified for Bank Account Number.

For such Countries in Lookup table you can maintain Value 0 Bank Length.

Now try this validation

Has_Any_Value(................) OR IF(Mid(Country.bank country length.Bank length,3,1)=0,TRUE,FALSE)

You dont have to write this OR logic 15 times its not a good approach.

For Swift code I guess Direct validation is better if for all countries code should not be less than 11 except for US.

Length(Swift Code)=11 OR if(Country[Record] = Country(US),Length(swiftCode)=11 or Is_NULL(Swift Code),False)

Regards,

Neethu Joy

Edited by: Neethu joy on Feb 14, 2012 11:13 AM

[Former Member · Accepted solution]

Hi Rafal,

Ill give the expression for the same Bank length field we created you can use the same for the swift code

If(is not null(country. bank country length.bank length), has any values (country. bank country length. bank length,len(bank number)),is null (bank number))

Hope this helps

Regards,

Prasad.

[Former Member · Accepted solution]

Hej Neethu Joy

I just tied to work with recommended by you validations, howerev I can not make them works.

I tried to have in table Countries values

DK.......4

NL........(empty - no values defined)

PL......0,1,2,3,4...15

I hoped that validation:

Has_Any_Value(................) OR IF(Mid(Country.bank country length.Bank length,3,1)=0,TRUE,FALSE)

will do the trick so for all countries as NL all values will be accespted, but I got an error every single time. but I think all was workign fine for PL with defined values.

I also can not make that one works:

or Swift code I guess Direct validation is better if for all countries code should not be less than 11 except for US.

Length(Swift Code)=11 OR if(CountryRecord = Country(US),Length(swiftCode)=11 or Is_NULL(Swift Code),False)

But it was not accepting empty values, I tried many options.

Can you take a one more look on that ?

Eaven if will not be possible to make first valitation works is also ok to use values as for defined country PL. But it will be very nice to make SWIFT working.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Can you elaborate more on Swift field i.e whcih table it belongs and Its type.

What kind of data it will hold .

Regarding the first validation I created it taking in account that countries like NL can be represented by 0 i.e If a country has 0 it is not resticted by any length.

But now can you elaborate more like country PL it has 0,1,2,3....15 what does this mean?

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

The first validation works fine, tested that with our business partners and it is really OK.

After a second thought your SWIFT validation code is really nice and of course works so I implemended it

I have one more think I need to do in that repository. Hope that one is the last one ))))

In my controling table "Countries" I have values:

Country............bank_length................................bank_key_definition

DK...........................4.............................................Bank_Number

LV.......................(all possible values)....................Swift_Code

values in bank_key_definition are bolean (Bank_Number as true and Swift_code as false)

Now when I create a new bank in main Bank table I should have an assignment which will copy Swift code to bank number.

Copy can be of course done in workflow, but I have a problem with creating correct assignment.

Can you help me with that?

Thank you.

BR

Rafal

ps. hope that is the last to do :=)))))

[Former Member · Accepted solution]

Hi Rafal,

If Swift code is a main table field and bank number too, then for copying simply select the field Bank Number in the expression editor .

But if there is any restrictions or conditions to it let me know.

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

I actually created that assignment:

IF(Bank Country.Bank Key Definition=FALSE,SWIFT Code)

I added it to workflow and it works fine for all countries defined as Swift_code in controling Countries table.

However there is a problem for all countries defined as Bank_number in controling Countries table.For all that countries my workflow is hanging. Actually system behaviour correct, so I think I should have some validation. In that case in the workflow first step will be validation if validation will fail then records will not be changed. If validation result is true then assignments will work.

Do you know how to create such validation? in sence if Bank Country.Bank Key Definition=FALSE then run assignment, else do nothing.

BR

Rafal

[Former Member · Accepted solution]

Hi Rafal,

Use If then else function so ur expression shud go like:

IF(Bank Country.Bank Key Definition=FALSE,SWIFT Code,Bank Number)

Before using in workflow test ur assignment manualy with all different cases .

Regards,

Neethu Joy

[Former Member · Accepted solution]

Hej Joy

Genius.

I did not know I should add Bank Number as the last value.

Works fine )

BR

Rafal