locked
Can someone help me with my code RRS feed

  • Question

  • Hi All,

    I am trying to do a record count using the code below. The FetchXML query retrieves the record count and is then supposed to store it in a wholenumber field called "new_totalRecords". I am getting errors because I am trying to re-use a snippet I found on the Internet. The code below seems to work for FetchXML queries and counting money, but not whole numbers. Can anyone please help me diagnose what's going on. I would be very grateful as this is something that has been bothering me for a few days now.

    The code is:

                try {
                    decimal totalAmountCCORC = 0;
                    string estimatedvalue_sumCCORC = string.Format(@"
                        <fetch distinct='false' mapping='logical' aggregate='true'>
                        <entity name='new_Counter'>
                        <attribute name='new_countvalue' aggregate='sum' alias='estimatedvalue_sumCCORC' />
                        </entity>
                        </fetch>", a.Id);
                    
                    EntityCollection estimatedvalue_sumc_resultCCORC = service.RetrieveMultiple(new FetchExpression(estimatedvalue_sumCCORC));
                    
                    foreach(var c in estimatedvalue_sumc_resultCCORC.Entities) {
                        if (c.Contains("estimatedvalue_sumCCORC") && c["estimatedvalue_sumCCORC"].ToString() != null) {
                            totalAmountCCORC = ((Money)((AliasedValue) c["estimatedvalue_sumCCORC"]).Value).Value;
                        }
                    }
                    
                    //UPDATE
                    Entity accCCORC = new Entity("new_Counter");
                    accCCORC.Id = a.Id;
                    accCCORC.Attributes.Add("new_totalRecords", new Money(totalAmountCCORC));
                    service.Update(accCCORC);
                } catch (FaultException ex) {
                    throw new InvalidPluginExecutionException("An error occurred in the plug-in.", ex);
                }


    • Edited by Sebd.DD Tuesday, December 10, 2013 11:07 PM
    Tuesday, December 10, 2013 10:41 PM

All replies

  • Hi,

    Try something like this:

    string fetchxml = string.Format(@"
    	<fetch distinct='false' mapping='logical'> 
    		<entity name='account'> 
    			<attribute name='accountid' alias='acc'/> 
    		</entity> 
    	</fetch>");
    
    EntityCollection result = service.RetrieveMultiple(new FetchExpression(fetchxml));
    int recCount = result.Entities.Count;


    Eric UNG [Senior Analyst Programmer :: Sydney, Australia]

    Wednesday, December 11, 2013 12:39 AM
  • Hi thanks for the reply,

    I am trying to update a field using: 

     LeadAccount.Attributes.Add("new_creditlimit", "" + recCount); but it is giving me errors. I'm not sure if my syntax is correct.

    Wednesday, December 11, 2013 9:07 AM
  • Hi,

    You store the value in integer type variable and update using the below syntax.

     LeadAccount.Attributes["new_totalRecords"] = recCount ;

    HTH!

    Thanks!

    Wednesday, December 11, 2013 9:12 AM
  • Hi, whenever I try to execute I am always returned with a 1. Although my query is for SUM, I'm not sure what's going on. but I suspect it is because of this line: recCount = result.Entities.Count;

    The value that I should get is 96. But I am always getting 1. The query is:

                

    int recCount = 0;
    string resultSet = string.Format(@"

          <fetch distinct='false' mapping='logical' aggregate='true'>

          <entity name='new_Counter'>

          <attribute name='new_countvalue' aggregate='sum' alias='resultSetVal' />                   

          </entity>

          </fetch>", a.Id);


    EntityCollection result = service.RetrieveMultiple(new FetchExpression(resultSet));

    recCount = result.Entities.Count;

    Can someone tell me what's happening here?

    Wednesday, December 11, 2013 1:17 PM
  • It looks like you aren't counting any values in your "foreach" statement? Below I have a solution that is kind of similar to yours. When a case is created I want to check if there are any active projects for the parent account and count how many open projects there are. I declare "dec" as a variable that will be my count for open projects. Then I get my query for open projects related the account and for each project returned I add 1 to "dec" then I set the value of the project count field to dec and that returns the total number of open projects for the case's related account. Hope this helps you.

    ntity entity = (Entity)context.InputParameters["Target"];
                    EntityReference contactlookup = (EntityReference)entity.Attributes["customerid"];
                    Entity seniorRep = service.Retrieve("account", contactlookup.Id, new ColumnSet("new_seniortechflag"));
                    bool supRep = (bool)seniorRep["new_seniortechflag"];
                    decimal dec = 0;
    
                    if (entity.Attributes.Contains("customerid"))
                    {
                        var res = from c in ServiceContext.CreateQuery("new_projectcontrol")
                                  where c["new_schooldistrict"].Equals(contactlookup.Id) && c["statecode"].Equals(0)
                                  select c;
    
                        foreach (var c in res)
                        {
                            if (c["new_schooldistrict"] != null)
                            {                                                     
                                   entity["new_activeprojects"] = true;
                                   dec = dec + 1;
                            }
    
                            else
                            {
                                entity["new_activeprojects"] = false;
                            }
                        }
    
                        if (supRep == true)
                        {
                            entity["new_tierlevel"] = new OptionSetValue(100000002);
                            entity["new_projectcount"] = dec;
                        }
                        service.Update(entity); 

    Wednesday, December 11, 2013 4:34 PM