Science Surf

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')

CC-BY-NC Science Surf accessed 07.11.2025