locked
CRM 2013 - C# Plugin - Working with FetchXml Query RRS feed

  • Question

  • Hi I have a plugin that is retrieving information from a grid via FetchXML

    As far as I'm aware the XML code is all okay, I'm putting the results into a EntityCollection and then into a list
    The list is trying to get the name of a product and the quantity. When name is selected it's stating that the given key is not present, I've tried changing it to productid and going from there, productid gets accepted but it outputs
    Microsoft.Xrm.Sdk.EntityReference instead of the name of the product, I'm assuming this is because it's a lookup field, however any attempts of getting the name has failed

    Here is my code, any help would be gratefully appreciated

    Guid orderID = (Guid)((Entity)context.InputParameters["Target"]).Id;
    
                    string fetchxml = @"
                    <fetch distinct='false' mapping='logical' output-format='xml-platform' version='1.0'>
                        <entity name='salesorderdetail'>
                            <attribute name='productid'/>
                            <attribute name='productdescription'/>
                            <attribute name='priceperunit'/>
                            <attribute name='quantity'/>
                            <attribute name='extendedamount'/>
                            <attribute name='salesorderdetailid'/>
                            <order descending='false' attribute='productid'/>
                                <link-entity name='product' alias='af' to='productid' from='productid'>
                                    <attribute name='name'/>
                               
                                <link-entity name='salesorderdetail' alias='ab' to='productid' from='productid'> 
                                <link-entity name='salesorder' alias='ac' to='salesorderid' from='salesorderid'> 
                                    <filter type='and'> 
                                        <condition attribute='salesorderid' operator='eq' value='" + orderID + @"'/> 
                                    </filter> 
                                </link-entity> 
                                </link-entity>
                                </link-entity>
                        </entity>
                    </fetch>";
                                    
                    EntityCollection result = service.RetrieveMultiple(new FetchExpression(fetchxml)); foreach (var c in result.Entities)
                        {
                            if (result != null && result.Entities.Count > 0)
                            {
                                List<string> _product = new List<string>();
                                foreach (Entity _entity in result.Entities)
                                {
                                   _product.Add(c.Attributes["name"].ToString());
                                   
                                   _product.Add(_entity.Attributes["quantity"].ToString());  
                                                                                          
                                }
    
                                CSVFile = string.Join(",", _product);
                                
                                
    
                                string AddressPath = "FM-OR" + "_";
                                string AddressSavePath = @"\\fm\CRMdata\maesteg\" + AddressPath + ".csv";
                                System.IO.File.WriteAllText(AddressSavePath, CSVFile.ToString());
                            }
    
                        }

    Thanks, Shaun


    S.Harrison

    Thursday, July 3, 2014 9:47 AM

Answers

  • Hi,

    Try using:

    ((EntityReference)c.Attributes["productid"]).name.ToString());

    or

    c.Attributes["productidname"].ToString());


    Admin QuikView Solution for CRM 2013


    Thursday, July 3, 2014 9:56 AM
  • Hi

    Figured it out

    The end code is as follows

    productid = ((AliasedValue)_entity.Attributes["af.productnumber"]).Value.ToString();

    Thanks for your help

    Shaun


    S.Harrison

    Friday, July 4, 2014 9:18 AM

