User Story description:
| Anchor | ||||
|---|---|---|---|---|
|
As a Submitter (i.e., Health Authority user), I would like to be able to submit forms in batch via a file upload functionality in order to save a significant amount of time when I have to submit data for multiple facilities/operators.
This Story focuses on the file submission only. The following points describe the whole workflow for the file submission:
Once authenticated, a submitter should be able to select a form to which they have access, and then decide to submit this form either manually or, if the option is available, via file upload.
Once the file upload selected, the user can download the template for the submission
Once the user has filled out the file, they can now submit it using the “browse“ button to select the file or they can also simply drag and drop the file into the dedicated area on the screen.
Once the file pre-loaded, the user can confirm their submission by clicking the “submit“ button.
From there, the file will be parsed and validated using the validation rules defined by the form designer on each field. Every row within the file will be the equivalent of one form submission.
During the import and validation, the user is prompted with a loading screen
Once the validation completed, a message will appear on the user screen indicating the Successful file processing or the Failed file processing, in which case a validation report will be available for download. If the import was successful, then the user will see the current status of their submission to be “Review pending“
The validation report will point out the different issues found within the files (e.g. First name missing, must be provided. Or, Incorrect format for birthdate, format must be yyyy-mm-dd)
Only the section in bold is part of this User Story.
Business Rationale:
Currently, Health Authorities gather data from multiple facilities/operators to then submit this data to the Ministry of Health.
It is important for the Health Authorities to gather this data first as it feeds their own system. The current issue is that the HAs have no easy way to submit the data to the Ministry of Health. They can only do so through time consuming and painful manual form entries. As a solution they would like to have the ability to make batch submissions through a file upload functionality.
Dependencies:
[List potential dependencies with other User Stories or Tasks]
# | Task or User Story | Type of Dependency |
21 | Parent | |
56 | Parent | |
57 | FORMS-209: (Reviewer) Identify forms submissions that were part of the same file submission | Parent |
Technical Details:
Questions:
Its is an all or nothing import. If one issue is found on one record, we do not insert the other valid records.
Front-end initial checks:
We accept only .csv files (comma separated values, including the header row)
We limit the size of the file to 10MB
We control the number of columns submitted. This should match the number of columns in the template
The column name should match to a field name.
Parsing:
We do not care about the order of the columns
We read the rows of the file one by one and try to create a form submission from each of them
As we parse the data we generate the Validation report containing the different errors identified when trying to enter a value for a given field
There shouldn’t be any actual submission if 1 error is detected. We only actually create the submissions if there is no error.
We will need to agree on the Validation Report format (it will be a .csv):
What will be the columns?
Example from other application:
We could add a column to identify an “error #” just in case the user needs additional support and it would make it easier .
We could add a column to identify the “value“ of the field that threw the error
If we are able to identify a “unique identifier“ in the form, then we could also have that ID in the Validation Report to better identify the row that threw an error.
We will need to think about how to manage interruptions:
If the processing is interrupted, how do we let the user know? What type of error is generated?
Audit need?
We will need to confirm whether we should keep track of all the attempts at uploading files for Audit purposes
Solution :
Let me resume what we are going to do in simple way :
Read the form data and convert it to an csv empty .
When the user upload the data check if the column name is match to form field.
If every thing is OK we convert each row to form submission and convert them to a List of submission and transfer it to the server.
if there is an error in a field format or field missing any thing else we abort the file submission and allow the submitter to download a file with a complete report .
First to generate the empty template csv file for a specific form for the submitter , we can use this package “export-from-json” from this command :
npm i export-from-json
In the project there is function that return all fields of a form but it sends only an array with the name of the fields , So now we are going to create a new function but it will return an array of this object .
| Code Block | ||
|---|---|---|
| ||
[{
name : "fullname",
type : "string"; \\ STring | Date | number,
required: true | false,
date_format: "MM-DD-YYYY"
},
{
name : "Gender",
type : "enum | options";
required: true,
date_format: "",
value : ["M", "F"]
}
] |
We have the name of the field , its type, if it is required and if it is a date which format it should be create. After calling this function now we can create the csv file and let the user to download it .
Next when the user upload the file we must read the data from CSV file to analyse them, check if there are no errors, to do that we can use this package “Papaparse” from this command
npm install papaparse --save-dev
This package is going to separate the header to the rows , so now you can check if header is compatible to the form by testing if the field is match and the number of column is equal .
How to generate the template csv file ?
Get the form, the extract all fields of the forms, create a JSON object with them and use the package vue-json-csv to generate the csv file .
How to read the csv file ?
Create A Drag-and-Drop File uploader, catch the file use papaparse to convert the file to an object.
check if the file is a CVS , read the data with papaparse. So we will have 2 sections of data one for the header and one for the data .
https://www.papaparse.com/docs
| Code Block |
|---|
// this.file is the local csv file
Papa.parse( this.file, {
header: true,
skipEmptyLines: true,
complete: function( results ){
this.content = results;
this.parsed = true;
}.bind(this)
} ); |
Check if the header is match to the fields of the form here is a sample of code to make the check but you can use your own logic:
| Code Block | ||
|---|---|---|
| ||
var array1= ["Name", "Gender"];
var array2= ["Fullname", "Gender"];
if(array1.sort().join(',') === array2.sort().join(',')){
alert('same members');
}
else
alert('not a match'); |
Remember the fields would be an object we supose to convert it to simple array like above. If everything is OK the header now we are going to check if the rows are ok;
| Code Block | ||
|---|---|---|
| ||
var fields = [{
name : "Fullname",
type : "string"; \\ STring | Date | number,
required: true | false,
date_format: "MM-DD-YYYY"
},{
name : "Gender",
type : "enum";
required: true,
date_format: "",
value : ["M", "F"]
}
,{
name : "Date of birth",
type : "Date";
required: true,
date_format: "MM-DD-YYYY",
value : []
}
];
var headerreport = ["Fullname", "Gender", "Date of birth"];
var submissions = [][];
var rows function isKey(obj, value) {
= [return ["Marc Jeff", "M", "08-28-1991"],
["Julia Fred", "F", "02-06-1994"],
["Julia Fred", "F", "02-06-1994"],
];
var report = [];
function isKey(obj, value) {
return obj.name === value ;
}
function addReport(obj,col, text) {
let find = false;
for (let k = 0; k < report.length ; k++) {
obj.name === value ;
}
function addReport(obj,col, text) {
let find = false;
for (let k = 0; k < report.length ; k++) {
if(report[k].column === col ){
find = true;
setReportError(k,text);
break;
}
}
if (report.length==0 || !find ) {
addNewReport(obj.name, col, text);
}
}
function generateReport(obj, case, i, j) {
var col = getExcelColumnName(j);
if (obj.required && case==="" || case == undefined) {
var error = "Row "+(i+1)+" : This field is required. ";
addReport(obj,col,error);
return;
}
switch(obj.type){
case "String" :
if (typeof case !== 'string' || case instanceof String){
var error = "Row "+(i+1)+" : This field should be a string . ";
addReport(obj,col,error)
}
break;
case "Number" :
if(typeof case != 'number'){
var error = "Row "+(i+1)+" : This field should be a number. ";
addReport(obj,col,error)
return;
}
break;
case "Date" :
// to complete
if (moment(case, 'YYYY-MM-DD').format('YYYY-MM-DD') === case){
var error = "Row "+(i+1)+" : This field format is not correct. ";
addReport(obj,col,error)
}
break;
case "DateTime" :
// to complete
if (moment(case, 'YYYY-MM-DD HH:MN:SS').format('YYYY-MM-DD HH:MN:SS') === case){
var error = "Row "+(i+1)+" : This field format is not correct. ";
addReport(obj,col,error)
}
break;
case "Enum" || "Options" :
let error = true;
for(let i = 0; i< obj.value.length; i++) {
if (obj.value[i]===case) {
error = false;
break;
}
}
if (error) {
var error = "Row "+(i+1)+" : This field format is not correct.it must contain one of those "+obj.value.join(',');
addReport(obj,col,error)
}
break;
}
}
function setReportError(k, text){
report[k].error.push(text);
}
addNewReport(name, column, error){
report.push({ name, column, error: [error] });
}
function getExcelColumnName(num){
num++;
let columnLetter = "",
t;
while (num > 0) {
t = (num - 1) % 26;
columnLetter = String.fromCharCode(65 + t) + columnLetter;
num = (num - t) / 26 | 0;
}
return columnLetter || undefined;
}
downloadCsvFile() {
const data = this.json_csv.data;
const fileName = this.json_csv.file_name;
const exportType = exportFromJSON.types.csv;
if (data) exportFromJSON({ data, fileName, exportType });
}
addFile(e,type) {
if(this.file!=undefined) {
this.addNotification({message:'Sorry, you can upload only one file.',consoleError: 'Only one file can be drag.',});
return;
}
let droppedFiles = (type==0)? e.dataTransfer.files : this.$refs.file.files;
if(!droppedFiles) return;
if(droppedFiles.length>1) {
this.addNotification({message:'Sorry, you can drag only one file.',consoleError: 'Only one file can be drag.',});
return;
}
this.file = droppedFiles [0];
this.parseFile();
},
handleFile(){
if(this.file==undefined) {
this.$refs.file.click();
}
},
removeFile(file){
this.files = this.files.filter(f => {
return f != file;
});
},
parseFile(){
Papa.parse( this.file, {
header: true,
skipEmptyLines: true,
complete: function( results ){
this.content = results;
this.parsed = true;
this.generateSubmission();
}.bind(this)
});
},
generateSubmission(){
// eslint-disable-next-line no-unused-vars
this.createSubmissions(this.content).then((data)=>{
this.value = 100;
this.error = false;
this.upload_state = 10;
this.message = data.message;
this.sendSubmission();
//eslint-disable-next-line no-unused-vars
}).catch((error)=>{
this.value = 100;
if(report[k].column === col ){this.error = true;
this.message = error.message;
find = true; this.upload_state = 10;
setReportError(k,textif(error.code==500) this.generateAndShowCsvErrorFile();
});
},
break; createSubmissions(content){
return new Promise((resolve, reject) => }
{
} if (reportcontent.data.length==0 || !find ) {) reject({ message: 'Csv file is empty', code : 400 });
let entries = addNewReport(obj.name, col, textObject.entries(content.data[0]);
} } functionif generateReport(obj, case, i, j) {
var col = getExcelColumnName(j);
if (obj.required && case==="" || case == undefined) {
entries.length != this.formFields.length) reject({ code : 401, message: 'Header of this csv file is not compatible to this form.'});
var error = "Rowfor "+(let i+1)+" := This field is required. ";0; i<content.data.length; i++) {
this.value = addReport(obj,col,errorthis.pourcentage((i+1));
return;let entries }
= Object.entries(this.content.data[i]);
switch(obj.type){ for case "String" :
(let j = 0; j< entries.length; j++){
if (typeof case !== 'string' || case instanceof String){
// eslint-disable-next-line no-unused-vars
var error = "Row "+(i+1)+" : This field should be a string . "; let key = entries[j][0];
// eslint-disable-next-line no-unused-vars
addReport(obj,col,error) } let value break= entries[j][1];
case "Number" : let obj = if(typeof case != 'number'){this.fields.find(o => o.name === key);
var error = "Row "+(i+1)+" : This field should be a number. "; this.submissions[i][key] = value;
addReportthis.generateReport(obj, value,col i,error j);
return;}
}
break; if (report.length != 0){
case "Date" : // to complete
break;
case "Enum" || "Options" :reject({ code : 500, message: 'Some errors have been found please download the report csv file to see them all.' });
// to} completeelse {
// dont forget to check the value
break;resolve({ message: 'Your Bulk submission has been successful' });
} } function setReportError(k, text){ }
report[k].error.push(text});
}
function addNewReportgenerateAndShowCsvErrorFile(name, column, error)) {
report.push({ name, column, error: [error] });const new_report = []
}
function getExcelColumnName(num){
num++;
let columnLetter = "",
t;
while (num > 0) {for (let i = 0; i< this.report.length; i++){
name, column, error: [error]
new_report.push({
t =name (num - 1) % 26;: this.report[i].name,
columnLetter = String.fromCharCode(65 + t) + columnLetter;column: this.report[i].column,
num = (num - t) / 26 | 0;error: this.report[i].error.join('|'),
}
)
return columnLetter || undefined;
}
for(let i = 0; iconst <data rows.length = new_report;
i++) { forconst (letfileName j = 0; j < header.length ; j++) {= "error_report_"+moment();
const exportType = exportFromJSON.types.csv;
var obj =if fields.find(data) exportFromJSON(element) => { isKey(element,header[j]){ data, fileName, exportType });
}
);
function sendSubmission() {
const subsObj addReport(obj,= rows[i][j],i,j)];
for (let }i }= 0; if (reporti< this.submissions.length != 0; i++){
console.log("file cant upload because it contain error." subsObj.push({ data: Object.assign({}, this.submissions[i]) });
}
service.sendListSubmission(subsObj);
}
|
This snippet above allow to check and report error in the csv file. Now if there is no error to report now we are going to create submission for each row an push theme to the server .
Other notes:
Overall parsing process if submitters are uploaded a CSV file:
First we try to translate the .Csv row into a Json submission. ← Do we build the validation report based on that translation?
Once the Json submission is created for each row, we send the POST request using the CHEFS API. ← What type of error messages can the API return?
Acceptance Criteria:
[List the Acceptance Criteria]
# | Description | Notes |
1 | I can choose to submit data using file upload | The option should be offered to the user |
2 | I can browse my files or drag and drop the file I wish to upload | .csv format only. Other formats will be rejected instantly |
3 | I can click on submit and see my file being processed | loading screen while file is processed |
4 | I can view the outcome of the processing of my file either via a “File Successfully Imported“ message or via a “File Could Not be Imported“ message |
|
5 | If my file is rejected, I can download the Validation Report to consult the details of the errors |
|
Prototype/Mockups
Provide high fidelity prototype or mockoup
Out of Scope:
The setup of a file upload from the perspective of the form designer is not part of this User Story
The review of a file submission is not part of this User Story
The download of the validation report is not included in this scope
Contact(s):
This User Story is primarily built for the LTC
Table of Contents
| Table of Contents |
|---|