API query to retrieve only records with a validation status of "approved"

User Support
1

I am using PowerBI to connect to my projects data. I would like to only use records that have a validation status of approved. In PowerBI the validation status appears as a nested record, therefore in the table it appears as "[record’ rather than having a value.

To get only “approved” records perhaps the best approach would be to add query parameters to my API call to filter only approved submissions., similar to this query for filtering by date: https://kc.humanitarianresponse.info/api/v1/data/332794?format=json&query={"_submission_time":{"$gt":“2018-12-31T10:52:19”}}

Is it possible to query only approved records and if so what would the the query call look like?

Your help is much appreciated

2

Hi, could you post some sample json, I want to see how the approved field look in the json?

3

Hi wqahmed. Thanks for replying.
It is the in-built approval/validation that appears here http://support.kobotoolbox.org/managing-projects/record-validation

Here is what the json from a record would look like (test data):

[
{
    "_notes": [],
    "enumerator_number": "0",
    "end": "2019-03-08T09:24:24.624+01:00",
    "formhub/uuid": "####",
    "start": "2019-03-07T17:32:37.946+01:00",
    "group_pop_change/Arrivals": "10",
    "group_partners/site_facilitators": "1",
    "group_pop_change/Births": "0",
    "_validation_status": {
        "by_whom": "username",
        "timestamp": 1552895046,
        "uid": "validation_status_approved",
        "color": "#00ff00",
        "label": "Approved"
    },
    "_version_": "v2RK6stTcbLtR47ifiYYVa",
    "_status": "submitted_via_web",
    "group_fs/number_of_families_that_received_food": "100",
1 Like
4

The query would work like this. Use the dot operator

query={"_validation_status.label":"Approved"}

Or to avoid spaces if you want to filter by not approved then use the uid field

query={"_validation_status.uid":"validation_status_approved"}
2 Likes