Join notes with cases

Other discussions about CSPro
Post Reply
AriSilva
Posts: 352
Joined: July 22nd, 2016, 3:55 pm

Join notes with cases

Post by AriSilva » March 23rd, 2020, 4:32 pm

I´m not an expert in SQL (very very far from that).
I was trying to read the notes attached to the variables, and finally I got it! And it worked, writing the noes contents to the WS and then to a csv file.
My query was
numeric ret = sqlquery(BRUMA_10_ENTREVISTA_DICT, entrySessions,
"select cases.key, notes.field_name, notes.record_occurrence, notes.content, notes.case_id from notes join cases on notes.case_id = cases.id");
The problem is that now I want to have access to the variables in the questionnaire, that is, I want to join the questionnaire, to be able to display, for example, the date when this questionnaire was filled, the device_id, the user_id (these are variables belonging to one of the record types, the X_record).
Is it possible?
Can you point me towards one of the examples, if you have them?
Or write a snipet simulating that?
Best
Ari

Gregory Martin
Posts: 1309
Joined: December 5th, 2011, 11:27 pm
Location: Washington, DC

Re: Join notes with cases

Post by Gregory Martin » March 24th, 2020, 10:29 am

You'll see that in the cases table there is a questionnaire column. The contents of each case are stored in that column, but you would have to parse that text to get to the record and position for the variable.

In the next version of CSPro, 7.4, the data will be broken out in tables so you query it, but for now, your best option is to access the data in CSPro using logic.

AriSilva
Posts: 352
Joined: July 22nd, 2016, 3:55 pm

Re: Join notes with cases

Post by AriSilva » March 25th, 2020, 7:06 pm

Yeah, I figured that out. I had already worked withe the questionnaire column as a whole to get the data, when I had a problem with loosing data because of a carriage return.
But I thought that you might have something under the table.
In any case, that is not an emergency, I´ll wait for the 7.4.
Best
Ari

Post Reply