Report export fields can be skipped if forms are edited over time

[dianabarsan]

Describe the bug /api/v1/export/reports can export incorrect fields for forms that have changed over time.

To Reproduce Replicating is inconsistent, because the endpoint uses the "first" report for each form that would exported to generate the map of fields. However the "first" report is chosen based on it's uuid ("string" sorted first uuid) rather than a logical choice of "last reported" or similar.

Steps to reproduce the behavior consistently:

1. Choose one form and submit a bunch of reports.
2. Export your reports by calling: http://<host>/api/v2/export/reports?filters[search]=&filters[forms][selected][0][code]=<form_id>
3. Get the "first" report's id by using this query: http://<host>/medic/_design/medic-client/_view/reports_by_form?key=["<form_id>"]&group=false&reduce=false&limit=1
4. Edit this report in fauxton and delete one (or all) of its fields
5. Export reports again and compare the two csvs. The second export should miss all the fields that you deleted.

Steps to reproduce the behavior inconsistently:

1. Choose a form and submit a bunch of reports
2. Export the reports
3. Edit the form to add additional fields
4. Create a bunch of more reports
5. Export the reports
6. If one of the old reports is first, you should not see the new fields in the export.

Expected behavior We should see at least all fields from the current version of the form, if it still exists on the server.

Logs No logs, no errors, just missing fields.

Environment

• App: api
• Version: since this endpoint was added.

Additional context I believe we should have all of the "current" form's fields (if it still exists on the server) as column headers instead of picking a report "at random". This was not an easy option to include when this feature was first implemented, because we did not do any XML processing server-side. Now, with the generated model.xml, it's easy to get a mapping of "current" report fields.

All possible fields is not an option, because we have no schema and everything is streamed, including reading from the database, but column headers are sent first and need to be compiled beforehand.

[oyierp]

Dear Diana I have followed the steps 1,2 3, thus I have the downloaded form and "first" report's id Figuring out how to execute 4, edit this report in fauxton, especially given that our CouchDB instance is containerized, will appreciate any ideas

[dianabarsan]

@oyierp Here's some documentation about how to use fauxton: https://couchdb.apache.org/fauxton-visual-guide/index.html#intro

[oyierp]

@dianabarsan, Thank you very much, I do this very comfortably on a locally hosted CouchDb instance, tried reading for containerized instances and not made much progress, let me review, will revert

[oyierp]

@dianabarsan, I managed to access my project in Fauxton (https:///_utils), and edited the fields by adding the two additional fields, we are now set

My last homework is to access the registration data, which is not available in the report, will follow the same procedure and revert

[oyierp]

@dianabarsan, We had three forms for the project with the following form_ids: case_investigation, contact:suspected_case:create and contact:suspected_case:edit, we have made progress with the case_investigation form and we can now access the field of interest in the report

Our project team this morning want to know the number of persons screened, which I can only get from the number of persons registered using the form with id contact:suspected_case:create Running the query under Fauxton with medic database to see if the form exists, no results are displayed { “selector”: {

"form": "contact:suspected_case:create"

}

}

Will appreciate any insights, thank you

[dianabarsan]

@oyierp Please redirect this question to the forum. as it's not related to the export functionality. Thank you!