Single Invoice for Multiple Sales Order based on Selected lines.

Hi All,

I have to invoice One Invoice for multiple Sales Order for the selected line in D365 F&O. I need to Post the Invoice through X++ .

Eg- As per below screen two Invoice should be generated based on Bath id which will hold the data from SO-0001 & SO-0002.

First Invoice will be hold all the data where Batch Id is BT-0001 and Second Invoice will be hold all the data where Batch Id is BT-0002.

Please Help

I would try something like this:

SalesFormLetter formLetter = SalesFormLetter::construct(DocumentStatus::Invoice);
// ... specify parameters here ...

formLetter.chooseLinesQuery(_chooseLinesQuery);
formLetter.chooseLines();
formLetter.runOperation();

It uses a query to select order lines. But that’s not a problem - you can use MultiSelectionHelper.createQueryRanges() to add query ranges for selected records.

Hi Martin

formLetter.chooseLinesQuery(_chooseLinesQuery); is throwing error for different type. below is error.

“unable to cast object of type ‘dynamics.ax.application.queries.salesupdate’ to type ‘dynamics.ax.application.sysqueryrun’”

You seem to be passing a wrong value to chooseLinesQuery(). The right type is SysQueryRun.

let me try with SysQueryRun … Can you provide any example for MultiSelectionHelper.createQueryRanges() ?

You can easily find examples by yourself. Simply right-click the method name and choose “Find references” or “Find All References” (depending on whether you do it in the designer or the code editor). You’ll get a list of references in Find Symbol Results window. Double-click one of them to see code.

For example, this is what you can see in tsTimesheetSignOffController.setRange():

FormDataSource formDataSource = FormDataUtil::getFormDataSource(this.parmArgs().record());
QueryBuildDataSource queryBuildDataSource = _query.dataSourceTable(tablenum(TSTimesheetTable));
...
MultiSelectionHelper selectionHelper = MultiSelectionHelper::construct();
selectionHelper.parmDataSource(formDataSource);
selectionHelper.createQueryRanges(queryBuildDataSource, fieldStr(TSTimesheetTable, TimesheetNbr));

Hi Martin,

I need to run this in a Batch where we need to create Invoice based on Batch Id for Multiple sales order and lines. One batch will be one Invoice Id

Okay, what’s the problem?

I am trying with below code .

SalesTable SalesTable;
SalesFormLetter salesFormLetter;
SysQueryRun queryRun;
Query query;
Str1260 strSalesTable =“ASO000169,ASO000170”;

CRMSalesOrderInvoiveTable CRMSalesOrderInvoiveTable; // This table hold the record from CRM for Invoice
str agreementNum;
//salesTable salesTable;
;
ttsbegin;

salesFormLetter = SalesFormLetter::construct(DocumentStatus::Invoice);

query = new Query(QueryStr(SalesUpdate));

query.dataSourceTable(tablenum(CRMSalesOrderInvoiveTable )).addRange(fieldnum(CRMSalesOrderInvoiveTable , SalesId)).value(strSalesTable);

queryRun = new SysQueryRun(query);

salesFormLetter.chooseLinesQuery(queryRun);
salesFormLetter.transDate(systemdateget());
salesFormLetter.specQty(SalesUpdate::All);
salesFormLetter.printFormLetter(true);

salesFormLetter.chooseLines();

salesFormLetter.run();

Let me simplify your code a bit:

Str1260 strSalesTable ="ASO000169,ASO000170";

Query query = new Query(queryStr(SalesUpdate));
query.dataSourceTable(tableNum(CRMSalesOrderInvoiveTable)).addRange(fieldnum(CRMSalesOrderInvoiveTable, SalesId)).value(strSalesTable);

SysQueryRun queryRun = new SysQueryRun(query);

SalesFormLetter salesFormLetter = SalesFormLetter::construct(DocumentStatus::Invoice);
salesFormLetter.chooseLinesQuery(queryRun);
salesFormLetter.transDate(systemdateget());
salesFormLetter.specQty(SalesUpdate::All);
salesFormLetter.printFormLetter(true);
salesFormLetter.chooseLines();
salesFormLetter.run();

By the way, please use Insert > Insert Code to paste source code, as I did.

I’m surprised that you’re not filtering SalesTable or SalesLine. What’s the reason? Also, SalesUpdate query doesn’t contain any table called CRMSalesOrderInvoiveTable. Did you add it in an extension? If not, you don’t get anything from query.dataSourceTable(tableNum(CRMSalesOrderInvoiveTable)), do you?

We are using this CRM table as the staging table where we are getting Batch Id from CRM based on that Batch Id we need to Invoice the lines which are related to multiple Sales Orders.

Let me ask again:

  • What’s the problem that you need help with?
  • Does query.dataSourceTable(tableNum(CRMSalesOrderInvoiveTable)) return a value?
  • Have you added CRMSalesOrderInvoiveTable to SalesUpdate query?
  1. When i am trying to Invoice Some Specific lines. All Open Sales order are getting Invoiced with same id Where as we requiered only the records which are present in CRMSalesOrderInvoiveTable should Invoiced.
  2. Yes Query is getting records from CRMSalesOrderInvoiveTable
  3. No i didn’t Added this new table as we are just updating sales Table .
  1. You need to isolate the problem before you can fix it. I would start by debugging the code for choosing lines.

2.+3. I’m sorry, but it’s not clear to me how you can add CRMSalesOrderInvoiveTable from SalesUpdate query if such a data source doesn’t exist there. If it works, there must be an extension that you’re not aware of. If it’s the case, find it and review the type of join.

OK . thanks martin,

Let me do some more investigation .

Thanks for all suggestion.

Hi Martin,

Thanks for you help…

this has been done long ago by your guidance.

The actual issue was with SalesUpdate query. It was working fine till we were invoicing all CRM table data in one invoice but At last movement we got a changes where Invoice was supposed to done based on Batch ID.

For that we had to Create a new System Base Query and used it with your suggested code.

Thanks