function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
karen_pandakaren_panda 

Validation Rule using VLOOKUP

Hello,

 

I have a custom object assignment__c with the following fields:
Account__c
Province__c
City__c
SalesTeam__c
province_with_city
province_without_city

 

The following cases need to be validated:

1. if Account1 ON Toronto SalesTeam1 exists, Account1 ON SalesTeam2 is not allowed to save. (if a record with city value exists, new record with the same province without city is not allowed.)

2. if Account1 ON SalesTeam1 exists, Account1 ON Toronto SalesTeam2 is not allowed to save.(if a record without city value exists, new record with the same province with city is not allowed.)

3. if Account1 ON SalesTeam1 exists, Account1 ON SalesTeam2 is not allowed to save.(if a record without city value exists, new record with the same province without city is not allowed.)

 

I have following workflow actions - Field Update to populate province_with_city and province_without_city when a record is created or updated.
- Populate Province With City
Formula: IF(LEN( City__c) > 0, Account__c & " " & Province__c , "")

- Populate Province Without City
Formula: IF(LEN( City__c) = 0, Account__c & " " & Province__c , "")

 

Then I add a validation rule to check province:
OR(AND(LEN( City__c ) > 0, VLOOKUP(
$ObjectType.Assignment__c.Fields.Province_Without_City__c,
$ObjectType.Assignment__c.Fields.Name,
Firm__c & "" & Province__c) = Firm__c & "" & Province__c), AND(LEN( City__c ) = 0, VLOOKUP(
$ObjectType.Assignment__c.Fields.Province_With_City__c,
$ObjectType.Assignment__c.Fields.Name,
Firm__c & "" & Province__c) = Firm__c & "" & Province__c))

 

There is no error in the formula. But the validation is always pass.

It doesn't capture the cases I plan to validate. If Account1 ON SalesTeam1 exists, Account1 ON Toronto SalesTeam2 is saved successfully.

 

Is my design correct for validation these cases? I'm not quite sure with VLOOKUP function. Is it used correctly in the validation rule?

 

Any reply is appreciated.

 

Regards,

 

Karen

 

 

karen_pandakaren_panda

Hello,

 

Sorry, the validation rule is as below:

 

OR(AND(LEN( City__c ) > 0, VLOOKUP(
$ObjectType.Assignment__c.Fields.Province_Without_​City__c,
$ObjectType.Assignment__c.Fields.Name,
Account__c & "" & Province__c) = Account__c & "" & Province__c), AND(LEN( City__c ) = 0, VLOOKUP(
$ObjectType.Assignment__c.Fields.Province_With_Cit​y__c,
$ObjectType.Assignment__c.Fields.Name,
Account__c & "" & Province__c) = Account__c & "" & Province__c))

 

Regards,

 

Karen
karen_pandakaren_panda

Hello,

 

I have the following lines in the testMethod:

Assignment__c[] oga = new Assignment__c[]

{
       new Assignment__c(Account__c = AccountId, Sales_Team__c = stId1, Province__c='MB'))
};
insert oga;
               
Assignment__c MBRecord = new Assignment__c(Account__c = AccountId, Sales_Team__c = stId3, City__c='Winnipeg', Province__c='MB');
insert MBRecord;

 

And below is the log file lines for validate rule. The last line is VALIDATION_PASS which

 it shouldn't be.

 

15:26:18.091 (8091719000)|WF_SPOOL_ACTION_BEGIN|Workflow
15:26:18.097 (8097369000)|WF_FIELD_UPDATE|[Omnibus Geography Assignment: 1966 a19W00000000Pd8]|Field:Assignment: Province Without City|Value:001W000000690Lt MB|Id=04YW0000000079F|


15:26:18.139 (8139431000)|VALIDATION_RULE|03dW0000000060Z|Check_Province
15:26:18.139 (8139840000)|VALIDATION_FORMULA|OR(AND(LEN( City__c ) > 0, VLOOKUP(
$ObjectType.Assignment__c.Fields.Province_Without_City__c,
$ObjectType.Assignment__c.Fields.Name,
Account__c & "" & Province__c) = Account__c & "" & Province__c), AND(LEN( City__c ) = 0, VLOOKUP(
$ObjectType.Assignment__c.Fields.Province_With_City__c,
$ObjectType.Assignment__c.Fields.Name,
Account__c & "" & Province__c) = Account__c & "" & Province__c))|$ObjectType.Assignment__c.Fields.Province_Without_City__c=Assignment__c.Province_Without_City__c , $ObjectType.Assignment__c.Fields.Name=Assignment__c.Name , Province__c=MB , Account__c=001W000000690Lt , City__c=Winnipeg , $ObjectType.Assignment__c.Fields.Province_With_City__c=Assignment__c.Province_With_City__c
15:26:18.139 (8139855000)|VALIDATION_PASS

 

Any reply is appreciated.

 

Regards,

 

Karen