Populating root endpoint object queries with fields from multiple related objects

Hi all, I'm currently setting up specific data KPIs for the store managers at various locations and I plan to update a master table for each endpoint which is then used to populate the Final User Dashboard. I've got the main queries working, mostly anyway, and I'm noticing that some of the data I need is located in various related objects in stead if the main end point,
IE: and "Order" end point doesn't offer total received amount in terms of cost or the ordered amount as far as I could tell. that data seems to be in the "Orderlines" respectively.
Conversely the "Sale" end point gives my final totals in terms of transactional dollars but no total quantities over all for the total sale. I know I can call each "saleID" and get the related "Salesline" and what not but I was hoping I could somehow add the field to the main Sale endpoint query and keep everything nice and tidy. instead of having to query and log each order line and sum up totals myself. 
Ideas or best practices would be much appreciated thanks !
Much thanks for everyone's help!
Tags:

2 comments

  • Adrian SamuelAdrian Samuel Posts: 164Moderator, Lightspeed Staff moderator
    edited October 17
    Hey @LiquidLuke, this project sounds great! What technology/library/framework are you using to build this dashboard?

    Regarding the Order endpoint you can specify the "OrderLines" relation and then manually add up the quantities from each line looping through the array of OrderLines objects. You'll get a response which will include the OrderLines.OrderLine.total- which is the total cost from all the quantities of that given line.

    OrderLines.OrderLine.quantity is the total quantity for that OrderLine.

    An example query would look something like this:

    For a group of Orders
    https://api.lightspeed.app/Account/API/{{AccountId}}/Order.json?load_relations=["OrderLines"]
    
    or for a specific Order
    
    https://api.lightspeed.app/Account/API/{{AccountId}}/Sale.json?load_relations=["SaleLines"]

    You can read more about this here:
    https://developers.lightspeedhq.com/retail/endpoints/Order/


    The Sales endpoint works similarly.

    A query to get the SaleLines of a given sale would be formatted as thus:

    For a group of Sales:
    https://api.lightspeed.app/Account/API/{{AccountId}}/Sale.json?load_relations=["SaleLines"]
    
    Or for a specific Sale:
    https://api.lightspeed.app/Account/API/{{AccountId}}/Sale/{{SaleId}}.json?load_relations=["SaleLines"]
    The Sale object and SaleLine quantities have a variety of calc{{data}} fields that you will find useful dependant on the region and tax setup the business in question finds itself in

    You can find out more about this here:
    https://developers.lightspeedhq.com/retail/endpoints/Sale/


    I hope this helps and I look forward to hearing your response!
  • LiquidLukeLiquidLuke Posts: 26Member
    Hey "Adrian Samuel", thanks for the links! Im actually trying to get a proof of concept running off of Google Sheets then using Google 
    Data Studio to create the dash boards. My plan is to then use the Google Appmaker to create apps but Im loosely debating a run at Vue.js. Not getting to do this full time its a bit of a crutch using all google products and I would like to learn to build it from the ground up but that being said theres a lot to know and we use GSuite in the company already which comes with its own baked in authentication and Apps Script for the suite of programs so We will see but these links will help to kick things off ! 

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