on 09-17-2010 1:30 PM
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
I found the solution...
Thank you All...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
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
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
Anil,
Your second business rule is not clear... please can you explain again.. thanks
~ Shiv
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.