Most recently I had to add forgotten data to a SQL server database that was in use only until 2010. Script language was Cold Fusion at that time creating a visual interface to 96 well plates where proband IDs are assigned to plate positions by drop down fields.
R doesn’t have any good way for formatted data entry. Some shiny apps would be helpful but a bit overkill here. Data export into a javascript framework would be also a more professional solution while I just needed only a quick way to modify my database.
So I came up with some R code producing html code embedding javascript functions that can export the result as SQL code, so a 4 language mixup.
dataentry <- function(plate_id,city) {
# get orig IDs from database first
# position identifier A01, A02...
pos = NULL
for (i in 1:8) {
pos = c(pos, paste0( rep( LETTERS[i], 12), str_pad( 1:12 ,2,pad="0" ) ) )
}
HTML <- paste('<html>
<script>
function download(filename, text) {
var element = document.createElement("a");
element.setAttribute("href", "data:text/plain;charset=utf-8," + encodeURIComponent(text));
element.setAttribute("download", filename);
element.style.display = "none";
document.body.appendChild(element);
element.click();
document.body.removeChild(element);
}
function saveValues() {
var frm = document.getElementById("f");
var params = "";
var sample_id = ',sample_id,';
for( var i=0; i<frm.length; i++ ) {
sample_id++;
var fieldId = frm.elements[i].id;
var fieldValue = frm.elements[i].value;
if (fieldValue) params += "INSERT INTO samples (plate_id,plate_position,patient_id) VALUES (',plate_id,'," + fieldId + "," + fieldValue + ")\\n";
}
download("',plate_id,'.SQL",params);
}
</script>
<body>
<form id="f">')
# dropdowns
sel ='<option value=""></option>'
for (i in 1:dim(patients)[1]) {
sel <- paste0(sel,'<option value=',patients[i,"patient_id"],'>',patients[i,"Orig_ID"],'</option>\n')
}
for (i in pos) {
if (substr(i,2,3) == "01") HTML <- paste0(HTML,'<BR>')
HTML <- paste(HTML,'<select id=',i,'>',sel,'</select>')
}
HTML <- paste(HTML,'</form>
<br><input name="save" type="button" value="SQL" onclick="saveValues(); return false"/>
</body></html>')
sink( paste('plate.html') )
cat(HTML)
sink()
}
dataentry(725,'city')