Create an estimate through Excel with the API

Extensibility
21

The MD5Value definitely shouldn’t have any dashes or spaces in it, it should be just a sequence of 32 hex digits 0123456789abcdef

22

@Rob - I’ve edited your post to remove the application ID and part of the account number.

Just to reiterate what @ian_roberts said however, the MD5 shouldn’t have dashed in it.

An example of an MD5 string would be:

902fbdd2b1df0c4f70b4a5d23525e932
23

Thanks Ian and Matthew, I realise the MD5 value is incorrect, I am still working on a suitable solution to incorporate this, however this was not the error I got, the error stated an invalid child element on line 18 “invoicedata”

Thanks
Rob

24

This is the error response I am getting from the code above.

error

25

My xml looks accurate but I am still getting an error response stating invalid child element “InvoiceLines”

<?xml version="1.0" encoding="UTF-8" standalone="true"?>

-<ns1:Invoice_Create xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns1="https://api.quickfile.co.uk">


-<ns1:Header>

<ns1:MessageType>Request</ns1:MessageType>

<ns1:SubmissionNumber>d3c6fab8-db69-4c26-993a-a43063bb9033</ns1:SubmissionNumber>


-<ns1:Authentication>

<ns1:AccNumber>**********</ns1:AccNumber>

<ns1:MD5Value>e9a0034f82497dcd217e92b8f6f42dc8</ns1:MD5Value>

<ns1:ApplicationID>********-48fe-4387-84b5-************</ns1:ApplicationID>

</ns1:Authentication>

</ns1:Header>


-<ns1:Body>


-<ns1:InvoiceData>

<ns1:InvoiceType>ESTIMATE</ns1:InvoiceType>

<ns1:ClientID>3721645</ns1:ClientID>


-<ns1:ClientAddress>

<ns1:Address>Unit 75 Albion Rd</ns1:Address>

<ns1:CountryISO>GB</ns1:CountryISO>

</ns1:ClientAddress>

<ns1:Currency>GBP</ns1:Currency>

<ns1:TermDays>30</ns1:TermDays>

<ns1:Language>en</ns1:Language>


-<ns1:InvoiceLines>


-<ns1:ItemLines>


-<ns1:ItemLine>

<ns1:ItemID>0</ns1:ItemID>

<ns1:ItemName>MISC</ns1:ItemName>

<ns1:ItemDescription>150mm caps</ns1:ItemDescription>

<ns1:ItemNominalCode>4000</ns1:ItemNominalCode>


-<ns1:Tax1>

<ns1:TaxName>VAT</ns1:TaxName>

<ns1:TaxPercentage>20</ns1:TaxPercentage>

<ns1:TaxAmount>20</ns1:TaxAmount>

</ns1:Tax1>

<ns1:UnitCost>100</ns1:UnitCost>

<ns1:Qty>1</ns1:Qty>

</ns1:ItemLine>

</ns1:ItemLines>

</ns1:InvoiceLines>

</ns1:InvoiceData>

</ns1:Body>

</ns1:Invoice_Create>
26

Hi @Rob

The only thing I can see missing here is the schema location in the opening tag:

xsi:schemaLocation="https://api.quickfile.co.uk https://api.quickfile.co.uk/schemas/1_2/Invoice_Create.xsd"

If you change:

<ns1:Invoice_Create xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns1="https://api.quickfile.co.uk">

to:

<ns1:Invoice_Create  xmlns="https://api.quickfile.co.uk"  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  xsi:schemaLocation="https://api.quickfile.co.uk https://api.quickfile.co.uk/schemas/1_2/Invoice_Create.xsd">

Does that change anything?

27

when mapping the xml in excel it appears the schema location gets stripped out, I’m not sure as yet how to get excel to include this, will have to do some digging.

thanks
Rob

28

Let us know how you get on. I’ve moved the related posts into another thread for now so we can keep the feature request for the request itself.

29

Having added the schemaLocation to the xml file manually it does work, therefore this appears to be the cause of the error, however I have no idea how I can get excel to include the schema location when exporting to xml, any ideas?

Rob

30

What library are you using to generate the XML?

31

I am using Excel 2016 export function, it is creating the xml which appears accurate, however when I post the file I get invalid child element errors, if I then edit the xml file in xml notepad and add the schemaLocation attribute, re-save, the updated files posts successfully without errors.

I am finding it difficult to get excel to include the SchemaLocation when exporting.

Thanks
Rob

32

I think I have now managed to get this working! I have added some code which edits the xml to include the schema location, this appears to have sorted the issue.

just a quick question, is there a character limit on the “ItemName” when I use longer names it throws up an error, no problems with shorter names however?

Thanks
Rob

33

The item name is optional and I think it’s more intended for when you are using the inventory, the description is the main field for the line detail - name has a max length of 20 characters but description allows 500.

34

Thanks for confirming this Ian, explains the reason it was kicking it back to me, got this all sorted now and although my code may be a little untidy it seems to work well.

I just need to add all our customer ID’s into a list, is there a quick way of exporting the customer id along with the customer name or do I have to go through all our customers manually?

Many thanks

Rob

35

If you run a backup of your account this information should all be in the Client_Ledger.csv in the backup zip file.

36

Perfect! thanks Ian

Regards
Rob