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
504-517-2620
