1

I am trying to query for asset name from case and my query is:

SELECT ID, accountId, account.assetId from case limit 10

but the above query is not working. Error: No such column 'assetId' on entity 'Account'.

My actual requirement is to get values of caseId, related accountId and asset name related to this accountId in a JSON format.

Map<Id, Case> caseMap = new Map<Id, Case>([Select Id, accountId, account.name, From Case Where ID IN :caseIds]);

JSONGenerator gen = JSON.createGenerator(true);
        gen.writeStartArray();
            for(Id caseId : caseIds){
                gen.writeStartObject();
                gen.writeStringField('CaseId', caseId);
                string acId = caseMap.get(caseId).accountId;
               //get asset names related to above accountId here
                gen.writeEndObject();
            }
        gen.writeEndArray();

Can someone please correct me where I am getting this wrong.

  • What do you mean when you say it's not working? Do you get an error? – Nick Cook Aug 6 '15 at 0:09
  • Hi Nick, updated the error I am getting in my post. Kindly refer. Thanks – sf_user Aug 6 '15 at 0:12
  • assetId doesn't exist on Account - see developer.salesforce.com/docs/atlas.en-us.object_reference.meta/…; instead, accounts are parents of 0..n Assets – cropredy Aug 6 '15 at 0:36
  • Doesn't that explain the problem? Is asset a parent or child of Account? If it's a child, you can't use the dot notation to get the values. i.e. There can potentially be many Assets associated with an Account. Which Asset should the query select? – Nick Cook Aug 6 '15 at 0:36
1

The Asset object is a child object of the Account object. You will need to get all related children (assets) from the case's parent Account. Also instead of using JSONGenerator you can directly serialize the list with JSON.serialize(). This is how I would solve this:

Create a wrapper class to hold the Case Id, Account Id and all Asset Names related to that account (as you described):

public class CaseWithAsset
{
    public Id caseId;
    public Id accountId;
    public List <String> assetNames; 
}

Then in your constructor create a list that will hold multiple instances of this class:

List <CaseWithAsset> casesWithAssets = new List <CaseWithAsset> ();

The query on the Case object should only get the parent Account IDs:

List <Case> cases = [SELECT Id, AccountId FROM Case LIMIT 10];

Then follow the comments in the code below:

// Map the account Id to the related case and also create a placeholder for the asset names
Map <Id, CaseWithAsset> accountIdToCaseWithAsset = new Map <Id, CaseWithAsset> ();

// Go through each case in your list
for (Case caseRecord : cases)
{
    // Create new instance of the wrapper class
    CaseWithAsset caseWithAsset = new CaseWithAsset();
    // Set the caseId and accountId (we know both at this stage)
    caseWithAsset.caseId = caseRecord.Id;
    caseWithAsset.accountId = caseRecord.AccountId;
    // Create an empty list that will hold all related asset names
    caseWithAsset.assetNames = new List <String> ();
    // Map this wrapper class instance to the accountId - it's the key that links this and the asset
    accountIdToCaseWithAsset.put(caseRecord.AccountId, caseWithAsset);
}

// Query the assets that are related to the account IDs from above
for (Asset asset : [SELECT Id, Name, AccountId FROM Asset WHERE AccountId IN :accountIdToCaseWithAsset.keySet()])
{
    // Get the wrapper class instance from the map and add the asset name to the empty list
    accountIdToCaseWithAsset.get(asset.AccountId).assetNames.add(asset.Name);
}

// Serialize the list of wrapper class instances
String jsonString = JSON.serialize(accountIdToCaseWithAsset.values());

I typed all this on the fly, it might have some typos or errors, but you can tweak it to your needs. It should get you started at least.

  • 1
    Thanks Bachovski. This should definitely help me in getting started. – sf_user Aug 6 '15 at 0:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.