NGLP - Configure To Display PDF Completion Certificate

Overview

NGLP does not support showing a quick action for PDF Completion Certificates configured using the ENTITY_TEMPLATE table in the SysAdmin PDF Area. However, the same functionality can be achieved by configuring a quick action directly on the workflow. Include the following script in the NGLP upgrade process will migrate the configuration from an ENTITY_TEMPLATE to a PDF Quick Action.

Finding out if the client uses PDF Completion Certificates

Run the following SQL to determine if there are any PDF Completion Certificates being displayed on Learning Plan Activities. One row is returned for each Activity Completion workflow that has a PDF Completion Certificate with the name of the PDF, the name of the Workflow, the number of workflow steps in the workflow, the number of areas in which the PDF is currently displayed, and an indicator if it is visible to the practitioner.

select t.TEMPLATE_NAME, 
	w.WORKFLOW_ID, 
	WORKFLOW = w.NAME, 
	WORKFLOW_STEPS = count(distinct ws.WORKFLOW_STEP_ID), 
	AREAS = count(distinct et.AREA_ENUM),
	PRACTITIONER = case sum(case et.AREA_ENUM when 3 then 1 else 0 end) when 0 then 'Hidden' else 'Visible' end
	from ENTITY_TEMPLATE et
	join PDF_TEMPLATE t on t.PDF_TEMPLATE_ID = et.PDF_TEMPLATE_ID
	join ENTITY e on e.ENTITY_ID = et.ENTITY_ID and e.ENTITY_TYPE_ID = 2
	join WORKFLOW w on w.ENTITY_ID = et.ENTITY_ID
	join WORKFLOW_STEP ws on ws.WORKFLOW_ID = w.WORKFLOW_ID
	group by t.TEMPLATE_NAME, w.WORKFLOW_ID, w.NAME

Limitations

Configuring PDF Completion Certificates configured using Quick Actions does not mimic the legacy functionality precisely but in most cases, it will work the same.

The primary limitation is that the Script will make the PDF link visible to all users in all areas no matter what was configured in the SysAdmin area via the ENTITY_TEMPLATE table. If the PDF should not be visible in all areas, the new actions created should be manually modified so their visibility rules limit them to the roles that have access to those areas (or the script can be customized to do so). For example, if the ENTITY_TEMPLATE does not have an entry for the Practitioner Area (Area_Enum = 3), you will still need to adjust the configuration to limit the action buttons to roles other than those that can access the Practioner Area. In most cases, even if it was not displayed in all areas based on the entries in the ENTITY_TEMPLATE table, it is okay to do so anyway.

If by chance more than one PDF_TEMPLATE was configured in the ENTITY_TEMPLATE for the same entity, the script will make both of them visible via a quick action even though there was a limitation in that feature where the 2nd PDF would not be visible as the old feature did not support more than one PDF. It is unlikely this situation exists but it is noted here for reference.

What the Script Does

The script does the following:

For every distinct PDF_TEMPLATE referenced in the ENTITY_TEMPLATE table that is configured for a COMPLETED_ACTIVTY_WORKFLOW (Entitiy Type ID = 2)
It adds an “Open PDF” Quick Action (Type = 10) to every workflow step with the following properties:
- Name and Button text = “View “ + the name of the PDF Template.
- Visible on Quick Action Menu with PDF icon
- Visible only when Workflow is Completed Successfully
- Visible to everyone
If then deletes all ENTITY_TEMPLATE records for the PDF so the Quick Action does not appear twice on the LegacyLP view.

Final Notes

Once the script is run, the PDF Quick Action configuration can be further modified to take advantage of the many more flexible rules that the Workflow Step Actions can support such as making the PDF visible under more or less restrive conditions.

The Script

Include the following SQL in the upgrade process:



PRINT '------------------------------------------------------------';
PRINT FORMATMESSAGE ('%s - Started "Create PDF Actions for NGLP"',FORMAT(GETDATE(),'MM/dd/yyyy hh:mm:ss tt'));
PRINT '------------------------------------------------------------';

