cancel
Showing results for 
Search instead for 
Did you mean: 

Validation expression help

Former Member
0 Kudos

Hi All,

I have requirement like having two fields

Status(Lookup) and Date (Literal Date)

Business rules as follows

1) If "Status" = "Draft" then Date value should be equals to 09/09/9999

2) If "Status" <> "Draft" then Date value should not be equals to 09/09/9999

I tried with validation Expression Has_Any_Values, but no use.

Can any one please help me on this....

Thanks

Anil

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

I found the solution...

Thank you All...

former_member208981
Contributor
0 Kudos

Hi Anil,

You can use the below expression in your Assignment:

IF(HAS_ALL_VALUES(Status.[RECORD],Draft),"09/09/9999",''enter the second date here")

Assignment Field = Date Field

Note: Validation will not assign a value to the field. need to use Assignment for it

Get the value Draft from the option "Lookups" in the expression box.

Thanks,

Priti

Former Member
0 Kudos

Hi Priti Thanks for your reply...

I don't want to assign date in Date field.'

Validation requirement are

1. If i select Status = "Draft" then i can select date only 09/09/9999. It should give an error message if i select any other date except 09/09/9999

2. If i select status other than "Draft" Ex: if i select Status "Active". Date field should not allow to select 09/09/9999.

Shiv - I think my explanation is clear now.

I wrote validation for 1st scenario

IF(HAS_ANY_VALUE(Status.Name="Draft",TRUE), HAS_ANY_VALUE(Date="09/09/9999",TRUE))

But its not working as required.

What i came to know is if Date field Data Type is Text is working fine. But in my data model we declared Date field Data type as Literal Date. so it's not working and now i cannot change data type here we have lots of data in it.

can any one have sugessions to implement these validations please welcome.

Thanks,

Anil

Former Member
0 Kudos

Hi Anil,

Try below expression and let me know whether it is working fine or not.

IF( Status ="Draft" ,Date ="9999-09-09",Date not equal to "9999-09-09")

Please put not equal symbol in place of not equal to.

Validation should be automatic as per your requirement.

Thanks,

Narendra

Former Member
0 Kudos

Hi Anil,

Try this below expression

IF(Status.Name="Draft" , LEFT(Date, 4)= "9999" AND MID(Date,6,2)="09" AND MID(Date,9,2)="09" , IF(Status.Name not equal to "Draft", MID(Date,9,2) not equal to "09"))

Logic:It will check if Status=Draft then date should be only 09/09/9999 and if Status is not equal to "Draft" e.g. "Active", it can have any date except 09/09/9999. This expression is working fine at my end.

Note: I am not able to write not equal to operator here , so please select it from Operators tab of Expression correctly.

Kindly mark thread as answered if this solves your query.

Regards,

Mandeep Saini

Edited by: Mandeep Singh Saini on Sep 17, 2010 10:29 PM

Former Member
0 Kudos

Hi Mandeep,

IF(Status.Name not equal to "Draft", MID(Date,9,2) not equal to "09"))

In the above expression, you are checking only date. why not year and month? As you are checking only date, it will give false

result for dates contains "09" .What if date contains "2010-09-09". For this also it gives false result which is not supposed to be when status is not equal to draft. I think as per anil requirement, when status is not draft date should be other than "9999-09-09" .so total "9999-09-09" need to be checked instead of only 09.

Thanks,

Narendra

Former Member
0 Kudos

Hi Narendra,

I agree with you, Expression should be

IF(Status.Name="Draft" , LEFT(Date, 4)= "9999" AND MID(Date,6,2)="09" AND MID(Date,9,2)="09" , IF(Status.Name not equal to "Draft", LEFT(Date, 4) not equal to "9999" AND MID(Date,6,2) not equal to "09" AND MID(Date,9,2) not equal to "09"))

@ Anil, Please try and revert if you find any issue.

Regards,

Mandeep Saini

Former Member
0 Kudos

Hi Mandeep,

Thanks for you reply.. It is very helpful

I tried with the expression as you suggested, It is working for following condition IF(Status.Name="Draft" , LEFT(Date, 4)= "9999" AND MID(Date,6,2)="09" AND MID(Date,9,2)="09" .

If i use condition IF(Status.Name Not equals to "Draft", LEFT(Date, 4) Not equals to "9999" AND MID(Date,6,2) Not equals to "09" AND MID(Date,9,2) Not equals to "09"),

I'm not able to select date in Date field. it's giving error message if i select any date value.

any other suggestion...

Regards,

Anil

Edited by: Anil Kumar Pokala on Sep 20, 2010 8:51 AM

Former Member
0 Kudos

Hi Anil,

IF(Status.Name Not equals to "Draft", LEFT(Date, 4) Not equals to "9999" AND MID(Date,6,2) Not equals to "09" AND MID(Date,9,2) Not equals to "09"),

In the above condition use OR operator instead of AND operator in between conditions.

Thanks,

Narendra

former_member205403
Active Contributor
0 Kudos

Anil,

Your second business rule is not clear... please can you explain again.. thanks

~ Shiv