A flaw with spreadsheets

Following is a piece which is connected to my research. I don't think that it belongs in the research proposal and I doubt whether it belongs in the thesis either. Maybe I can condense the idea into one or two sentences then shoehorn it in.

One of the failings of spreadsheets is that data which is added to the spreadsheet is not retained. What I mean is this: a clerk can create a spreadsheet directly from Priority, then add data, normally notations, to it. But if the spreadsheet has to be recreated, the notations will be lost. Following is an example.

A clerk is responsible for returning samples (chairs) which have been loaned to customers. In Priority, I created a report which lists all the outstanding samples, and the clerk would output this report to Excel, adding to each row the steps which she had taken to retrieve the samples. She would present this spreadsheet in a fortnightly meeting. For the next meeting, she would again output the updated report from Priority to Excel, and once again would have to add all her notations to the spreadsheet.

When I heard about this, I suggested something different: there is a place in Priority where she can note the actions which she has taken to retrieve each sample - instead of adding these actions to Excel where they are ephemeral, write them into Priority where they are permanent. I added this text field to the report, so now every time that we create the report, one can immediately see all the actions taken to retrieve the samples.

I suggested the same idea to another clerk who reports on long term debts. Here I wasn't successful in improving the reporting style; this might well be because the clerk tends to report on debts per customer (as opposed to the actual invoices which have yet to paid). The comments field is on the invoice level as opposed to the customer level. Writing comments on the customer level is not good because some of these are repeat customers so comments referring to one debt are likely to appear in the wrong context. An intermediate level would be projects - a customer can have several projects and one project can be connected to several invoices.

This is a problem which I have seen several times in Priority - what might be termed the granularity of data. Priority (and probably every other ERP program) stores data at an atomic level - an order, a delivery note, an invoice - whereas for reporting purposes, a higher level is required (a "molecular" level). This "molecular" level doesn't really exist - the only thing which I can think of is projects, and this isn't always applicable. Still, this is an interesting line of thought and I will continue thinking along these lines. 

As it happens, there is a divisional meeting on Sunday in which the above clerk will present the latest list of long term debts. I'll try and have a word with her after the meeting. Unfortunately, she tends to be rather fixed in her ideas and not open to changes.