No announcement yet.

Macro to get a value from another table in Data Entry

  • Filter
  • Time
  • Show
Clear All
new posts

  • Macro to get a value from another table in Data Entry

    The macro sclGetValue is designed for use within data entry screen code to fetch a value from another table. This value can then be used to perform logic checks within the current screen or in whatever way it is needed.

    In order to use the macro it must be added to a macro library. This can be a global, source or study level macro library. Download the file and save to the appropriate directory.

    Calling the macro
    The macro can be called from any screen method. It takes 5 named parameters which pass the names of the variables defined for each value. The variables must be defined and populated prior to calling the macro. Only the variable name is passed. The parameters are as follows:
    1. dsName = character variable containing table to open - include the library name ('libname.tablename').
    2. dsField = character variable containing field name to get value from.
    3. dsWhere = character variable containing a valid SAS where clause that will return a single record. This can be constructed dynamically from values that are present on data entry screen, including header fields. For example:

      mywherecls="investigatorNumber='100' and patientNumber='10015'";

      This could be constructed dynamically using the Investigator and Patient dropdowns as follows:

      mywherecls="InvestigatorNumber='"||Investigator.selectedItem ||"' and PatientNumber='"||Patient.selectedItem||"'";
    4. dsCharVar = character variable that is declared and used to return a character value from call.
    5. dsNumVar = numeric variable that is declared and used to return numeric value from call.

    The following example illustrates how to call the macro from the screens onLoad() method when a record is loaded. It retrieves the investigator name and the patient enrollment date for the current subject from the view PVIEWS.ENROLLMENT and populates two screen variables with the values. However, it could get any value from any table in a library and perform any task required.

    /** onLoad Method **/                                                                                                                   
    %include objfile;                                                                                                                       
    _onLoad: method _self_:u:object;                                                                                                        
    link createobj;                                                                                                                         
    ** Add user code here **;                                                                                                               
    ** Declare variables to pass marcro parameters **;                                                                                      
    dcl char mydata='';      * libname.tablename to open *;                                                                                 
    dcl char myfield='';     * the feild to get *;                                                                                          
    dcl char mywherecls='';  * Where clause to find record *;                                                                               
    dcl char _retValC_='';   * variable to hold character return value *;                                                                   
    dcl num  _retValN_=.;    * variable to hold numeric return value *;                                                                     
    ** Assign values **;                                                                                                                    
    mywherecls="InvestigatorNumber='"||Investigator.selectedItem ||"' and PatientNumber='"||Patient.selectedItem||"'";                      
    ** call macro **;                                                                                                                       
    %sclGetValue(dsName=mydata, dsField=myfield, dsWhere=mywherecls, dsCharVar=_retValC_, dsNumVar=_retValN_);                              
    ** reassign field and call again to get another value **;                                                                               
    %sclGetValue(dsName=mydata, dsField=myfield, dsWhere=mywherecls, dsCharVar=_retValC_, dsNumVar=_retValN_);                              
    ** do something with values **;                                                                                                         
    if _retValN_ > . then do;                                                                                                               
          myDateField.text = _retValN_;                                                                                                     
    else do;                                                                                                                                
      ** Do something if no value returned **;                                                                                              
    if _retValC_ > '' then do;                                                                                                              
       myInvestigatorName.text = _retValC_;                                                                                                 
    else do;                                                                                                                                
       ** Do something if no value returned **;                                                                                             
       myInvestigatorName.text = 'COULD NOT FIND INVESTIGATOR';                                                                             
       dcl char rcc=messageBox({"The value was not returned"},'!');                                                                         
    Error Checking
    The macro will check for common errors and write a message to the log if any of the following conditions are found:
    • The table does not exist
    • The table cannot be accessed
    • The field referenced does not exist
    • The where clause produced 0 or more than 1 result
    Attached Files
    Last edited by Greg Wagner; 02-21-2013, 11:31 AM.