Rest API request guidance.

[BigMacUK007]

Hi Guys, Just starting my low-code journey with Budibase.

Looking to build an internal app for a security company. I will have a database with all store locations in UK with postcodes. I want to be able to get auto long/lat of these post codes, then using the Police API https://data.police.uk/docs/method/crime-street/ send API requests to get the latest crime for that area.

I have successfully connected to the API via the docs, I would just appreciate any help or guidance to get me started on pulling together the above in Budibase.

Thank you in advance

[andz-bb]

hey @BigMacUK007 - welcome to Budibase 😊

I've had a play around with your idea on my end and I think I have a good way for you to pull everything together.

For the calculation of lat/long from a postcode I created a new REST datasource that queries postcodes.io with a postcode as a binding. I have this set to run on an automation when a new Location row is added. After the query runs with the newly added Postcode, the lat/long returned from the query are added to the location row.

As the homepage of my app I have a dashboard with all of my locations and a form sidebar to create more. I've included a map component here to visualise the locations available. When I click a row or a marker it takes me to the location details page. This takes the location _id as a URL parameter, which allows me to limit the page to the relevant location.

For the location details page I have a repeater block with location data filtered down to the location by _id from the URL parameter. Using this location data I can pass the latlong for that location into the Police API query. This query then populates a table of crime for that specific location, as well as the crime map below. I also have a Location details side panel with a form block for editing the location details if needed.

Hopefully this gives you some ideas of how to proceed with your app! I've attached my app export here if you would like to play around with it or see how I have done specific parts of it. If you have any questions let me know and I will try and help!

Crime-export-1677171432347.tar.gz

[BigMacUK007]

Hey @andz-bb. Brilliant. Thank you so much for taking the time to take a look at this. I will definitely take a look further.

For info, at what point in the system do I generate the police API request for the location into the crime table, Is this an automation also? Also, I assume that the automation for the long/lat request would trigger if I uploaded a larger qty of locations from csv rather than individuals as long as the automation is set up first?

Thanks again, very much appreciated - Great support from this community 👍

[BigMacUK007]

I see the api request direct from the query 👍

Just need to look at aggregation and see all the data in the table as only see the first 12 rows. Thinking maybe I could build a screen with repeater cards for each crime category by site.

[andz-bb]

@BigMacUK007 you're very welcome!

For the Police API request you just need to define the query in the Data section and then use that Query as the source of a Data Provider. The Data Provider runs the query with the bindings that I pass in. In our case this is nested within a Location provider so we can provide the latitude and longitude from that location for our police query.

Unfortunately I don't think the automations will be triggered using the standard import function in the data tab. This is because they are first added to the dev app DB and automations aren't triggered for those. As a workaround, we have a community component plugin that allows you to import a CSV file to a table in the front end of the app. Since this will run on the prod DB the automation should be triggered properly. Here's a link to the community plugin: https://github.com/mjashanks/budibase-csv-import - I've tried it out and it works for your use case 👍

Hope this helps!

[BigMacUK007]

Sorry to be a pain. Just been looking through the test you kindly shared to learn and understand. Just a couple of noob queries.

• How would I set up to see more data in the table, currently set to show 12. so unable to see all the other records.
• How and where would I do aggregations of the rest API data, ie Rest House - 100 Anti-social, 50 Violencw, etc etc. My thinking is that I would probably implement a stat card for each site. Or chart etc. But currently, all are just individual records. So when I click on the site from the list I will get a screen with 1 card per crime category with the qty for that site location and a map or charts below.
  

Also, I have some more learning to do ref the bindings I think lol

Thanks for help and advice

Ben

[BigMacUK007]

I have managed to aggregate using the transformer. However, in the app it is just using the default lat and long values regardless of what location I set, not the bindings. Any advice, do I need to add more in the transformer?

Ben

[andz-bb]

@BigMacUK007 hey Ben,

that transformer function looks good to me. For a given location you will need to pass in the lat/long values as bindings where the REST query is being used. I've annotated a screenshot here which explains the full flow of what needs to be done to get the query to return the proper data for the location you want:

Hope this clears things up for you - let me know if you have any further questions!

[BigMacUK007]

⭐️ Thank you. That works