I have a program that pulls Invoices from our CRM Dynamics Online system into a .csv file.
Sometimes our salespeople create multiple invoices on the same Order in CRM, and I need a way to remove any duplicate invoices on the .csv.
I would like to only pull invoices with distinct order numbers. How would this be achieved? I've been working all morning with distincts to no avail.
My code:
private EntityCollection GetInvoices(Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy crmService)
{
string fx = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
<entity name='invoice'>
<attribute name='invoiceid' alias='InvoiceId' />
<attribute name='customerid' alias='ShipTO' />
<attribute name='shipto_postalcode' alias='ShipZip' />
<attribute name='shipto_line2' alias='ShipAddr2' />
<attribute name='shipto_line1' alias='ShipAddr1' />
<attribute name='shipto_stateorprovince' alias='ShipState' />
<attribute name='shipto_country' alias='ShipCountry' />
<attribute name='shipto_city' alias='ShipCity' />
<attribute name='neu_customerponumber' alias='PO' />
<attribute name='paymenttermscode' alias='Terms' />
<attribute name='createdon' alias='ShipDate'/>
<attribute name='ordernumber' alias='InvoiceNo' />
";
fx += GetInvoiceFilter();
fx += @"
<link-entity name='salesorder' from='salesorderid' to='salesorderid' visible='false' link-type='outer' alias='order1'>
<attribute name='datefulfilled' alias='FirstOfShippingDate' />
<link-entity name='systemuser' from='systemuserid' to='ownerid' visible='false' link-type='outer' alias='systemuser1'>
<attribute name='fullname' alias='SalesRep' />
</link-entity>
</link-entity>
<link-entity name='invoicedetail' from='invoiceid' to='invoiceid' visible='false' link-type='outer' alias='invoicedetail1'>
<attribute name='neu_percentdiscount' alias='Discount' />
<attribute name='invoicedetailid' alias='InvoiceDetailId' />
<attribute name='baseamount' alias='Amount' />
<attribute name='extendedamount' alias='Sales' />
<link-entity name='product' from='productid' to='productid' visible='false' link-type='outer' alias='product1'>
<attribute name='neu_division' alias='Division' />
<attribute name='producttypecode' alias='Desc' />
<attribute name='productnumber' alias='CatalogNumber' />
</link-entity>
</link-entity>
<link-entity name='account' alias='account1' to='customerid' from='accountid'>
<attribute name='accountnumber' alias='CustID' />
</link-entity>
</entity>
</fetch>";
return crmService.RetrieveMultiple(new FetchExpression(fx));
}
private string GetInvoiceFilter()
{
string fetchFilter = "";
fetchFilter = @"<filter type='and'>";
if (txtOrderName.Text.Length > 0)
{
fetchFilter += @"<condition attribute='ordernumber' operator='eq' value='";
fetchFilter += txtOrderName.Text;
fetchFilter += "' />";
}
else
{
fetchFilter += @"<condition attribute='statuscode' operator='eq' value='1' />";
fetchFilter += @"<condition attribute='neu_exportedtopeachtree' operator='null' />";
fetchFilter += @"<condition attribute='createdon' operator='on-or-after' value='";
fetchFilter += dtpFrom.Text;
fetchFilter += "' />";
fetchFilter += " <condition attribute='createdon' operator='on-or-before' value='";
fetchFilter += dtpTo.Text;
fetchFilter += "' />";
}
fetchFilter += @"</filter>";
return fetchFilter;
}
The distinct clause on the fetch statement only filters out duplicate ids that come back in the result set. To filter our your dupes based on order number, you'll have to do that programmatically after the results come back.