Basic API Questions

LiquidLuke · August 2018

Hi there, Im very new to the coding world and even newer to API usage so please pardon my newbie questions but I've hit a point where I need some guidance. I'm writing an integration for Google Sheets and using the Apps Script editor to make the calls. here are the 2 immediate questions i have.

Q1: I am trying to retrieve a sales report of all the sales for a particular store & register. We have 5 stores each with 1 register on the account. I'm not sure how to structure the endpoints accordingly.

Q2: Can I request specific fields from each sales line only ?
I know that there is load_relations only im not entirely sure if that's the means in which to get the fields or if I use that after the sale.json? to load the [salelines]?

https://api.lightspeedapp.com/API/Account/166476/Sale?load_relations=["SaleLines.Item"]&completeTime=><,2018-07-24T00:00:00-0500,2018-08-14T00:00:00-0500

I have the Oauth2 protocol working fine Im just not finding many examples where sales are used as most use items as the endpoint. Which reminds me in many javascript object {} examples there is the term "items" used to reference the values with in an object array. Is that a valid term to use when calling the sales lines given that there are actual "items" to be considered with in the sales lines? "

I appreciate any help, references and suggestions that are offered as well as your time and attention replying to this . I have my code up on my Github should you wish to have a look. and while I've messed with it a bit its close to the most recent version, Thanks again!

Alex Lugo · August 2018

Hello @LiquidLuke,

1. You can use the shopID as your query string to only display sales from a specific store. Making a GET request to the Shop endpoint will provide the shopID of each one of your shops.

