Using the .NET Business Connector to Export Sales Orders with Multiple Sales Lines into AX from a .NET Application: Part 2

Expanding on the previous blog, let’s say that another requirement has been added, which is, to add the ability to track the date that a record was imported into the system, which will be used to prevent duplicate records for a given customer in a given month from being imported.

The first step would be to add an ImportDate field in the SalesTable via the AOT

The second step would then be to modify our class (ImportToAX) method (ImportSalesOrder) to accept an import date.

The third step would then be to modify our class (ImportToAX) method (ImportSalesOrder) to include logic to determine if a duplicate record (a given customer in a given month) already exists. Programming instinct coupled with my novice skills in AX development tell me that in order to determine if we already have a record for a given customer in a given month, I would add the following lines into our X++ class (ImportToAX) method (ImportSalesOrder):

Select count(RecId) From salesTable
where salesTable.CustAccount == CustAccount && mthofyr(salesTable.ImportDate) == mthofyr(ImportDate) && year(salesTable.ImportDate) == year(ImportDate);
ret = salesTable.RecId;

if (ret == 0)
  {
   ………
  }
else
  {
    throw Global::error("Only one record is allowed per customer in a given month!");
  }

This however is invalid code, which throws two errors when compiled. The first being:

“Illegal use of WHERE expression”

This error is thrown, apparently because methods cannot be used in a where clause, and since I have attempted to use the mthofyr() and year() methods within my where clause, we have an issue. Therefore, one possible way of getting around this, is to take a different approach, such as the following valid code:

found = false;

while select salesTable where salesTable.CustAccount == CustAccount
{
if (mthofyr(salesTable.ImportDate) == mthofyr(ImportDate) && year(salesTable.ImportDate) == year(ImportDate)) found = true;
}

if (!found)
{
………
}
else
{
throw Global::error("Only one record is allowed per customer in a given month!");
}

The second error that we get:

“Container and unbounded string (text) fields are not allowed in a WHERE expression”

This is inherently related to our method definition, where the CustAccount parameter is defined as an str:

str ImportSalesOrder(str CustAccount, real Total, real Taxes, real Fees, date ImportDate)
{
……
}

The problem is that the salesTable.CustAccount field is actually defined as the extended data type, CustAccount, which extends the base type str. Because AX is a bit finicky, a standard str type cannot be compared to an extended data type (even though the extended data type extends the base type we are comparing to). Therefore, the simplest solution is to make our incoming CustAccount parameter of type CustAccount.

str ImportRoyaltyReport(CustAccount CustAccount, real Total, real Taxes, real Fees, date ImportDate)
{
……
}

This actually has no ill affect the .NET side of the project. In fact, the only parts of our .NET function needing change would be first in the function definition:

static public bool ImportOrder(string CustomerAccount, decimal Total, decimal Tax, decimal Fees, DateTime ImportDate)

And then the code to pass in the additional Import Date parameter to the AX method call:

object[] param = new object[5];
param[0] = CustomerAccount;
param[1] = Total;
param[2] = Tax;
param[3] = Fees;
param[4] = ImportDate;
Object retval = Import.Call("ImportSalesOrder", param);

Finally, to reduce code redundancy in our AX class (ImportToAX) method (ImportSalesOrder), the method could be compacted to avoid having the same block of code repeated 3 times (as in Part 1). Here we can use a couple additional variables, a loop, and a switch statement, which will transform our method as whole to look like the following:

str ImportSalesOrder(CustAccount CustAccount, real Total, real Taxes, real Fees, date ImportDate)
{

SalesTable salesTable;
SalesLine salesLine;
InventDim inventDim;
NumberSeq NumberSeq;

str retval;
int line;
str line_item;
real line_amt;
boolean found;
;

retval = "";

found = false;

while select salesTable where salesTable.CustAccount == CustAccount
{
if (mthofyr(salesTable.ImportDate) == mthofyr(ImportDate) && year(salesTable.ImportDate) == year(ImportDate)) found = true;
}

if (!found)
{

// Generate the next SalesId (which is a Number Sequence)...

NumberSeq = NumberSeq::newGetNumFromCode(SalesParameters::numRefSalesId().numberSequence);
salesTable.SalesId = NumberSeq.num();

// Create the Sales Order...

salesTable.initValue();
salesTable.CustAccount = CustAccount;
salesTable.deliveryDate = today();
salesTable.PurchOrderFormNum = "Imported Data";
salesTable.Tmt_RoyaltyImportDate = ImportDate;

salesTable.initFromCustTable();

salesTable.insert();

// Create the 3 Sales Lines...

for (line = 1; line <= 3; line++) { switch(line) { case 1: line_item = "Total"; line_amt = Total; break; case 2: line_item = "Taxes"; line_amt = Taxes; break; case 3: line_item = "Fees"; line_amt = Fees; } salesLine.clear(); salesLine.SalesId = salesTable.SalesId; inventDim = salesLine.inventDim(); inventDim.InventSiteId = "01"; salesLine.setInventDimIdFromInventDim(inventDim); salesLine.ItemId = line_item; salesLine.SalesQty = 1; salesLine.SalesUnit = 'EA'; salesLine.SalesPrice = line_amt; salesLine.LineAmount = line_amt; salesLine.createLine(NoYes::No, NoYes::Yes, NoYes::No, NoYes::No, NoYes::No, NoYes::No); } retval = salesTable.SalesId; } else { throw Global::error("Only one record is allowed per customer in a given month!"); } return retval; }