1

I'm trying to add a Business Central [Sales Line] child record to a [Sales Header] parent invoice record in a C# OData program. I know the header record exists because the program has just created it & I can see it in BC, but when I try to add the child record using the binding Document No: 102206 I get an error (see below) that indicates it can't see the header record.

The field Document No. of table Sales Line contains a value (102206) that cannot be found in the related table (Sales Header).

Any thoughts?

NickSO
  • 33
  • 2
  • 6
  • You have two tables and the number has to be in both the Sales Header table and the Sales Line table. Normally you add a new sales which gives an order number and then you add line items. – jdweng Dec 21 '20 at 12:00
  • I'm trying to add a record to both tables using the same number (in this case: 102206). I'm successfully adding the parent header record but the attempt to add the child Sales Line record is failing with the aforementioned error. – NickSO Dec 21 '20 at 13:37
  • Is both tables and number or varchar? One may be a string and the other a number. – jdweng Dec 21 '20 at 14:56
  • In both tables the tablerelation field is Code[20]. Same data type. – NickSO Dec 21 '20 at 16:44
  • Check the tablerelationship property : https://learn.microsoft.com/en-us/dynamics365/business-central/dev-itpro/developer/properties/devenv-tablerelation-property – jdweng Dec 21 '20 at 17:09
  • Just to further illustrate the mystifying nature of this issue, I'm re-reading the parent Invoice (siParent) that's just been created and then using the key values to create the child, but I'm still getting the same error? Baffling. // Sanity Check - Reread parent invoice record to prove that it exists var siParent = gobjDSC.APISalesInvoice.Where(si => si.No == lstrUnPostedInvoiceNo).First(); // Pass in the key values from the parent object to the child NAV.APISalesLine lobjUSL = NAV.APISalesLine.CreateAPISalesLine(siParent.DocumentType, siParent.No, 10000); – NickSO Jan 06 '21 at 14:48
  • What driver are you using? ACE/JET? If there is an empty cell in column 1 the ACE/JET will stop. You may need to add : IMEX=1. See : https://www.connectionstrings.com/excel/ – jdweng Jan 06 '21 at 17:29
  • I'm using the OData interface to MS Business Central which means there is no driver. You seem to be referring to an Excel spreadsheet? Do we have crossed wires? – NickSO Jan 08 '21 at 09:11
  • The OData requires a connection to a database and the connection is done through a driver. The ACE/JET is an Oledb driver that is usually used to connect to Office products and can be used to connected to a range of databases that have an Ole db interface. – jdweng Jan 08 '21 at 10:35
  • I am running into this same issue with Purchase Invoices and Purchase Invoice Lines. The issue is sporadic. Sometimes I'm even able to create the first line and then subsequent lines will fail. Did you make any progress on this issue? – IamMike May 18 '22 at 17:41
  • I'm afraid not. I ended up writing a Business Central extension in AL using Visual Studio Code which worked a treat. – NickSO May 20 '22 at 08:20

1 Answers1

0

The Sales Header table contains multiple different Document Types so unless you specify the Document Type it will assume you mean Quote (because it is the default value for Document Type).

Another approach you could use is to insert the header and lines in one go using deep insert.

The payload for that should look something like this:

{
    "Number": "Your Document No.",
    "invoiceLines": {
        ...
    }
}

Note that invoiceLines might be not be the right name - it could be salesInvoiceLines, salesLines or just lines.

kaspermoerch
  • 16,127
  • 4
  • 44
  • 67