INSERT INTO [dbo].[WORKFLOW_STEP_ACTION]
           ([WORKFLOW_STEP_ACTION_TYPE_ID]
           ,[WORKFLOW_STEP_ID]
           ,[NAME]
           ,[GOTO_STEP_ID]
           ,[DISPLAY_ORDER]
           ,[CONTINUE_AFTER_ACTION]
           ,[BUTTON_CLASS]
           ,[VISIBILITY]
           ,[VISIBILITY_COMPARISON_RULE_ID]
           ,[BUTTON_TEXT]
           ,[FEEDBACK_MESSAGE]
           ,[NOTES]
           ,[REFRESH]
           ,[STALE_PAGE_MESSAGE]
           ,[HELP_TEXT]
           ,[CONFIRMATION_MESSAGE]
           ,[QUICK_ACTION_ICON]
           ,[VISIBLE_TO_ROLE_IDS]
           ,[SHOW_ON_WORKFLOW_POPUP]
           ,[SHOW_ON_QUICK_ACTION_MENU]
           ,[VISIBLE_WHEN_WORKFLOW_INCOMPLETE]
           ,[VISIBLE_WHEN_WORKFLOW_COMPLETE_SUCCESSFUL]
           ,[VISIBLE_WHEN_WORKFLOW_COMPLETE_UNSUCCESSFUL]
           ,[VISIBLE_ON_STEP_ACCESS_CURRENT]
           ,[VISIBLE_ON_STEP_ACCESS_TARGET]
           ,[ENFORCE_REQUIRED_FIELDS]
           ,[PDF_TEMPLATE_ID]
           ,[COMPLETION_BADGE_ACTION]
           ,[COMPLETION_BADGE_ICON]
           ,[COMPLETION_BADGE_TOOLTIP])

SELECT DISTINCT
			[WORKFLOW_STEP_ACTION_TYPE_ID] = 10
           ,ws.[WORKFLOW_STEP_ID]
           ,[NAME] = 'View ' + t.TEMPLATE_NAME
           ,[GOTO_STEP_ID] = NULL
           ,[DISPLAY_ORDER] = 1
           ,[CONTINUE_AFTER_ACTION] = 1
           ,[BUTTON_CLASS] = ''
           ,[VISIBILITY] = 'CONDITIONALLY'
           ,[VISIBILITY_COMPARISON_RULE_ID] = NULL
           ,[BUTTON_TEXT] = 'View ' + t.TEMPLATE_NAME
           ,[FEEDBACK_MESSAGE] = ''
           ,[NOTES] = 'Added via Script during NGLP upgrade on ' + cast(CURRENT_TIMESTAMP as VARCHAR(30))
           ,[REFRESH] = 'ALWAYS'
           ,[STALE_PAGE_MESSAGE] = ''
           ,[HELP_TEXT] = NULL
           ,[CONFIRMATION_MESSAGE] = NULL
           ,[QUICK_ACTION_ICON] = 'fa-file-pdf-o'
           ,[VISIBLE_TO_ROLE_IDS] = ''
           ,[SHOW_ON_WORKFLOW_POPUP] = 0
           ,[SHOW_ON_QUICK_ACTION_MENU] = 1
           ,[VISIBLE_WHEN_WORKFLOW_INCOMPLETE] = 0
           ,[VISIBLE_WHEN_WORKFLOW_COMPLETE_SUCCESSFUL] = 1
           ,[VISIBLE_WHEN_WORKFLOW_COMPLETE_UNSUCCESSFUL] = 0
           ,[VISIBLE_ON_STEP_ACCESS_CURRENT] = 0
           ,[VISIBLE_ON_STEP_ACCESS_TARGET] = 0
           ,[ENFORCE_REQUIRED_FIELDS] = 0
           ,[PDF_TEMPLATE_ID] = t.PDF_TEMPLATE_ID
           ,[COMPLETION_BADGE_ACTION] = 'NONE'
           ,[COMPLETION_BADGE_ICON] = ''
           ,[COMPLETION_BADGE_TOOLTIP] = ''

from ENTITY_TEMPLATE et
join PDF_TEMPLATE t on t.PDF_TEMPLATE_ID = et.PDF_TEMPLATE_ID
join ENTITY e on e.ENTITY_ID = et.ENTITY_ID and e.ENTITY_TYPE_ID = 2
join WORKFLOW w on w.ENTITY_ID = et.ENTITY_ID
join WORKFLOW_STEP ws on ws.WORKFLOW_ID = w.WORKFLOW_ID
PRINT FORMATMESSAGE ('Added [%d] WORKFLOW_STEP_ACTION Records to view Completion PDFs',@@ROWCOUNT);

delete et
from ENTITY_TEMPLATE et
join ENTITY e on e.ENTITY_ID = et.ENTITY_ID and e.ENTITY_TYPE_ID = 2
PRINT FORMATMESSAGE ('Deleted [%d] ENTITY_TEMPLATE Records',@@ROWCOUNT);


PRINT '------------------------------------------------------------';
PRINT FORMATMESSAGE ('%s - Completed "Create PDF Actions for NGLP"',FORMAT(GETDATE(),'MM/dd/yyyy hh:mm:ss tt'));
PRINT '------------------------------------------------------------';