Fafhrd on 20/2/2013 at 21:38
Oh, that's way different than I thought. I thought that they were uploading data to some sort of centralized database and you were then pulling the data into the spreadsheet from there. It being local for everybody makes things way more complicated on the end user. A custom app probably is the way to go for that.
[edit]Just for clarification: "2. They link this to a Google Drive document, which is a spreadsheet. This creates a new Sheet." So for every new entry to their database, a new sheet is created instead of a new line in the existing sheet?
SubJeff on 20/2/2013 at 22:35
Ha ha. Yeah, that's why its so hard to get right - you can't trust end users with access to the under-the-hood workings.
And no, it just adds a new line to the Sheet. You end up with a just one massive worksheet that, at present, you have to export as an Excel file or csv file that you then have to upload into the gawd-awful official program for creating reports. You can actually manually enter the details directly into this program. It's a massive ballache though because it means you have to collect all your patient data, anonymously, and then enter it into this program. With the Memento database you can do it on the fly at any time of the day.
I've been on this for ages now and I can't crack in a Speadsheet program.
The problem I'm having is I need to use conditionals to generate stuff like: count all the cardiac cases that are were emergencies. This would be much more suited to a database actually, SQL would solve all my problems.
Edit: What I need is this, essentially - =COUNTIF('Logbook Test'!D:D, "Local") AND ('Logbook Test'!J:J,"Cardiac")
Fafhrd on 21/2/2013 at 02:48
Quote Posted by Subjective Effect
Edit: What I need is this, essentially - =COUNTIF('Logbook Test'!D:D, "Local") AND ('Logbook Test'!J:J,"Cardiac")
(
https://support.google.com/drive/bin/answer.py?hl=en&answer=1388882)
=QUERY in Google Spreadsheets can do this, sort of. =QUERY(A:J, "Select [columns you want in the output separated by commas] [SQL commands]") and then =countif the output (or =countif(query(etc))). I've forgotten how to do a conditional query based on the contents of a specific cell, though I'm pretty sure it's possible. [edit]Figured it out. You have to concatenate the cell reference in, so it would be "Select A, B, C, D, E, F where (F=' "&F3&" ')"). But don't put spaces between the single and double quotes.
[edit2]Query also has a weird quirk in that it'll only treat dates numerically (i.e. if you want a >, <, =, etc) if the number format of the cells being queried (including the conditional) is 'Normal' or some form of decimal. You can format the output back to a Date format without any problems though.
SubJeff on 21/2/2013 at 10:25
Get.
Out.
:D
Excellent. More testing today!
SubJeff on 22/2/2013 at 11:47
Nope, I can't work it out.
Why has Google not got a database app?
Gaaaah.
I could convert this all to XML or JSON and run queries on it then but then that would just be for me and I'm trying to make a solution for everyone.
