Messaging and/or reporting for patrons updated by age
With the new option to update patron codes based on age criteria (which we're excited about!), it would be useful to be able to send notices to patrons whose account type was auto-updated and/or to pull a report we could use for sending out messaging to the impacted patrons in our newsletter platform.
-
Phil Agnew
commented
So, there is actually a TransactionSubType for "Modified via patron aging job" (336) that is associated with the "Patron registration modified" TransactionType (2003)... Seems like someone was already expecting someone would be interested in tracking/reporting on this! I don't know where you stand with SQL, but you can plug this into the SQL Search for Patron Records and create a Patron Record Set with the results:
SELECT [PatronID]
FROM [PolarisTransactions].[Polaris].[TransactionHeaders] AS hLEFT JOIN (SELECT [TransactionID], [numValue] AS [PatronID]
FROM [PolarisTransactions].[Polaris].[TransactionDetails]
WHERE [TransactionSubTypeID] = 6
) AS d1
ON d1.[TransactionID] = h.[TransactionID]LEFT JOIN (SELECT [TransactionID], [numValue] AS [PatronAssignedBranchID]
FROM [PolarisTransactions].[Polaris].[TransactionDetails]
WHERE [TransactionSubTypeID] = 123
) AS d2
ON d2.[TransactionID] = h.[TransactionID]INNER JOIN (SELECT [TransactionID], [numValue] AS [AgingBoolean]
FROM [PolarisTransactions].[Polaris].[TransactionDetails]
WHERE [TransactionSubTypeID] = 336
AND [numValue] = 1
) AS d3
ON d3.[TransactionID] = h.[TransactionID]WHERE [TransactionTypeID] = 2003
AND [PatronAssignedBranchID] IN (BranchIDs)Replace the BranchIDs with whatever branches you want or remove that AND statement altogether and you should be able to get what you're looking for. Of course, if you're comfortable digging into the SQL a little more, it's not difficult to connect data from the PatronRegistration table to get a real report. But a standard report delivered via SSRS for staff that aren't comfortable with SQL would still be nice.