• 0 Vote(s) - 0 Average
  • 5
  • 4
  • 3
  • 2
  • 1
Thread Modes

UDF to pull SR Attachments
#1
I told a couple of folks that I would post this whenever we got the code working correctly so here it is.

Background:  We have a process in Service Apps with two Operation Approval steps, but the Rejection step in SA will not let you send an email nor add document attachments. Because of this (the bug has been reported) we were forced to code around it.  Because there may have been previous documents added to the SR, we wanted to be able to pinpoint documents that were attached via the Accept/Reject step only.  

Process:  Pull attachment information from a questionnaire and insert the links into an email. This process will pull all links from a questionnaire without adding a loop to your workflow or running a business rule to grab the information.   
The text of the UDF is attached

 
Add field to SD_REQUEST table (Optional)
New field name - E_SR_TEXT
Table name - SD_REQUEST
Data type - NVARCHAR (MAX)
No Default value
Script - alter table SD_REQUEST
add E_SR_TEXT NVARCHAR (MAX) NULL

Conditional step to check if there are document attachments (skip the UDF if no attachments)
select DOCUMENT_ID from SD_QUESTION_RESULT  
where QUESTION_ID in (248, 249, 250) AND REQUEST_ID IN (@ID@)

Used in a workflow, either set the result to a variable or set to the custom field:
update SD_REQUEST
set E_SR_TEXT=  (Select [50004].E_SR_ATT_CD_REJECT (@@ID@@))
where REQUEST_ID = @@ID@@
The result provides links to the Service Request document attachments in correct HTML format.  

The UDF can be updated and used to pull from any questionnaire by changing the QUESTION_ID in the below line:
AND qr.QUESTION_ID In (248,249,250)

jhendrix, proud to be a member of EV CONNECT FORUM since Apr 2016.






Users browsing this thread: 1 Guest(s)