JS  Use SQL() to obtain multiple field values

1.1 Problem

Sql() function can be used to dynamically obtain the value of a field in the database, but if you want to obtain the value of multiple fields, you must execute multiple sql() functions, which increases the amount of server resource requests , If the query SQL is more complex, the execution efficiency will be lower.

The following is an example of obtaining the person's name, surname, and phone number by entering the person ID in the report to show how to use a sql() function with JavaScript to get the values of these three different fields.

1.2 Solution

Because of the limitation of the sql() function itself, you can only get the information of one column at a time But if you want to get multiple columns, we can actually splice multiple fields at the same time and put them together. Use special separators, such as commas, etc. in JavaScript, so that the values of different columns can be obtained separately. 

2.1 Design template

Create a new template, the template is as shown in the figure below, B2 is the text box for entering ID, C2 is the query button, D2 is the formula sql("FRDemo","SELECT empname||','||sex||','||tel  FROM employee where EMPid='"+B2+"'",1,1), and column D is set to be hidden.

2.2 Add After Editing Event

In order to obtain the modified real-time value of the B2 widget, we need to set the After Editing event, as shown below

var v=this.getValue();
contentPane.setCellValue('B'+row,null,v)

2.3 Add button click event

Add a click event to the C2 query button, as shown below

var info=contentPane.curLGP.getCellValue('D'+row);      
var infoarray=info.split(',');    
var result='';       
if(infoarray.length==3){    
        
    result+='Name: '+infoarray[0]+'\n';    
    result+='Gender: '+infoarray[1]+'\n';    
    result+='Mobile: '+infoarray[2]+'\n';    
    FR.Msg.alert('Result ',result);    
} else {    
    alert('No info.');    
}

After saving, click data entry preview and you can see the above effect.

Note: Different databases have different methods of splicing characters. For example, Sqlite and Oracle use "||". Others such as MSSQL, MySQL, etc. generally use "+" directly. In addition, separators used for splitting in SQL and JavaScript are required to be consistent.

WorkBookSQL.cpt