Exporting Data

Questions
1

I’m needing to export a table consisting of ID’s from other tables into a txt file.
Need to export the actual data from these other tables not the id’s – Each record needs to have a “begin” and “end” statement- I’m only getting one record. For this one client I know there are over 800 records.

function irExport(){
	var dir = scopes.file.convertToJSFile(pmsInterfaceDirectory);
	if(dir && dir.exists() && dir.isDirectory()){
		var irsFromEC = scopes.file.convertToJSFile(dir.getAbsoluteFile()+'/'+ir_Export_File_Name);
	/** @type {JSFoundset<db:/eyecor_user_data/interpretation_report>}*/
	var irs = databaseManager.getFoundSet('eyecor_user_data','interpretation_report')

	irs.sort('last_name desc')
	irs.loadAllRecords();


	var procedure = irs.interpretation_report_to_procedures
	var dr = irs.interpretation_report_to_doctors
	var patient = irs.interpretation_report_to_patients
	var str=''
		
	str += 'BEGIN\r\n';
	str += 'ID='+irs.interpretation_report_id+'\r\n';
	str += 'LName='+patient.last_name+'\r\n'
	str += 'FName='+patient.first_name+'\r\n'
	str += 'ExamDate='+utils.dateFormat(irs.examdate,DATE_FORMAT)+'\r\n'
	str += 'RecordedDate=' +utils.dateFormat(irs.record_date,DATE_FORMAT)+'\r\n'
	str += 'CPTCode='+procedure.code+'\r\n'
	str += 'CPTDescription='+procedure.description_short+'\r\n'
	str += 'ClinicalFindings='+irs.clinical_findings+'\r\n'
	str += 'ComparativeData='+irs.comparative_data+'\r\n'
	str += 'ClinicalManagement='+irs.clinical_management+'\r\n'
	str += 'DocsName='+dr.name+'\r\n'
	str += 'END\r\n';
	
	scopes.file.writeTXTFile(irsFromEC,str);
	globals.DIALOGS.showInfoDialog('IRs','Exported I and Rs from EyeCOR');
}
}

I’m running Version: 6.1.6 - build 1439
Any suggestions?
Thanks in advance
Bob

2

Hi, Bob.

You only load the foundset which defaults to the first record…this is why you are exporting one record. To export all records in the foundset, you must visit each record using a loop structure. Try using the .forEach() function with your foundset:

e.g. irs.forEach()

I hope this helps!

3

Hi Bob,

As Kim said, you need to iterate over the FoundSet to get all the records.

kwpsd:
Try using the .forEach() function with your foundset:

e.g. irs.forEach()

Sadly this iterator function is only available in the latest versions (>7.4) and Bob is still on Servoy 6.1.
Here is an example how this can be done in 6.1.

function irExport(){
    var dir = scopes.file.convertToJSFile(pmsInterfaceDirectory);
    if(dir && dir.exists() && dir.isDirectory()){
        var irsFromEC = scopes.file.convertToJSFile(dir.getAbsoluteFile() + '/' + ir_Export_File_Name);
        /** @type {JSFoundset<db:/eyecor_user_data/interpretation_report>}*/
        var irs = databaseManager.getFoundSet('eyecor_user_data', 'interpretation_report');
        var procedure;
        var dr;
        var patient;
        var irsRecord;
        var strArr=[];
        
       irs.sort('last_name desc', true); // defer the sort
       irs.loadAllRecords();

        for ( var i = 1 ; i <= irs.getSize() ; i++ ) {

            irsRecord = irs.getRecord(i);
            
            procedure = irsRecord.interpretation_report_to_procedures;
            dr = irsRecord.interpretation_report_to_doctors;
            patient = irsRecord.interpretation_report_to_patients;

            // collect all the data in an array, which is WAY faster then concatenating a string
            strArr.push('BEGIN');
            strArr.push('ID=' + irsRecord.interpretation_report_id);
            strArr.push('LName=' + patient.last_name);
            strArr.push('FName=' + patient.first_name);
            strArr.push('ExamDate=' + utils.dateFormat(irsRecord.examdate,DATE_FORMAT));
            strArr.push('RecordedDate=' + utils.dateFormat(irsRecord.record_date,DATE_FORMAT));
            strArr.push('CPTCode=' + procedure.code);
            strArr.push('CPTDescription=' + procedure.description_short);
            strArr.push('ClinicalFindings=' + irsRecord.clinical_findings);
            strArr.push('ComparativeData=' + irsRecord.comparative_data);
            strArr.push('ClinicalManagement=' + irsRecord.clinical_management);
            strArr.push('DocsName=' + dr.name);
            strArr.push('END');
            
        }
        // join the array items using the return/linefeed
        scopes.file.writeTXTFile(irsFromEC, strArr.join("\r\n") + "\r\n");
        globals.DIALOGS.showInfoDialog('IRs', 'Exported I and Rs from EyeCOR');
       
    }
}

Hope this helps.

4

Thank you Robert, with a little tweaking this worked as needed!
And Thank you Kim for responding also.

5

Update:
If there is a record that has a blank field I get an error:

TypeError: Cannot read property “name” from

How can I except all other data and leave that field blank and continue the loop?

for example - if the user did not select a doctor the dr field in this record would be blank. This is where I’m getting the error

Thanks again,
Bob

6

Nice catch, Robert! :D

Bob…I suspect you are encountering a NULL value in the field. Generically, do something like this:

var myVariable = !myFieldName ? '' : myFieldName

The example is for a string data type field. You will have to adjust the code for other data types. If myFieldName is NULL, then an empty string is returned, otherwise, the value of myFieldName is returned.

I hope this helps!

7

Hi Bob,

As Kim already said (again! :) ) you can check if it has a record using a ternary operator, like so:

strArr.push('DocsName=' + (utils.hasRecords(dr) ? dr.name : ''));

Or when it does have a record but the value is null you can add this:

strArr.push('DocsName=' + (utils.hasRecords(dr) ? (dr.name||'') : ''));

Hope this helps

8

Kim and Robert,

Worked like a charm - Thank you both once again! I appreciate you help.

Bob