Some Bugs in Frontend Export need immediate fixes

[MustafaAbdulrahmanWHO]

Fields Format and Wrong Sort of HWs Columns.xlsx

### Urgency: High Describe the bug @SamuelPetragallo @JeffDotingco Some bugs in Frontend Export that need to be fixed and/or resolved.

Brief description Some bugs in Frontend Export that need to be fixed and/or resolved including but not limited to the following:

First, please make sure that all “Number Fields” are exported as “Numbers not Texts” including (Subject-ID, Beds, Ambulances, HWs, etc.) Second, please re-sort the columns of HWs based on the standard order (i.e. Specialist, General Practitioners, Assistant Doctors, Nurses, Midwives, and finally Other HWs) to make it consistent with Backend data structure.

Steps to reproduce the issue

1. Go to 'Projects'
2. Scroll down to 'Yemen'
3. Click on 'Download' which is the "Down Arrow Button"
4. Choose Export as XLSX
5. See error: All “Number” fields are exported as “Text” including Subject-id, Geographical Coordinates, Inpatient Beds, Ambulances, all number of HWs fields, etc. Above that, the HWs columns are not sorted based on the usual standard sort of Backend Export which is: Specialist, General Practitioners, Assistant Doctors, Nurses, Midwives, and finally Other HWs (in Frontend Export have exchanged the locations of “Specialist” and “Other HWs/Other Health Staff” columns by mistake).

Expected behavior The Desired Actions are as follows: First, please make sure that all “Number Fields” are exported as “Numbers not Texts” including (Subject-ID, Beds, Ambulances, HWs, etc.) Second, please re-sort the columns of HWs based on the standard order (i.e. Specialist, General Practitioners, Assistant Doctors, Nurses, Midwives, and finally Other HWs) to make it consistent with Backend data structure.

Additional details Please notice in the below screenshot: First, all "Number Fields" are exported as "Text" (sample fields/columns are highlighted in "Orange Color") Second, the HWs are not sorted correctly as Frontend Export have exchanged the locations of “Specialist” and “Other HWs/Other Health Staff” columns by mistake (mislocated fields/columns are highlighted in "Yellow Color")

Labels Type: Bug Urgency: High Subject: Data Export

[luna404]

Regarding Excel not respecting the data types, I spoke to the developer about this, and it seems this is not supported by the library we are using to generate the Excel export. However, there is an easy solution to this within Excel itself. If you load the data using PowerQuery it should automatically detect the field type. I did a quick screen recording showing how to do this in Excel 2016. If you have an older version of Excel you might have to manually install PowerQuery Add-in. As shown in the clip, it also allows to change other field types rapidly.

Your point on the order of the questions is noted and being reviewed by the developer.

[luna404]

I reclassified this issue as being of low urgency due to the fact that for the first part of the issue there is a very simple fix available within Excel. The second part of the issue is also not considered to have a major impact of your work, as the column order does not affect data analysis in Excel or other applications.

[luna404]

I'm marking this as resolved as there haven't been any issues reported over the last 2 months

[MustafaAbdulrahmanWHO]

Dear Caroline . . Please be informed that this bug still exists and we didn't report it again and again because we were waiting for you to fix it. So, please keep this issue open till it is resolved. Thank you

[luna404]

@MustafaAbdulrahmanWHO could we clarify what the remaining issue is. If this is about having to manually adjust some of the data types in Excel then, there is currently not much we can do about this. As explained above you can easily change that using Excel or Power Query.

We are currently exploring alternatives to LimeSurvey which might make it possible in the future to improve this. However, this issue also largely due to Excel's capability of recognizing data over which we have no control

[MustafaAbdulrahmanWHO]

@luna404 Please be informed that the Frontend Export bug is not resolved yet. Moreover, we can correct some data types using power query but as we discussed earlier the DataStructure of the Frontend Export is far different (isn’t consistent with Frontend Export DataStructure) from the usual DataStructure of the Backend Export which is based on the HeRAMS' Standard Data Model (SDM) and which we already familiar with in terms of number and order of exported columns/fields.

We have already built automated products/tools based on the standard DataStructure of the Backend Export. However, due to the incapability of the Backend Admin (LimeSurvey) of the new HeRAMS platform to export a non-duplicated version of HF data (i.e. exporting only the last updated version of each HF) which is a mandate for our analysis which is based on the last updated HeRAMS data to assess the health situation in Yemen (i.e. real-time analysis availability of health resources and services in all health facilities in Yemen) and due to the limitations of the analysis available in the platform.

So, we kindly request you to consider this issue as being of high importance with "Urgency: High" and don't consider it as resolved till you either solve the problem of Frontend Export or provide a capability to the Backend Export that allows exporting only the last updated version of health facilities. Please be informed that we need to build a dynamic dashboard for showing HeRAMS 2020 assessment results and need to make an Online connection to the HeRAMS platform to update data automatically (using API), so could you please advise how can we make an online connection to only the last updated version of health facilities in the system and what do you suggest to do to solve the problem of Frontend Export while there is no capability (at least for now) to export a non-duplicated version of the HeRAMS dataset from the Backend (i.e. exporting only the last updated version of HF data). We really highly appreciate your kind support and advice.

[luna404]

consider this issue as being of high importance with "Urgency: High" and don't consider it as resolved till you either solve the problem of Frontend Export or provide a capability You have reported two issues: columns not having the same order and numeric types not exporting as numeric types. I agree that neither issues has been resolved but simple workarounds exists to cope with these issues. Hence, this is not an urgent issue to resolve. Are there any other issues with the front-end export? If yes, please give specific examples. Regarding the numeric fields, as I mentioned previously, a solution to this is in the development plan but it is depending on several other major changes that need to be implement first. Most of these are already high priority, but as it requires substantial changes it will take some time until this is can be implemented.

DataStructure of the Backend Export which is based on the HeRAMS' Standard Data Model (SDM) You are misunderstanding the purposes of the SDM. The SDM is guiding the questions, not the the fields in the database. Hence, both the backend and the frontend are different from the SDM. Also, please note that some of your requests are also affecting this differences (e.g. users not being able to edit location related questions). Again, which questions other than array questions are not in order?

We have already built automated products/tools based on the standard DataStructure of the Backend Export Can you please give some examples of the type of reports you have build and what tools you have used? In any well built automated report it should be straight forward to make changes to the schema including data types. In case you have hardcoded the question order, than you should maybe consider change this as it is generally considered bad practice. If it is not possible to make any schema changes, we could consider a local "translator" script transforming the schema of the frontend export to align with your reports. If you need help with any of this, we can discuss and see if we are able to support any of the work required to adapt your reports to the front-end export.

However, due to the incapability of the Backend Admin (LimeSurvey) of the new HeRAMS platform to export a non-duplicated version of HF data (i.e. exporting only the last updated version of each HF) This is why you should use the frontend export where this option exists!

or provide a capability to the Backend Export that allows exporting only the last updated version of health facilities This is is technically not possible and never will be!