Look up order by a custom field

Hello. I’m using xmlrpc to do things with orders, and the first thing I need to do is check whether an order exists, based on a custom field. We have a field named _AmazonOrderNumber and I want to query to see if a particular order number exists. How do I do that?

Thank you very much.

Unfortunately due to Custom Fields not being indexed (as they are backed by blob storage) there is no efficient way to filter a query by their value at the moment.

Hmm. So there is no way at all? I suppose this is why I could not find any sample code for such a thing. Is there any workaround? I can’t accomplish my mission without it.

If I was required to do something like that, I would set up a secondary database that would provide indexing as required, and just maintain the records in synchronization with the master system.

To that end, we do provide RestHooks which notify you on events such as contact.add, so that you can pull the related record without needing polling:

After which point you’d just need to supply a front-end to query your secondary source.

I guess I can set it up on our end to track which orders were inserted into Keap. I just find it hard to believe that we can’t access the data that’s going into the system.

Along these lines, am I able to add data to custom fields via the API?

Yes, you can absolutely modify the data stored! Some of our objects have REST-enabled endpoints, but all others can be accessed via the XMLRPC API until work is complete on enabling the full suite.

Hi @Heather_Florio, you can definitely search the Custom Fields using the XML-RPC API.

Here is an example searching the Orders table using the Legacy iSDK command.

$app->dsQuery('Job', 1, 0, [ '_AmazonOrderNumber' => '123556' ], [ 'Id', 'ContactId' ] );

Here is there link to the Job Table - Table Schema Documentation - Keap Developer Portal

What Tom is on about is that if you have a large amount of Orders it may become time consuming having to search by the Custom Field. If you are doing a lot of look ups then it would be better to cache the Orders data into a Local Database where you can run your own database queries.

Hope that helps you.

Thanks, Pav. Where can I download the Legacy iSDK?

The Legacy iSDK no longer exists. If you did find a copy it would need to be brought up-to-date for the latest versions of PHP.

You can download the latest version of the Keap PHP SDK from here: GitHub - infusionsoft/infusionsoft-php: PHP client library for the Infusionsoft API.

You will need to use the “DataService - query” function to make it work: https://github.com/infusionsoft/infusionsoft-php/blob/master/src/Infusionsoft/Api/DataService.php

API Function: xml-rpc - Keap Developer Portal

Example with the 2 extra parameters.

$app->dsQuery('Job', 1, 0, [ '_AmazonOrderNumber' => '123556' ], [ 'Id', 'ContactId' ], 'Id', true);

If you do not want to use the SDK, then you will need to construct the XML, which is more cumbersome to do.