Use the Before Show Event to Alter a Label's Value

1. Select the Label user_id_assign_by in the Project Explorer.
2. In the Properties window click on the Data tab.
3. Select Text for the Data Type property.
4. In the Properties window click on the Events tab.
5. Right-click on Before Show event and select Add Code.... .

   CodeCharge Studio should then automatically switch to Code view.
   
   

   

6. Once in Code view, replace the following text:

   <!--- write your own code here   --->

   with the following:

   <CFIF blnEditModeTasks>
     <CF_CCToSQL Value= "#flduser_id_assign_by#" Type= "#ccsInteger#" >
     <CF_CCDLookUp Field="emp_name" Table="employees" Where="emp_id=#CCToSQL#" Connection="IntranetDB">
   <CFSET flduser_id_assign_by=CCDLookUp>
   <CFELSE>
     <CF_CCToSQL Value="#Session.UserID#" Type="#ccsInteger#" >
     <CF_CCDLookUp Field="emp_name" Table="employees" Where="emp_id=#CCToSQL#" Connection="IntranetDB">
   <CFSET flduser_id_assign_by=CCDLookUp>
   </CFIF>

   
   

   The above code consists of the following elements:

   • blnEditModeTasks - form variable, which specifies if the record is being edited. Depending on the value of this property, the program displays either the name of the person who originally submitted the task (Edit mode), or the person who is currently submitting the task (Insert mode).
   • user_id_assign_by - the name of the Label within the Grid, and at the same time the name of the database field that was used to create this Label and which is now its data source.
   • flduser_id_assign_by - the name of the variable used by the generated program to refer to the Label and to the corresponding database. CodeCharge Studio constructs variable names by adding the "fld" prefix to field's name.
   • CCDLookup -CodeCharge custom tag that supports retrieving a database value based on field name, table name, and a condition. Here, this function retrieves the Employee Name emp_name from the employees table using the condition that the key emp_id equals the current value of the Label.
   • CCToSQL - CodeCharge custom tag that converts a value into the format supported by the database. This function requires a parameter that tells it if a value should be converted to a number (Integer) or text. In this case, this function converts the current Label value to a number that can be used with CCDLookup function. It is advisable to always use this function together with CCDLookup.
   • IntranetDB - the name of the database connection that you want to use in the CCDLookup function.
   • Session.UserID - the session variable that contains the ID of the user that is currently logged in.
   
   

   The whole code reads approximately as follows:

   • If a record is being edited:
     Assign the name of the person who originally submitted the issue to the user_id_assign_by Label, by looking up employee's name from employees table using CCDLookup custom tag that uses the IntranetDB connection and the value of the user_id_assign_by Label.
   • If a new record is being created:
     Assign current user to the user_id_assign_by Label by retrieving his/her name from employees table using CCDLookup custom tag that uses IntranetDB connection and CCGetUserID custom tag that obtains current user's ID.

Next: Add a Hidden "Assigned By" Field to Auto-Update New Tasks