Data Format

Note: This page is obsolete and will soon be deleted.

Some report data will be submitted via email attachments. Our users rely on a mix of Excel 2007 through 2017 on Windows machines. As such, it's viable to provide them with .xlsx templates which they fill out, edit, save, and finally submit via an email attachments.

Because Excel 2007 doesn't 

Although performing thorough data validation at the system level within NiFi would be ideal, time constraints preclude this approach at the moment. Instead, we're relying on the provided Excel templates to steer users toward proper data entry to the extent possible. Users with modern versions of Excel will benefit from the validations we've entered into the Excel templates. Users relying on Excel 2007, however, will not. Such users can enter arbitrary and incorrect values into text fields which later versions of Excel protect. For instance, Excel 2017 users are only allowed to enter facility names into certain fields. Excel 2007 users can enter anything into such cells. 

To ask Excel 2007 users to manually enter facility and district names would be to ask for trouble. Typos would be inevitable. As such, we should likely provide template files pre-populated with rows containing the names of every possible facility and district. Users would then be trained to copy the template, delete the rows within it which are irrelevant to them, and use the resultant file.

It would have to be impressed upon users that every row they submit overwrites existing data in the system. Therefore, for example, they must take care to avoid submitting rows like the following:

In either of the above two cases, the user submits and potentially this overwrites data associated with somebody else's facility.

Although this approach obviously entails some risk, it allows:

A) Users to make corrections to their own facilities' data in a simple and straightforward manner.

B) Clean and simple backend logic. Rows are always inserted if their compound primary key doesn't exist and updated otherwise.

NOTE: An alternative to the above approach would be to always insert (rather that update) records submitted to the system. This would preclude users' ability to override their own entries as well as those belonging to their peers. The option is thus safer, but comes at the cost of potentially bloating the database and making it somewhat awkward to correct mistakes (A user who submitted an value of 100 which they subsequently wanted to change to 70 would have to know to submit the delta which, in this case, is -30.)