2. You can load multiple relations (Note each endpoint has unique relations). In order to load the SaleLines to the Sale endpoint use the SaleLines relations (GET /API/Account/{accountID}/Sale.json?load_relation=["SaleLines"].

LiquidLuke · August 2018

Ok but how do I load the Salelines for just the 1 register?
https://api.lightspeedapp.com/API/Account/Account ID/Shop/4/Sale.json?load_relations=["SaleLines.Item"]

Alex Lugo · August 2018

Hi @LiquidLuke,

You can use the shopID and registerID as query strings in your request.

GET /API/Account/{{accountID}}/Sale?load_relations=["SaleLines"]&shopID=2&registerID=3

LiquidLuke · August 2018

That's what I was after! In my head I was thinking to narrow down the scope before the request,like looking at the location for the info, when instead, you make the request with the filtered parameters. Thank you very much !

LiquidLuke · August 2018

So that query doesn't work it comes back:

Execution failed: Invalid argument: https://api.lightspeedapp.com/API/Account/myAccountID/Sale.json?load_relations=["SaleLines"]&shopID=4&registerID=6 (line 39, file "Code") [1.175 seconds total runtime]

ANy thoughts as to where i got it wrong? (I do have my correct Account ID in the Query)

Alex Lugo · August 2018

Hi @LiquidLuke,

The query works fine when I run it, Would it be possible to get a copy of the error message?

LiquidLuke · September 2018

That is the error i thought, again Im sorry Im new at this. I wonder if its a permission thing? I'll check the code that its returning.

LiquidLuke · September 2018

ok I got that one working, in a manner that returns data where i took out the load relations and queried by /sale.json?shopID=4 this of coarse returns a ton of data and I want to do my best to respect the limits of the bucket, so my next question are:
Q1: how do I specify the fields I want to return as I find I am usually only ever after a few specific fields?

And..

Q2: looking at the API docs I can load relations SaleLines to the Sale end point as you mentioned above and on the Sale page it's noted as a relation and then there is a returned field SaleLine type={Object}. Is the information returned from this the same as the end point Saleline? I ask only to use the SaleLine endpoint doc page as a guide to see what fields may be included so I can populate the headers in my google Sheet to match the results to.
It seems stupidly obvious but I have a habit of assuming to much so I'm asking just to be sure.
Again thank you for your time on this.

jamesratcliffe · September 2018

@LiquidLuke

Q1: You can't control which fields are returned.

Q2: Yes, you're right, you can (and should)use the SaleLine endpoint as a reference for the SaleLine objects that you load as a relation on the Sale endpoint. The SaleLines will look the same.

LiquidLuke · September 2018

Hey James, Thanks for your reply, I've made a fair amount of progress, At least on my scale of things, however one thing I'm having trouble with now is getting user profile data with custom field values included? I can get a list of possible Custom fields for any customer, or a list of those fields possible values but I can't find the right search query to get all of a customers information including the values set to custom fields for any one particular customer? This is where I've ended up but this is erroring out as invalid search? (ID Fields are properly filled out ) Any help would be great Essentially Id like to pull up a customer summary with their account data including the custom field values, eventually along with their purchase history and contact data.
/API/Account/My_Account_ID/Customer/Customer ID.json?load_relations=["CustomFieldValues"]

gregarican · September 2018

This seemed to work for me, using the URI syntax you listed. Here is a Fiddler trace example, where I pull the customer's note and custom field values.

GET https://api.merchantos.com/API/Account/{AccountId}/Customer/2504.json?load_relations=[%22Note%22,%20%22CustomFieldValues%22] HTTP/1.1

cache-control: no-cache

Postman-Token: bdb89f51-6e64-4f82-bfd4-27063edf6d8b

Authorization: Bearer {AccessToken}

User-Agent: PostmanRuntime/7.1.1

Accept: */*

Host: api.merchantos.com

cookie: __cfduid=db226f08af7bd70432115fe0872508a6f1518446657

accept-encoding: gzip, deflate

Connection: close

HTTP/1.1 200 OK

Date: Mon, 17 Sep 2018 12:00:16 GMT

Content-Type: application/json

Content-Length: 683

Connection: close

x-frame-options: SAMEORIGIN

X-XSS-Protection: 1; mode=block

X-Content-Type-Options: nosniff

X-LS-Acct-Id: 164019

X-LS-OAuth-Client-Id: 64577

X-LS-API-Bucket-Level: 1/100

X-LS-Shard-Id: 18

X-LS-API-Drip-Rate: 5

X-LS-Master-System: true

X-LS-Master-Account: false

X-LS-Master-Catalog: false

Vary: Accept-Encoding

Expect-CT: max-age=604800, report-uri="https://report-uri.cloudflare.com/cdn-cgi/beacon/expect-ct"

Server: cloudflare

CF-RAY: 45bb69582b279dd3-ORD

{"@attributes":{"count":"1"},"Customer":{"firstName":"Joe","lastName":"Schmoe","title":"","company":"","companyRegistrationNumber":"","vatNumber":"","creditAccountID":"0","customerTypeID":"0","discountID":"0","taxCategoryID":"0","Note":{"note":"This is an updated note.","isPublic":"false","timeStamp":"2018-09-05T13:45:34+00:00"},"CustomFieldValues":{"CustomFieldValue":[{"customFieldValueID":"2493","customFieldID":"1","name":"Legacy ID","type":"string","value":"700000"},{"customFieldValueID":"4041","customFieldID":"2","name":"Anniversary ","type":"date"}]},"customerID":"2504","createTime":"2018-06-03T06:29:15+00:00","timeStamp":"2018-09-05T13:45:34+00:00","archived":"false"}}

One thing I would recommend if you are looking for reporting data, that doesn't have to be near real-time. What I wound up doing was to export paginated record sets afterhours. Ported these to a local SQL DB. That way I could provide me endusers the data pot for them to draw from. Different reports, different parameters, different options, etc. they could choose. But just hitting my SQL DB. That way they wouldn't notice any performance penalties, I could introduce different angles in terms of table joins, etc.

Adrian Samuel · September 2018

@LiquidLuke you can either specify the Customer ID or hit the whole Customer endpoint and as you've shown in your query and load the CustomFieldValues/ CustomFieldValues.value relation to get the customer

For all customers showing their custom fields values
https://api.lightspeedapp.com/API/Account/{{AccountID}}/Customer/.json?load_relations=["CustomFieldValues"]

For specific customers showing their custom field values
https://api.lightspeedapp.com/API/Account/{{AccountID}}Customer/{{CustomerID}}.json?load_relations=["CustomFieldValues"]

Besides that isn't a way to query for specific Custom field values without first making a request and sorting the data yourself from there.

LiquidLuke · September 2018

@gregarican thanks for the info, I just noticed the comment today and your example reminded me about using the %22 for quotes and i tried it and it worked! @Adrian Samuel Thanks for clarifying the finer points as well and providing the links so I could double check my own. As for paginating, that is my plan. I was planning to dump the info in to a google spreadsheet each night but the custom fields for the customers I would like to have updated asap. Seeing as I cant have triggers from the events in lightspeed POS I will probably just run a scan or use a custom button and have the cashier kick off a program as mentioned in another post of mine by @Adrian Samuel.
I'm sure I'll have more questions soon thank you both for the help!

LiquidLuke · October 2018

gregarican said:

One thing I would recommend if you are looking for reporting data, that doesn't have to be near real-time. What I wound up doing was to export paginated record sets afterhours. Ported these to a local SQL DB. That way I could provide me endusers the data pot for them to draw from. Different reports, different parameters, different options, etc. they could choose. But just hitting my SQL DB. That way they wouldn't notice any performance penalties, I could introduce different angles in terms of table joins, etc.

@gregarican Or anyone who sees this before him, I'm having an issue paginating properly and using the $offset=100 option. I have the offset changing in the URL but it doesn't seem to be recognized by the API as its always returning the first 100 , heres the call im making

"https://api.lightspeedapp.com/API/Account/AcctID/Order.json&offset=300"
I had it going but rejigged my code to accept more options and somewhere along the line I seem to have forgotten whats needed. ANy thoughts?

LiquidLuke · October 2018

I caught it , I had forgotten the ? after the main URL

Basic API Questions

15 comments