SQL queries in formulas

This code sample shows how to include a custom query in a formula that will return results in a dataset. Parameter binding is used to speed performance and abstract datatypes. This example would be added to the BeforeAdd event for a service request in the Workflow Manager. It checks the bulk pickup date on a service request detail page that the agency has added, and shows a message with the count of scheduled pickups.

'This formula will query and return the number of service requests
		currently scheduled for bulk pickup. It is an example to show the ability to
		bind and query the database values needed from the DB directly

Dim res as Result = Result.Success 
	 'Declare a parameter collection to bind the query to 
		Dim params as DBParameterCollection = new DBParameterCollection() 
		
'This is a sample date to use for the query. Typically, this would
		  be pulled off of the service request 
		Dim sampleDateStart as new DateTime(2017, 7, 13) 
		Dim sampleDateEnd as new DateTime(2017, 7, 13, 23, 59, 59) 
		
'Add the sample date to the query parameter collection. This allows
		  it to bind using ADO.NET
		params.Add( new DBParameter( "@PickupDateStart", sampleDateStart )
		  ) 
		params.Add( new DBParameter( "@PickupDateEnd", sampleDateEnd) ) 
		params.Add( new DBParameter( "@RequestTypeBulk", "ACCIDENT-EMPLOYE"
		  ) ) 
		params.Add( new DBParameter( "@RequestTypeRG", "RG" )) 
		params.Add( new DBParameter( "@Resolved" , "N" )) 
		
'Execute the custom SQL statement. This will bind the parameters and
		return the data in a DataSet object 
	  
Dim sql = "SELECT COUNT(*) FROM CRMDETAILPAGES.ACCIDENTDETAILS AS
		ACCIDENTDETAILS INNER JOIN CRM.CUSTPROB AS CUSTPROB ON ACCIDENTDETAILS.SERVNO =
		CUSTPROB.SERVNO AND ACCIDENTDETAILS.SERVNO = CUSTPROB.SERVNO INNER JOIN
		CRM.PROBDEFN AS PROBDEFN ON CUSTPROB.PROB = PROBDEFN.PROBKEY WHERE
		(PROBDEFN.PROBCODE = @RequestTypeBulk OR PROBDEFN.PROBCODE = @RequestTypeRG)
		AND (CUSTPROB.RESFLAG = @Resolved) AND (ACCIDENTDETAILS.BULKPUDATE >=
		@PickupDateStart) AND (ACCIDENTDETAILS.BULKPUDATE <=
		@PickupDateEnd)"

'Create a dataset and execute the associated sql statement 
	 
Dim data as DataSet 
		res = DataServices.ExecSQL(
		  oServiceRequest.UserInfo.UsersDefaultProvider, sql, params, data) 
		
'Is this a successful query? If so, then perform additional processing
		on the result set. Information will be in the data.Tables[0].Rows collection 
	 
if res.IsSuccess andalso data.Tables.Count > 0 andalso
		data.Tables(0).Rows.Count > 0 then 
	 
'Get the number and return it 
		dim currentPickupRequest as Integer 
		currentPickupRequest = data.Tables(0).Rows(0).ItemArray(0) 
		res = new Result(0, ResultSeverity.UserError, "Number of scheduled
		  pickups = " & currentPickupRequest) 
		
end if 
	 
return res