API extension (?parameter?) to allow 'labels' to be returned not just 'names'

[nat]

What is the general goal of the feature?
To allow API users (e.g. those pulling KoBo data into Power BI) to be able to directly access the values by label (in the specified language) rather than by name.

This is a frequently-requested item with multiple forum posts. There are several workarounds listed (e.g. make forms public and do a join, make submissions public (!!!), use a separate connector, or export as csv etc.) but ideally it would be more intuitive for a user to just have a flag/parameter to be able to specify that they want labels to be returned with their data.

Some workarounds - e.g. connecting to csv format via API rather than JSON format - only work for forms without repeat groups, so need to make sure the repeat group use case is also catered for.

What are the most likely user stories for how and when this would be used by someone on your team?

Anyone connecting KoBo and Power BI - or connecting to the KoBo API for any reason.

Can you sketch out graphically how you think this should look/work in practice?
Flag on API call, similar to how users can specify format for Excel export via the UI.

NOTE it may just be that this exists somehow and it’s just that additional documentation in support site is required!

How useful would this feature be to other users or organizations?
Very

What can you contribute to making this feature a reality?
Testing support

Thanks a lot, here are some references:

API to GET Kobo submissions using 'label' rather than value

I am using the API to return submitted forms from Kobo from: https://kf.kobotoolbox.org/assets/xxx/submissions/ My question is how do I return the actual text form response (called ‘label’ in the API) rather that the ‘Value’ in the Kobo form (called ‘name’ in the API) These ‘Values’ are numbers and I cannot interpret them. I see there are some lookup values (list_name, $kuid) but I’m hoping there is an easy way to just retrieve the text rather than having to write code to loop through all the …

Deleted data from the server still popping up Excel Power Query

Hi @iker.montes you are able to access the labels if the form is shared but not the data. You can see the various translated labels at the endpoint: curl -X 'https://[kpi]/api/v2/assets/{asset_uid}/?format=json' The translated labels are nested as follows: “content” -> “choices” -> “label”

Get label and hint from Rest API

I’m working in a custom report, in other app, but I want to get the label and hint for show the answers cuter, in this moment I just get the value and name of fields, but the name doesn’t have a good format for final user. How I get the label and hint in a Rest API?

Simple Connector for Power BI (Excel Power Query) to get labels from Kobotoolbox Data

Hi all, I have created a Power BI Template that connects a KoboToolbox Form to Power BI in a simple way. I think that it is simpler waythat the one HERE, I never managed to make it work…Like others I always got a Json Problem) The file is HERE It will get you the data with LABEL (Human Readable) in the DEFAULT language of your form. The data needs to be public for this to work. Thus you need to go to your form and go to SETTINGS–>SHARING [image] Select: Anyone can view submissions made to…

KoBo API examples, using new KPI endpoints

This is copied from some private correspondence I had a while ago. Perhaps it’s useful to someone in this format, until more official documentation is developed for the new API In writing “new,” I’m contrasting KPI to the old KoBoCAT (/api/v1) API. KoBo API Examples I’ve used curl to demonstrate these API calls. You’ll notice that all my curl commands begin with curl --silent --user jnm_api:some-secret-phrase --header 'Accept: application/json' and end by being piped to python -m…

Pull data from Kobo to Power BI

That database would be the xls download export file. See attached. Testa.xlsx (82.9 KB) Please note that I linked Kobo to Power BI using Postman (i found an article on the forum here). However, the choices are not populating in Power BI, only the xml values. I would prefer not having to manually download the file, change the headers to english, and then import to Kobo. Is real time not an option?

Connecting Power BI to KoBo

Hi @bob Have you gone through the entire trail that was shared by @iker.montes ? The trail fully explains the process which picks up the labels as opposed to the values. Stephane

[wroos]

Hello @nat ,
Might using Excel download with Label option (and language choice) a workaround?
Kind regards

[iker.montes]

But then you cannot query from an external software (excel power query , power Bi , tableau…) we use this type of things more and more, rather than a "static " file that we need to download everytime we need a refresh

[nat]

@wroos

Might using Excel download with Label option (and language choice) a workaround?

Unfortunately it’s not - need an automated integration process, as some of our dashboards get updated 6 times a day. The API is great for connecting to, just a few tweaks (such as the above) are needed to make it more usable in a streamlined manner

Cheers and many thanks @wroos and @iker.montes

[Josh]

Hi @nat, thank you for the feature request. How would you suggest the response object look with the labels? Unfortunately labels aren’t unique, may be in multiple languages, contain invalid JSON characters, etc. and therefore cannot be used as keys in the JSON response.

[nat]

Thanks @Josh - yes agree some complexities with certain languages and non-uniqueness. Re multiple languages, I would just go with one (specified as a param) rather than trying to return all languages at the time.

Acknowledge what you’ve said above, and also that the labels can be verbose - an in-between step that would massively help would be to at least have a flag to include/exclude groups from the ‘name’ key.
Could then have one API call to get the big list of name-label mappings for a form (by language), then use that data to map to the separate API call to get the submission data with names.

At the moment if I’m trying to map the labels to the names returned in submissions I have to figure out complicated logic to split out the groups (several of my forms have complicated repeat groups going on).

Hope that makes sense and thanks a lot.

[wroos]

Dear @nat ,
You surely know that you can get a full list (per language) with SPSS Label export from Toolbox which seems usable for parsing.
Kind regards

[nat]

Thanks @wroos, yes can get a manual list of names-labels via the UI also by just exporting the form itself. However there’s still not a 1:1 match between the names list and the data returned in submissions API due to the groups being also included with names. A number of us are looking to get these details programmatically rather than manual file download via UI. Cheers!

[wroos]

Understood. Just thought that labels and variable names normally should be stable (after tested form design), so even transfering once might be sufficient - different to the dynamic data.
Have a comfortable week-end

[nat]

@nat

an in-between step that would massively help would be to at least have a flag to include/exclude groups from the ‘name’ key.

This in-between step (allowing user to specify whether or not groups appear in the API response) would also help with the situation below. @Josh should I log a separate Suggestion Box item for the groups/no groups flag do you think?

All columns do not appear when connecting to powerbi

@wroos sorry for my slow response - agree starting with a list of all the column names could be useful - however in the case of JSON vs csv source in PBI, as the JSON needs to be expanded in order to access groups/repeats then there’d be a bit of manual work to figure out that logic. Also, the JSON always returns groups in the field names, can’t find a param to turn that off. @tomisin do you have any repeats in your data? As far as I’m aware, csv is great for scenarios where there are no repe…

[Josh]

Hi @nat, you can log a separate suggestion for this. In a sense these are both about making the JSON API more flexible but it would be helpful to discover what flexibility is desired.

[nat]

Thanks, have logged!

API - assets (submission data) - flag to omit 'Group' from the JSON response key

Spin off from What is the general goal of the feature? To streamline ability for API users to easily import data into Power BI/PowerQuery and similar tools, as they will no longer have to put in code to extract the “name” of each field without including the “group”. At the moment, if a user calls API (v1 or v2) to get submissions data (https://[kpi-url]/api/v2/assets/{asset_uid}/data/ (access submitted data), e.g. https://kobo.humanitarianresponse.info/api/v2/assets/a6krNsaSnJFJDM9BgMSU3r/da…

[nat]

Noting that the new-ish Synchronous Exports functionality sorts out a lot of these problems! Thanks!

Can configure the export to show the labels, and to ignore (or include) the groups. Can also include/exclude questions from the results - which is great when wanting to leave sensitive data out and have it not even come to PBI.

https://support.kobotoolbox.org/synchronous_exports.html