All replies

  • Hi,

    Try using:

    ((EntityReference)c.Attributes["productid"]).name.ToString());

    or

    c.Attributes["productidname"].ToString());


    Admin QuikView Solution for CRM 2013


    Thursday, July 3, 2014 9:56 AM
  • Hi Dynamotion

    Just realized that I put in the wrong code

    I only used c.attributes when I was trying different things out to see if it would work

    It's normally like the one below with _entity.Attributes

    Thanks, Shaun


    S.Harrison

    Thursday, July 3, 2014 10:03 AM
  • Neither worked

    Name wouldn't be accepted by the plugin - therefore removed it, but still ended up with Microsoft.Xrm.Sdk.EntityReference

    Second line of code again produced an error message stating that the given key was not present in the dictionary

    Thanks, Shaun


    S.Harrison

    Thursday, July 3, 2014 10:14 AM
  • Hi,

    In that case, did you try with

    ((EntityReference)_entity.Attributes["productid"]).name.ToString());

    or

    _entity.Attributes["productidname"].ToString());


    Admin QuikView Solution for CRM 2013


    Thursday, July 3, 2014 10:19 AM
  • Hi

    Tried both with C and _entity and no luck with either

    Is it do to with what I'm doing with the results from the Query perhaps?

    Something interesting was that within the add to list nest when I tried converting the lookup to its proper name it does work, however when I then export to CSV the name is still outputted as Microsoft.Xrm.Sdk.EntityReference

    Thanks, Shaun


    S.Harrison

    Thursday, July 3, 2014 10:28 AM
  • Hi Shaun,

    When you cast it to "EntityReference" like ((EntityReference)_entity.Attributes["productid"]). what are the options that you get in intellisense? Do you get Id, Name, etc.?


    Admin QuikView Solution for CRM 2013

    Thursday, July 3, 2014 11:31 AM
  • Hi Dynamotion

    My mistake, I was trying

    ((EntityReference)_entity.Attributes["productid"])name.ToString());

    As suggested

    But didn't even notice the . missing before name

    Just inserted it and now its working fine

    Thanks for your help

    Shaun


    S.Harrison

    Thursday, July 3, 2014 11:36 AM
  • Hi Shaun,

    My mistake. :) Typo, I missed the dot (.). Fixed it now.


    Admin QuikView Solution for CRM 2013

    Thursday, July 3, 2014 12:14 PM
  • Hi

    This is off topic but do you have any idea how to split the string outputted in a certain way?

    I've ended up with the following CSV file

    ProductE1, 3.0, ProductE2, 5.0, ProductE3, 2.0

    I want the output to be as follows;

    ProductE1, 3.0

    ProductE2, 5.0

    ProductE3, 2.0

    Thanks, Shaun


    S.Harrison

    Thursday, July 3, 2014 12:26 PM
  • Hi,

    After appending the ProductName and the Quantity with a "," try appending a "\r\n" and then write to your CSV file.


    Admin QuikView Solution for CRM 2013

    Thursday, July 3, 2014 12:39 PM
  • Will this output my requirements in different strings / array
    Sorry, didn't explain myself properly

    Thanks, Shaun


    S.Harrison

    Thursday, July 3, 2014 12:42 PM
  • Do you want to store your output to an array? Like each element of the array will be ProductName, Quantity e.g. {"Product1, 2", "Product2, 4", "Product 3, 5"}?

    Admin QuikView Solution for CRM 2013

    Thursday, July 3, 2014 12:48 PM
  • Im assuming an array would be best as you described it, that way then I can check the length of the array and loop through it to produce the individual CSV files

    Thanks, Shaun


    S.Harrison

    Thursday, July 3, 2014 12:55 PM
  • Hi, sorry to be a pain, but just been informed that i need product number not name for the CSV file-.-

    Technically it shouldn't be a problem

    I've changed name in the FetchXML Query to "productnumber"

    And changed the line 

    ((EntityReference)_entity.Attributes["productid"]).name.ToString());


    To the following

    _entity.Attributes["productnumber"].ToString();

    But the programe is saying that the given key is not present in the dictionary again

    Is it because the product number isn't in the grid?

    Thanks, Shaun


    S.Harrison


    Thursday, July 3, 2014 3:17 PM
  • Hi Shaun,

    The "productnumber" has to be within the <linkentity> tags of the "product"; something like this:

    <fetch distinct='false' mapping='logical' output-format='xml-platform' version='1.0'>
    	<entity name='salesorderdetail'>
    		<attribute name='productid'/>
    		<attribute name='productdescription'/>
    		<attribute name='priceperunit'/>
    		<attribute name='quantity'/>
    		<attribute name='extendedamount'/>
    		<attribute name='salesorderdetailid'/>
    		<order descending='false' attribute='productid'/>
    			<link-entity name='product' alias='af' to='productid' from='productid'>
    				<attribute name='name'/>	
                                    <attribute name='productnumber'/>				
    			<link-entity name='salesorderdetail' alias='ab' to='productid' from='productid'> 
    			<link-entity name='salesorder' alias='ac' to='salesorderid' from='salesorderid'> 
    				<filter type='and'> 
    					<condition attribute='salesorderid' operator='eq' value='" + orderID + @"'/> 
    				</filter> 
    			</link-entity> 
    			</link-entity>
    			</link-entity>
    	</entity>
    </fetch>
    


    Admin QuikView Solution for CRM 2013

    Friday, July 4, 2014 6:42 AM
  • Hi

    Sorry, forgot to mention I did include that line, I replaced it instead of name and no luck

    I just tried it with the <attribute name -'name'/> back in there and still no luck

    Code is as follows

    string fetchxml = @"
    <fetch distinct='false' mapping='logical' output-format='xml-platform' version='1.0'>
    <entity name='salesorderdetail'>
    <attribute name='productid'/>
    <attribute name='productdescription'/>
    <attribute name='priceperunit'/>
    <attribute name='quantity'/>
    <attribute name='extendedamount'/>
    <attribute name='salesorderdetailid'/>
    <order descending='false' attribute='productid'/>
    <link-entity name='product' alias='af' to='productid' from='productid'>
    <attribute name='name'/>
    <attribute name='productnumber'/>
    <link-entity name='salesorderdetail' alias='ab' to='productid' from='productid'>
    <link-entity name='salesorder' alias='ac' to='salesorderid' from='salesorderid'>
    <filter type='and'>
    <condition attribute='salesorderid' operator='eq' value='" + orderID + @"'/>
    </filter>
    </link-entity>
    </link-entity>
    </link-entity>
    </entity>
    </fetch>";
    EntityCollection result = service.RetrieveMultiple(new FetchExpression(fetchxml));
    {
    if (result != null && result.Entities.Count > 0)
    {
    string todaysDate = DateTime.Now.ToString("dd/MM/yyyy"); //for date ordered
    DateTime dateAndTime = (DateTime)dateDeliveryRequiredImage["requestsdeliveryby"];
    var date = dateAndTime.ToString("dd/MM/yyy");
    List<string> _product = new List<string>();
    foreach (Entity _entity in result.Entities)
    {
    productid = _entity.Attributes["productnumber"].ToString();
    quantity = _entity.Attributes["quantity"].ToString();
    CSVFile = CSVFile +
    "FM-SSO" + "," +
    "" + "," + //room for transaction user id
    actualID + "," +
    "" + "," + //room for ordered by address
    "" + "," + //room for deliver to address
    "" + "," + //room for invoive to address
    customerOrderRefImage["new_customerorderreference"].ToString() + "," +
    todaysDate + "," +
    date + "," +
    productid + "," +
    quantity + "," +
    "" + "," + //room for ordered by address
    "" + "," + //room for ordered by address
    "" + "," + //room for ordered by address
    "" + "," + //room for ordered by address
    "" + "," + //room for ordered by address
    "\n"; //room for ordered by address
    }
    string AddressPath = "FM-SSO" + "_" + actualID;
    string AddressSavePath = @"\\fm\CRMdata\maesteg\" + AddressPath + ".csv";
    System.IO.File.WriteAllText(AddressSavePath, CSVFile.ToString());
    }
    
    


    S.Harrison

    Friday, July 4, 2014 7:36 AM
  • Hi Shaun,

    Did you try with

    _entity.Attributes["af.productnumber"].ToString();

    instead?


    Admin QuikView Solution for CRM 2013


    Friday, July 4, 2014 8:46 AM
  • Hi

    The line;

    _entity.Attributes["af.productnumber"].ToString();

    Did stop the error message appearing however, in the CSV file the output is now

    Microsoft.Sdk.Xrm.AliasedValue

    Thanks, Shaun


    S.Harrison

    Friday, July 4, 2014 8:56 AM
  • Hi

    Figured it out

    The end code is as follows

    productid = ((AliasedValue)_entity.Attributes["af.productnumber"]).Value.ToString();

    Thanks for your help

    Shaun


    S.Harrison

    Friday, July 4, 2014 9:18 AM
  • Hi Dynamotion

    Sorry to bother you again,

    I noticed that the date was always one day behind in the CSV output

    I researched a bit and found that its to do with UTC time being needed to convert to local time

    I asked online and got some results back with the following code;

    private DateTime RetrieveLocalTimeFromUTCTime(DateTime utcTime, IOrganizationService service)
            {
    
               
                int? timeZoneCode = RetrieveCurrentUsersSettings(service);
                
                if (!timeZoneCode.HasValue)
                    throw new Exception("Can't find time zone code");
    
               
    
                var request = new LocalTimeFromUtcTimeRequest
                {
                    TimeZoneCode = timeZoneCode.Value,
                    UtcTime = utcTime.ToUniversalTime()
                };
    
                var response = (LocalTimeFromUtcTimeResponse)service.Execute(request);
    
                return response.LocalTime;
                //var utcTime = utcTime.ToString("MM/dd/yyyy HH:mm:ss");
               // var localDateOnly = response.LocalTime.ToString("dd-MM-yyyy");
            }
    
            private int? RetrieveCurrentUsersSettings(IOrganizationService service)
            {
                var currentUserSettings = service.RetrieveMultiple(
                new QueryExpression("usersettings")
                {
                    ColumnSet = new ColumnSet("localeid", "timezonecode"),
                    Criteria = new FilterExpression
                    {
                        Conditions =
                {
                    new ConditionExpression("systemuserid", ConditionOperator.EqualUserId)
                }
                    }
                }).Entities[0].ToEntity<UserSetting>();
    
                return currentUserSettings.TimeZoneCode;
            }

    But the line

     }).Entities[0].ToEntity<UserSetting>();

    throws an error saying The type or namespace 'users' could not be found

    Any suggestions to fix it or an alternative method for sorting my problem?

    Thanks, Shaun


    S.Harrison

    Friday, July 4, 2014 10:32 AM
  • Hi All, been some time since I've been on here

    I've re-visited this plugin and realized that I need the product description also

    However when I use the line

     description = _entity.Attributes["productdescription"].ToString();

    or

     description = _entity.Attributes["description"].ToString();

    I'm getting an error stating that 'The given key was not present in the dictionary'

    If anyone's still out there let me know if you know a solution!

    Thanks


    S.Harrison

    Friday, May 8, 2015 3:47 PM