+ Start a Discussion
karen_pandakaren_panda 

SOQL - same province and city records

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

Best Answer chosen by Admin (Salesforce Developers) 
AmitSahuAmitSahu

You can use map I guess:

 

Map<String,String> strCityMap = new Map<String,String>();

 

for(String str1:pCity)

{

for(String str2:pProvince)

{

strCityMap.put(str1,str2);

}

}

All Answers

AmitSahuAmitSahu
So you mean when there will be 4 accounts with ON and Toronto then the allocation should be 25?
karen_pandakaren_panda

Yes, each record gets allocation =25 if there are 4 records for Account1 ON Toronto with different Teams.

 

Thanks,

 

Karen

karen_pandakaren_panda

Hello,

 

I tried to start with the following code:

 

    //get a set of the unique province and city
    Set<String> pCity = new Set<String>();
    Set<String> pProvince = new Set<String>();
 
    for (percentage__c p: Trigger.new)
    {
        pCity.add(p.City__c);
        pProvince.add(p.Province__c);
    }

 

    //get a set of cities which have more than one Team shared
     Set<String> Cities = new Set<String>();
      
    for (percentage__c existingCities : [select Id, city__c  from percentage__c group by city__c having count(city__c ) > 1]);
    {
         Cities.add(existingCities.city__c);
    }

 

then plan to compare whether set pCity is inside set Cities.

 

I'm getting stuck for going further.

 

Any help or even just a high-level guidance is appreciated.

 

Regards,

 

Karen

karen_pandakaren_panda

Hello,

 

I noticed my SOQL [select Id, city__c  from percentage__c group by city__c having count(city__c ) > 1] would fail if there are 2 provines with the same city name.

I would like to simplify my question to how can I get a list of province, city which have more than one record in the database.

For example:

Account1 ON Toronto Team1

Account1 ON Toronto Team2

Account2 BC Toronto Team3

Account2 BC Toronto Team4

Account3 ON Niagara Falls Team5

 

I want the query return:

ON Toronto

BC Toronto

 

Any help is appreciated.

 

Regards,

 

Karen

AmitSahuAmitSahu

You can use map I guess:

 

Map<String,String> strCityMap = new Map<String,String>();

 

for(String str1:pCity)

{

for(String str2:pProvince)

{

strCityMap.put(str1,str2);

}

}

This was selected as the best answer