A clutch of issues that I’d appreciate some help with, or at least to compare notes on. Partly in return, I’ve also attached a data extraction script that some might find of use.
In V3.1 –
1. In the Discrepancies screen I can filter the table on type, event, CRF etc., but try as I might I cannot filter on a date, no matter what format I try to put the date in. The default date setup used here is dd/Mmm/yyyy, e.g. 29/Sep/2011. Anyone else had this problem, or know of the magic formula required to filter on a date?
2. When I’ve uploaded rules I have missed out the Rule element, after I discovered that the system seems to default to TRUE for all rule ‘opportunities’ – i.e. if I download the XML the run statement is obligingly there with all data entry types marked as true, and the rule_action_run table also shows all the values are TRUE.
The rules fire OK on normal data entry, but when I import new data it goes in without a murmur. There does not appear to be any validation operating, in fact the feedback on the screen says that no validation checks fired even when the data includes crazy values. Anyone else had this issue?
3. Probably a silly question but clarification would be appreciated: When I run batch validation, either on a rule or a CRF nothing seems to happen. Is that because the rules have already fired on initial data entry? In other words is batch firing only of use if the data entry firings have been turned off? Or should they fire anyway? (Yes I could spend time running experiments to work it out either way, but I really don’t have the time).
4. In some situations some validation checks keep firing every time the section is re-saved. Normally if a discrepancy note is raised then if I go back to the section and re-save (e.g. after changing another value) the original discrepancy is not re-fired, which is what I’d expect. But for at least one data item the discrepancy event fires again, even though the target item has not been changed. It will just keep raising new discrepancy notes – identical except for their IDs. The 2 rules on the (date) question are against the current date (not after) and a fixed date (not before). Again, anyone had a similar issue or know of an explanation?
5. After importing data the reason for change mechanism appears to lose the plot. Given the form has been labelled by the system as complete it posts a request for a ‘reason for change’ if a value is changed. But it also posts a RFC request for data items on the same section that have neither been changed nor imported. Furthermore, attempts to furnish an RFC for these questions seems to have no effect and the RFC is not saved. But the system still requests one, and so the form becomes unsaveable. Anyone else had this issue or can provide an explanation?
And as for the script...
As much fun as XSL transforms might be (if I had the time) I have also been looking at getting data directly out of the DB. That was going OK until I also wanted to 'decode' any category values in the item_data table. The response_set table stores these as comma delimited lists so that it didn't appear very easy, until I came across a neat PostgreSQL function called regexp_split_to_table, which can take such lists and turn them into rows on the fly, and more importantly seems to transform neighbouring lists of the same size into matching rows. (OK, so I'm new to Postgres).
The attached document details the script to get a full dataset for a particular study, and the steps in constructing it. It seems to work but I can't claim much testing. As the document says it almost certainly needs refinement to take into account the statuses of the various entities, but that will take some further work. This is shared as-is, on the chance that others might want to do / already have done a similar thing. If you find any errors I'd be very happy to receive the details.
Best wishes, Mit freundlichen Grüßen, Meilleures salutations