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

Hello,

I'm new to Force.com.
I have a custom object: percentage__c with the following custom fields:
Account
Province__c
City__c
Team__c
Allocation__c

I need to create an after insert, after update, after delete trigger to update the allocation__c field if there are more than one Team__c for the same Account, Province__c, and City__c.

Here are some sample data:
Account1 ON Toronto  Team1 100
Account2 ON Niagara Falls  Team1  100

when Account3 comes in,
Account3 ON Toronto Team2 100

the trigger should update both Account1 and Account3 to 50, the total should always be 100.
Account1 ON Toronto Team1 50
Account3 ON Toronto Team2 50

I need help on how to get the same Province__c and City__c from the existing records based on triggers.new in a set of Id.

Any help is appreciated.

Regards,

Karen

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

Hello,

I'm new to Force.com.
I have a custom object: percentage__c with the following custom fields:
Account
Province__c
City__c
Team__c
Allocation__c

I need to create an after insert, after update, after delete trigger to update the allocation__c field if there are more than one Team__c for the same Account, Province__c, and City__c.

Here are some sample data:
Account1 ON Toronto  Team1 100
Account2 ON Niagara Falls  Team1  100

when Account3 comes in,
Account3 ON Toronto Team2 100

the trigger should update both Account1 and Account3 to 50, the total should always be 100.
Account1 ON Toronto Team1 50
Account3 ON Toronto Team2 50

I need help on how to get the same Province__c and City__c from the existing records based on triggers.new in a set of Id.

Any help is appreciated.

Regards,

Karen