Am I correct in assuming I could create an API to do this, I have successfully created the API but being new to this I am unsure how to post the XML file say from Excel, any help would be appreciated
thanks
Rob
Hi @Rob
Do you have experience of particular coding languages? We do have a JSON option with the API which may be better.
I’m not sure if an XML file could be saved from Excel if I’m honest, but even if it could save it as an XML file it’s likely to need to be altered slightly.
.xlsx is an XML file.
I have some but limited knowledge of coding, I have developed a small VBA program which returns the values required for our estimates, ideally I want to import this into Quickfile using the Invoice_Create method.
I thought XML would be better suited to Excel? however if I can achieve what I need to do using JSON then I am open to any help at the moment.
Many thanks
Rob
I have mapped the xml schema into a worksheet and can get the data into the relevant cells, however I do need pointing in the right direction of how to send the xml to Quickfile, any help would be appreciated
Thanks
Rob
Hi @Rob
If it’s a one off, you could possibly use the sandbox and submit it there.
If you go to Account Settings >> 3rd Party Integration >> API, you can go through to the sandbox and submit an API call there directly. This comes with the bonus that it would generate the header information for you too.
Hi Matthew
Unfortunately not a one off, the XML (or JSON) worksheet will be used on a daily basis, I just need the VBA code to post to Quickfile
This Stack Overflow post looks like a good place to start, though you probably want to use application/xml as the Content-Type rather than form-urlencoded.
Thanks Ian, I have been looking at various posts in Stack Overflow, I have now managed to make a connection at least, I will keep plugging away at it.
Could anyone confirm the quickfile address to post the file to?
Many thanks
Rob
For XML files, they should be sent to:
You will need to ensure you include the relevant headers for authentication too. There’s more details (and examples of the XML) on our API site: https://api.quickfile.co.uk/
Can you confirm your json endpoint I.e would the following be correct?
https://api.quickfile.co.uk/2/Invoice_Create
Thanks
Rob
Hi @Rob
The endpoint would be: https://api.quickfile.co.uk/1_2/invoice/create
On the documentation, if you find the function you want (e.g. Invoice_Create), you can then click “JSON” and view the endpoint there.
Hope that helps!
Many thanks,
I have set the request headers in the code for Authentication etc, can I then just send the body as json or xml or do I need to include all details in the Json including header info?
The way the QuickFile API works there’s no authentication in the HTTP headers, it’s in the header section of the payload, so you do need both the Header and Body sections in the JSON.
Thank you for confirming Ian
Rob
I have reverted back to importing XML as this seems easier than converting to JSON, all now appears to be working with the exception of creating the MD5 hash, I’m not sure how to generate this from within excel or within the VBA code, I have downloaded the MD5 message-digest algorithm basMD5 and imported into a module however I am unsure how to implement this? are there specifics to how this works, i.e. must the hash be a certain number of digits? I realise it is generated from the api key, account number and submission number but not sure how to bring this together.
Any help would be much appreciated, I am almost there and quite impressed with the api capabilities.
Kindest regards
Rob
Just a general tip for pasting code as sometimes non-visible characters or tabs/spaces can be an issue so screenshot is often no good;
Use code tags (3 backticks before and after the code) like so.
```
Code goes here
on the next line etc
```
Gives;
Code goes here
on the next line etc
Or you could use https://www.pastebin.com/ if the code is particularly long and detailed.
Thanks Lurch, 1st time I have posted any code, does this work?
<script src=“https://pastebin.com/embed_js/FBeTK9Nx”></script>
or this
<?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>1</ns1:SubmissionNumber>
-<ns1:Authentication>
<ns1:AccNumber>61314*******</ns1:AccNumber>
<ns1:MD5Value>161314632567BEAE2FC-B3F6-4E8F-8 </ns1:MD5Value>
<ns1:ApplicationID>**********-************-*************-***********</ns1:ApplicationID>
</ns1:Authentication>
</ns1:Header>
-<ns1:Body>
-<ns1:InvoiceData>
<ns1:InvoiceType>ESTIMATE</ns1:InvoiceType>
<ns1:ClientID>3721645</ns1:ClientID>
<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>Window Profiles 1 side</ns1:ItemName>
<ns1:ItemDescription>Std white window profiles Paint 1 Side</ns1:ItemDescription>
<ns1:ItemNominalCode>4000</ns1:ItemNominalCode>
-<ns1:Tax1>
<ns1:TaxName>VAT</ns1:TaxName>
<ns1:TaxPercentage>20</ns1:TaxPercentage>
<ns1:TaxAmount>1.1</ns1:TaxAmount>
</ns1:Tax1>
<ns1:UnitCost>5.5</ns1:UnitCost>
<ns1:Qty>1</ns1:Qty>
</ns1:ItemLine>
-<ns1:ItemLine>
<ns1:ItemID>0</ns1:ItemID>
<ns1:ItemName>Door Profiles 1 side</ns1:ItemName>
<ns1:ItemDescription>Std white door Profiles Paint 1 side</ns1:ItemDescription>
<ns1:ItemNominalCode>4000</ns1:ItemNominalCode>
-<ns1:Tax1>
<ns1:TaxName>VAT</ns1:TaxName>
<ns1:TaxPercentage>20</ns1:TaxPercentage>
<ns1:TaxAmount>1.2</ns1:TaxAmount>
</ns1:Tax1>
<ns1:UnitCost>6</ns1:UnitCost>
<ns1:Qty>1</ns1:Qty>
</ns1:ItemLine>
</ns1:ItemLines>
</ns1:InvoiceLines>
-<ns1:Scheduling>
-<ns1:SingleInvoiceData>
<ns1:IssueDate>2019-03-20</ns1:IssueDate>
</ns1:SingleInvoiceData>
</ns1:Scheduling>
</ns1:InvoiceData>
</ns1:Body>
</ns1:Invoice_Create>