Basic API Questions

LiquidLukeLiquidLuke Posts: 26Member
edited August 2018 in Technical Documentation
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!
Post edited by LiquidLuke on
Much thanks for everyone's help!
Tags:

15 comments

  • Alex LugoAlex Lugo Posts: 131Administrator, Lightspeed Staff moderator
    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"].
    Alex Lugo
    API Support Specialist
    Lightspeed HQ
  • LiquidLukeLiquidLuke Posts: 26Member
    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"]
    Much thanks for everyone's help!
  • Alex LugoAlex Lugo Posts: 131Administrator, Lightspeed Staff moderator
    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


    Alex Lugo
    API Support Specialist
    Lightspeed HQ
  • LiquidLukeLiquidLuke Posts: 26Member
    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 !
    Much thanks for everyone's help!
  • LiquidLukeLiquidLuke Posts: 26Member
    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)
    Much thanks for everyone's help!
  • Alex LugoAlex Lugo Posts: 131Administrator, Lightspeed Staff moderator
    Hi @LiquidLuke,

    The query works fine when I run it, Would it be possible to get a copy of the error message?
    Alex Lugo
    API Support Specialist
    Lightspeed HQ
  • LiquidLukeLiquidLuke Posts: 26Member
    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. 

    Much thanks for everyone's help!
  • LiquidLukeLiquidLuke Posts: 26Member
    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.
    Much thanks for everyone's help!
  • jamesratcliffejamesratcliffe Posts: 160Administrator, Lightspeed Staff moderator
    @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.
    James Ratcliffe
    Lightspeed HQ
  • LiquidLukeLiquidLuke Posts: 26Member
    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"]
    Much thanks for everyone's help!
  • gregaricangregarican Posts: 267Member 
    edited 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 SamuelAdrian Samuel Posts: 470Moderator, Lightspeed Staff moderator
    @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.

    Adrian Samuel

    API Integrations Consultant - Strategic Solutions

    Lightspeed HQ

  • LiquidLukeLiquidLuke Posts: 26Member
    @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!
    Much thanks for everyone's help!
  • LiquidLukeLiquidLuke Posts: 26Member

    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?
    Much thanks for everyone's help!
  • LiquidLukeLiquidLuke Posts: 26Member
    I caught it , I had forgotten the ? after the main URL 

    Much thanks for everyone's help!
Sign In or Register to comment.