SQL Query for Templates and Usage in ProLaw


This is handy code to have around for a few purposes. First, I always like to validate my document templates in my upgraded test database prior to upgrading live to ensure that my templates still work. This SQL code will return all the document type names, their event classes, the form number in ProLaw, and the rules set. The code will also include counts of how many times that template has been used by date (my code is set to start the first of this year 2021). Second, I think it's good to have use counts to determine if the templates are being used.


I use a discontinued rules set to hide my no longer used document types, so I've excluded all documents from DISCONT rules set. If you don't have a discontinued rules set, don't worry, it won't affect your results.

select EventTypes.EventDesc, EventTypes.EventClass, FormRTF, EventTypeRules.RulesSet,

(select count(events.RTF) from events

left outer join EventTypes et2 on et2.EventTypes = events.EventTypes

where et2.EventTypes = EventTypes.EventTypes and events.AddingDateTime > '1/1/2021') as EventCount


from EventTypes

left outer join EventTypesDocuments on eventtypes.EventTypes = EventTypesDocuments.EventTypes

left outer join EventTypeRules on eventtypes.EventTypeRules = EventTypeRules.EventTypeRules


where EventTypesDocuments.FormRTF is not null and RulesSet <> 'DISCONT'

order by RulesSet, EventClass, EventDesc

If you have any problems running the code, adapting it to your needs, or need some enhancements in addition to what is provided above feel free to email me or call me.


Thanks

Jason@c2axis.com

504-517-2620



Featured Posts
Recent Posts