API request for zones

[Domas]

I’m trying to get data via openboxes api.
I would like to get response something like this:

{
    "zones": [
        {
            "id": "zoneId1",
            "bins": [
                {
                    "id": "binId1",
                    "products": [
                        {
                            "id": "productId1",
                            "amountInBin": 4
                        },
                        {
                            "id": "productId2",
                            "amountInBin": 3
                        }
                    ]
                }
            ]
        },
        {
            "id": "zoneI2",
            "bins": [
                {
                    "id": "binId2",
                    "products": [
                        {
                            "id": "productId1",
                            "amountInBin": 4
                        },
                        {
                            "id": "productId2",
                            "amountInBin": 3
                        }
                    ]
                }
            ]
        }
    ]
}

Is it achievable? Or I will have to make multiple requests for that?

[jmiranda]

Let me look into it and get back to you. We don’t have an API that responds with that structure (i.e. where zones are the parent), but you’d easily be able to transform a list of bin locations that include the zone into a similar structure.

p.s. Apologies for the delay. Your posts were stuck in our moderation queue and I haven’t checked that in a few days.

[Domas]

Yes, I found that one with bins, but it doesn’t return products in the bin, right? I already made my custom controller for this case

[jmiranda]

I was looking to see if the Locations API had an Available Items nested resource.

It doesn’t, but should.

Allow clients to retrieve available items for a given location

So, the only way to do this (besides forking and creating your own endpoint, as it sounds like you did) would be to create a Custom Data Export and use the provided URL as a quasi-API endpoint.

Step 1. Create required document type

Create a new document type for custom data exports

Required Fields

• Name: Custom Data Export
• Document Code: DATA_EXPORT

Step 2. Write SQL query

Write a SQL query to pull the data you need and save it to a file.

select 
    product.product_code, 
    product.name as product_name, 
    location.name as location, 
    bin_location.name as bin_location, 
    inventory_item.lot_number, 
    inventory_item.expiration_date,
    product_availability.quantity_on_hand,
    product_availability.quantity_available_to_promise as quantity_available
from product_availability
join product on product.id = product_availability.product_id
join inventory_item on inventory_item.id = product_availability.inventory_item_id
join location on location.id = product_availability.location_id
left join location bin_location on bin_location.id = product_availability.bin_location_id;

Including the zone (and any other required data point) has been left as an exercise for the reader.

Step 3. Upload query

Upload your SQL query as a document using the newly created Custom Data Export document type.

Step 4. Go to custom data exports

Navigate to the Custom Data Exports page by hovering over the Reporting tab and selecting the Custom data exports menu item.

Step 5. Test the custom data export API

Export the data as CSV or JSON to test the endpoint

NOTE: This will export all available items across all of your locations so this might be a little slow.

Step 6. Get the custom data export API URL

Right-click the JSON button to copy the URL. It should look something like this.

https://<server-url>/openboxes/dataExport/render/cab2b1fa8fa99b6d018fac2efdee001d?format=json

[jmiranda]

I had to add a LIMIT to my query because my database has about 85,000 available items so the browser was crashing. But here’s what it looks like when it’s working.

From here you can add zone to the query and then have your API client transform the data (group by zone) to get it closer to the format you’re expecting. Or you could just change the query to order by Zone and then have the client iterate over all rows

Custom data export don’t currently support parameters, so there’s no way to do string interpolation like in the following query. This makes it much less useful but it works ok if you’re in a bind and aren’t concerned with hard-coding your report parameters.

select * 
from something 
where something.somethelse = ${somevalue}