New internal variables in SPSS label and in data download

User Support Data Management
,
1

Hello,
It seems that recently the list of internal variables (columns) and SPSS labels on KoBo download has been extended and changed in order:
…
/_notes ‘_notes’
/_status ‘_status’
…
/_submitted_by ‘_submitted_by’
/_tags ‘_tags’

Can anyone explain what they contain (and where the values come from)?
Is there any documentation (link) for this change? (Since which release?)

We would appreciate, if you extend the label / data export list that you only add variables at the END and keep the previous order because external programming does count on the given order (before KoBo changes).

Before we had, as far as we know only:
/_id ‘_id’
/_uuid ‘_uuid’
/_submission_time ‘_submission_time’
/_validation_status ‘_validation_status’

Side-note: Other internal labels are still missing as SPSS labels (from repeat groups/sheets), like
_index
_parent_table_name
_parent_index
_submission__id
_submission__uuid
_submission__submission_time
_submission__validation_status

Also variable names starting with _ are NOT allowed in SPSS.

2

Will have a look at it @wroos.

3

Thanks. Also the columns with internal variables in Table View do not fully correspond to the internal variables exported in SPSS labels (and XLS data download). But this inconsistency is not new.

1 Like
4

Hi @wroos
I believe this is a different issue from the one you indicated

If so, do you mind if we separate this one to merge it with a more similar one?

On the SPSS issues raised,

Is this only limited to SPSS or should be extended to all third party purposed downloads?

Will give more details on this cc @Josh

This was meant to capture approval status but will clarify with @jnm the mode of submission e.g. using webforms

This documents the user who submitted the record

When designing the question, there was an ability to tag these questions. This should therefore capture this information.

Let us review and get back to this Kindly see the response from @Josh

Stephane

5

Hi @wroos the unofficial release notes for these changes can be found here:

This was a partial release only in production on OCHA thus far and will be included in the full release next week. The new default exported columns are included in this release and I apologize for the order change.

Regarding where _notes and _tags come from, they can be set on submissions through the /api/v1 are from legacy KoBoCAT features and have just not been part of the exports until now. We’ve begun including them in preparation for shutting down legacy exports: there is not a direct replacement for per-submission notes and tags, but we did not want anyone to lose access to anything they may have entered using those legacy features. [edit by @jnm]

With the upcoming changes, you will be able to filter which columns you want included in your export so you can just exclude all those that are irrelevant for your needs.

3 Likes
6

What do you mean by “correspond”? We do not intend or desire at this time for the table view to be an exact duplicate of the XLS exports—for example, the table view obviously has no provision for multiple worksheets and thus anything in the XLS export for linking parent rows to repeating-group child rows is completely irrelevant—but if you are claiming that something like _id in the table view does not match _id in the XLS export, then that is a significant bug.

1 Like
7

Right, thanks. Are any of these underscore-prefixed fields even useful at all in SPSS exports? If so, how are you using them?

1 Like
8

That’s certainly a reasonable request, and as Josh said, we’re sorry for causing trouble with your integrations. That said, I’m uneasy guaranteeing that we’ll never, ever change the order of existing fields or insert something new between existing fields. Is there some way we can help you make your external programming less brittle?

2 Likes
9

Hello,
Thanks! We don’t ask for guarantee, only wanted to suggest avoiding resorting as far as possible.
Now it is like this:

Table View
Validation status start end … … (data) … …
username deviceid today __version__ _id _uuid _submission_time _submitted_by

Download
start end today username deviceid audit … … (data) … …
_id _notes _status _submission_time _submitted_by _tags _uuid _validation_status _index

Kind regards - and, of course, THANKS for the new release :slightly_smiling_face:

10

Yes, e.g. you will use _index and _parent_index to join the repeat levels.
And others are interesting for monitoring, QA and analysis, like submission_time, submitted_by, _validation_status.

We also like to use _parent_index to link repeats in the Excel sheets when we specify QA/cleaning issues in Excel (e.g. all members of a household to the household level).
And _id or __uuid is helpful for us to validate that all repeat joins went well.

11

It would be helpful to have __version__ (see table view) also in the data download. This would make it easier to check for data issues with form changes (see community topics).

But our issue was more about the SPSS labels (sorry, not well explained, I updated the post now). We would like that all exported (internal) variables from the XLS data download are included also in the SPSS label download and have a naming (in export) corresponding to SPSS syntax. So, also for the repeats as their data will be imported to SPSS.

12

Hello @stephanealoo,
Thanks. Feel free to separate parts as extra topic.

But there was/is already_validation_status** in the download?
It seems to be something else, we see values: like “submitted_via_web”.

13

_status is a really old thing from KoBoCAT, and I can only be 99% sure of exactly what it does without ploughing a whole bunch of time into reading code. Here’s my conclusion, until proven otherwise, backed up by checking the values in the database:

I don’t see any other values being used for _status.

2 Likes
14

@wroos, we’ve reordered the fields on OCHA so that the older stuff now exports in the familiar order and new additions come at the end. The change should be live now.

3 Likes