[API extract help] Trying to extract all Contacts

Integrations
1

To cut a long request short my client has been using Quickfile sucessfully for a number of years, however they wish to be able to feed the raw invoice and client data out into a basic self built CRM solution (which will be written in Access2007). This is so that they can effecively market new products, do some long overdue data cleansing and attempt to get some “permissibility to contact” informtion.

The existing backups aren’t quite good enough becase

  1. Some companies have changed name and therefore Client Name doesn’t match on the Sales_Invoice.csv AND the Client_Ledger.csv.
  2. Some companies have multiple contacts - the client_ledger.csv only reflects these.

Although I’m a pretty reasonable coder in VB, VBA, Access, Java, I haven’t played with XML queries before and just need a little hand holding to tell me where I’m going wrong. Once I’ve set an extract of one or more fields from a single table, I’ll know how to replicate for other tables etc.

1.I have created a completely new quickfile instance for test. I don’t expect to have more than a couple of clients OR invoices in which to test this.

2.I’ve created my “APP” and got an AppID

3.I’ve done the API Sandbox Test - and it doesn’t work. I get an INVALID Client ID, even if I use different ClientIDs, create a Client first with a clientID etc.

<?xml version="1.0" encoding="utf-8"?>
<Invoice_Create>
  <Header>
    <MessageType>Response</MessageType>
    <SubmissionNumber>00000006</SubmissionNumber>
  </Header>
  <Body>
    <Errors>Invalid Client</Errors>
  </Body>
</Invoice_Create>

What ClientID should I be using?

Ideally I want some code to

  1. Extract the full Client table with IDs
  2. Extract all the Contact records
  3. Extract the full Invoice table with IDs (so they now link)
  4. Extract the full Item table with IDs

Finally - if anyone has any idea -

  1. How do I create the MD5 automatically based upon additional submissions
  2. Is it possible to push one of these data requests out from a tool which only runs VBA (i.e. Access).

I know this is loads of questions, but i’m sure just knowing the basics of extracting a full extract of ItemID, ItemName and NominalCodes would pretty much allow me to answer all further questions.

Many ** many** thanks,

Jon White

2

When you’re giving it a client ID, are you giving it your reference (as in the one highlighted below)

Or, are you giving it QuickFile’s ID, which can be found in the URL for that client for testing, or you can pull it from the API itself:
https://xxxxxx.quickfile.co.uk/secure/clients/clientView.aspx?cID= 123456

Edit: The reference you would need from the API can be found from a ‘Client_Search’ request. Example result would be:

 <?xml version="1.0" encoding="utf-8"?>
<Client_Search>
  <Header>
    <MessageType>Response</MessageType>
    <SubmissionNumber>**Submission Number Goes Here**</SubmissionNumber>
  </Header>
  <Body>
    <RecordsetCount>**Number of Clients Found**</RecordsetCount>
    <ReturnCount>**How many have been returned this time**</ReturnCount>
    <Record>
      <ClientID>**** THIS IS THE ID YOU NEED ****</ClientID>
      <ClientCreatedDate>12/10/2013</ClientCreatedDate>
      <CompanyName>**Company Name**</CompanyName>
3

Hi Matthew,

Thanks very much for that. I’ve been playing this morning and have made great headway. I had already worked out by finding out the quickfile ClientID or InvoiceID I could extract the details, however is there a way to pull out a list of all the ID’s - basically like a

For Each ClientID in [my account]
… Run XML to pull for that ClientID
… For Each InvoiceID in [That ClientID] within DateRange
… Run XML to pull for that InvoiceID
… Next
Next

Sorry for being a muppet, i’m only just starting to play with soap/xml driven queries!

Thanks

4

You can do this using the Client_Search method, the maximum however you can pull out is 200 records at a time.

  <Body>
      <SearchParameters>
        <ReturnCount>200</ReturnCount>
        <Offset>0</Offset>
        <OrderResultsBy>CompanyName</OrderResultsBy>
        <OrderDirection>ASC</OrderDirection>
    </SearchParameters>
  </Body>

You can set the offset parameter to 200 to get at the 200-400 range and iterate through as required.

5

Hi Glenn,

Along with yours, and Matthews help (Parker1090) , I’m starting to rock and roll.

Many sincere thanks for being there to hold my hand.

Jon

2 Likes
6

Just wanted to say, that after much playing about I’ve managed to create an Access 2010 database with extracts the entire Client and Invoice tables along with multiple contact and invoice line items.

Thanks very much for the advice.

Jon

2 Likes
7

That’s great to hear, you made light work of that :smile:

1 Like
8

Thanks… I thought so to… especially as I have never used that sort of data interface before.

9

Hi Jonnyboy. Great work. Will you be sharing the access database so others can also use (me included hehe). Very useful for mass data manipulation.