Problem using look up values in expression editor of Validation

[Former Member]

HI,

There is a requirement in which I have to use drop-down values in my validation.

Now, the problem is there are around 150 records in my drop down list. So to use so many values in my expression is not feasible.

The drop-down vaues are of five types, viz. (PAN AV, PAN NOT AV, VAT AV, VAT NOT AV, NA). Every validation ends with any of this five type.

Can any one please suggest, if  there is some way to group this values or use some range for these values to complete my validation expression.

Regards,

Rohini.

[Former Member · Accepted solution]

Hi Rohini,

Can you please let me know what is the requirement you want to achieve?

Please let me know the main table records against which you are trying to compare with lookup values?

Also kindly elaborate the structure of your repository a bit, mainly regarding main table fields and look up table etc.

Thank you.

Regards,

Ankush Bhardwaj

[Former Member · Accepted solution]

Hi Ankush,

We have gone for upgrade to 7.1 SP08.

I am using main table field PAN Number with the lookup table values Tax rule.

This look up is inside a tuple.

PAN Number is a main table field with type text.

Now, my actual requirement is "If PAN Number field is null then system should not allow some Tax codes having the tax code description ending with PAN AV na dif PAN Number is not null then system should not allow the user to select the codes ending with PAN NOT AV.

For eg: tax rule descriptions here are like: PAN available, PAN not availlable, VAT available, VAT not available, not applicable(NA).

There are 150 records as I said earlier, with the look up values having three fields(Tax code, Tax description, Tax rule). Tax code comes in drop-down as : WCT: 'Inv Civil Contract  2% CHN, PAN AV' or 'WCT: Adv Other Contract  4% CHN, PAN NOT AV'.

Now there are 150 records in the drop down, so to use them in validaiton is kind of a problem.

So, I was asking that if there is any way to use so many values in validation expression without actually selecting these records in expression editor manually.

I hope its clear now.

Thanks for the help.

Regards,

Rohini.

[Former Member · Accepted solution]

Hi Rohini,

If my understanding is correct then you want to restrict user from entering data in one field(look up field in tuple), based upon the value entered in other field(main table field.

So i suppose that you would be required to do so in Portal.

So i would suggest you to write validation in portal itself rather than MDM and execute when user pushes SAVE button to enter details. If validation fails then user should not be able to save data.

I am saying this because it will be difficult(or not at all possible) to use Tuple fields in expression for validation.

Or another option could be to filter pick-list values itself based upon the fact whether PAN Number is entered or not. So this way user will be able to select only those values which are being displayed to them based upon the status of the main table field.

Kindly let me know if this workaround suits your requirement.

Thanks and Regards,

Ankush

[Former Member · Accepted solution]

Hello Ankush,

Actualy we have upgraded MDM to 7.1 SP08, in which it is posible to use tuple fields with main table in Expression editor.

We are not having portal interface for this repository, so thefirst solution is not possible.

Can you please elaborate the second solution you gave, that is to filter the pick list based on whether the PAN Number is entered or not.

I mean, can you please explain how to achive this.

Regards,

Rohini Joshi.

[Former Member · Accepted solution]

Hi Rohini,

Are you storing tax code descriptions in Tuple field itself?

If yes, then simple if statement would work like:

IF(IS NULL(PAN Number) AND Has any values(RIGHT(Tax.Description,6), "PAN AV"),"False","True")

If you are not storing description and only code is being stored there then you would have to first fetch corresponding description from lookup table and then do comparison on that value. then your expression will be a bit complex like:

IF(IS NULL(PAN Number) AND Has any values(RIGHT(IF(Tax.Code=Tax.Code,Tax.Description),6), "PAN AV"),"False","True")

IF(Tax.Code=Tax.Code,Tax.Description) - It will check the value of tax code in record with all the values of tax code in lookup table and returns tax description where it matches in lookup table record.

What above expression means is that if PAN Number is NULL and For the description of tax code entered currently, if last 6 characters are equal to "PAN AV" then it should return false value.

Similarly you can create expression for other validation for PAN available case.

Kindly check this and you should be able to create expression as explained above.

Please get back to me in case of any doubts.

Thanks and Regards,

Ankush

[Former Member · Accepted solution]

Hi Rohini,

For got to mention about second option of filtering the lookup values as per requirement:

I was telling this option could be implemented in Portal itself. Since you are not using Portal so you won't be able to use that option as well.

Regards,

Ankush

[Former Member · Accepted solution]

Hi Rohini,

Please try this :

If (Is_Null(PAN Number),(Right(Tuple.TaxCode.description,6)<> PAN AV), Right(Tuple.TaxCode.description,10) <> PAN NOT AV))

The solution could be on these lines,this is just to give an idea.Please tweak and test this logic.

Thanks,

Ravi

[Former Member · Accepted solution]

Hi,

I will use one look up field 'Tax rule', which will be having values PAN AV and PAN NOT AV in the look up table and with tax description.

And I will built up the validation using this 'Tax rule' field, this way it will be easy.

Thanks for all your help.

Regards,

Rohini.