Has anyone implemented the 3rd RTF queue?
We’re trying, but we don’t have all our locations in the 2nd queue, and existing hold requests now appear to be stuck in the 2nd queue rather than moving on. This is even though we have “When no items in Secondary RTF, transfer immediately” checked.
If we cancel/reactivate the request, it will immediately jump to the 3rd queue.
I can’t speak to the 3rd RTF queue, but something similar happened to us when we migrated our holds. A large number of migrated holds were similarly “stuck” in their existing RTF queue because the had already passed the configured promotion term… It seems the “transfer immediately” configuration is only applied when the hold is placed/activated, so existing holds aren’t affected…
The only solution I was able to come up with was to identify the “stuck” holds and manually reactivate each of them… It was… tedious… I want to say there were ~2K holds I had to touch? I’m a little fuzzy on my exact workflow, but I think I had a the holds open in a SQL viewer from our previous ILS that I was just copying IDs (either Patron or Item IDs?) from and cancelling/re-opening each hold… ran through a few hundred at a time and knocked it out in a couple days… It was a real pain, but without a way to perform any bulk actions on holds (which, honestly, we probably shouldn’t be making bulk changes to holds under normal circumstances…) that’s what I had to do…
I did a bunch of investigation in the Polaris database before concluding that I would need to make manual changes. It’s a bit of a mess and you’d have to tweak LibraryGroupID to match your local OrganizationIDs, but if it’s any help, here’s that query, where I was looking for active Holds trapped in RTFCyle =1
SELECT TOP (1000) [SysHoldRequestID]
,[PatronID]
,[PickupBranchID]
--,[SysHoldStatusID]
,(SELECT [Description] FROM [SysHoldStatuses] WHERE [SysHoldStatusID] = r.[SysHoldStatusID]) AS HoldStatus
--,[RTFCyclesPrimary]
,[CreationDate]
--,[ItemBarcode]
,[BibliographicRecordID]
,COUNT([SysHoldRequestID]) OVER(PARTITION BY [BibliographicRecordID]) AS HoldCount
--,[TrappingItemRecordID]
,[Origin]
,[CreatorID]
--,[ModifierID]
--,[ModificationDate]
--,[RTFCyclesSecondary]
,[RTFCycle]
--,[PrimaryRandomStartSequence]
--,[SecondaryRandomStartSequence]
--,[ItemLevelHold]
--,[ItemLevelHoldItemRecordID]
--,[PrimaryRTFBeginDate]
--,[PrimaryRTFEndDate]
--,[SecondaryRTFBeginDate]
--,[SecondaryRTFEndDate]
--,[NotSuppliedReasonCodeID]
--,[RTFCyclesTertiary]
--,[TertiaryRTFBeginDate]
--,[TertiaryRTFEndDate]
--,[TertiaryRandomStartSequence]
--Update local Library counts according to changes in subquery
,c.RoanokeCityAvailableCount
,c.RoanokeCountyAvailableCount
,c.BotetourtAvailableCount
,c.SalemAvailableCount
,c.TotalAvailable
,c.AvailableBranches
--Start from Holds table
FROM [SysHoldRequests] AS r
--Join item availability counts to determine if there are items that could fill the hold at its current RTFCycle
INNER JOIN (
SELECT [AssociatedBibRecordID]
--Calculate Library totals (MUST BE UPDATED FOR OTHER SYSTEMS)
,SUM(CASE WHEN [LibraryGroupID] = 2 THEN 1 ELSE 0 END) AS [RoanokeCityAvailableCount]
,SUM(CASE WHEN [LibraryGroupID] = 13 THEN 1 ELSE 0 END) AS [RoanokeCountyAvailableCount]
,SUM(CASE WHEN [LibraryGroupID] = 21 THEN 1 ELSE 0 END) AS [BotetourtAvailableCount]
,SUM(CASE WHEN [LibraryGroupID] = 27 THEN 1 ELSE 0 END) AS [SalemAvailableCount]
,COUNT(*) AS TotalAvailable
,STRING_AGG(CAST([Branch] AS VARCHAR(MAX)), ', ') AS AvailableBranches
FROM (SELECT [ItemRecordID]
,[AssociatedBibRecordID]
,[AssignedBranchID]
,(SELECT [Name] FROM [Organizations] WHERE [OrganizationID] = i.[AssignedBranchID]) AS [Branch]
--Get Library OrganizationID for Branch
,(SELECT [ParentOrganizationID] FROM [Organizations] WHERE [OrganizationID] =i.[AssignedBranchID]) AS [LibraryGroupID]
--Get Name for the Library for ease of use
,(SELECT [Name] FROM [Organizations] WHERE [OrganizationID] = (SELECT [ParentOrganizationID] FROM [Organizations] WHERE [OrganizationID] =i.[AssignedBranchID])) AS [LibraryGroup]
,[CheckInDate]
FROM [CircItemRecords] AS i
WHERE [ItemStatusID] = 1 --Available Items
AND [RecordStatusID] = 1 --Final Records
AND [Holdable] = 1 --Holdable flag enabled
AND [HoldableByBranch] = 0 --Branch hold restriction NOT enabled
AND [HoldableByLibrary] = 0 --Library hold restriction NOT enabled
AND [NonCirculating] = 0 --Item NOT noncirculating
) AS i
GROUP BY [AssociatedBibRecordID]
) AS c
ON c.[AssociatedBibRecordID] = r.[BibliographicRecordID]
WHERE [RTFCycle] = 1 --Current RTFCycle
AND [SysHoldStatusID] = 3 --Active holds
AND [ItemBarcode] IS NULL --Not copy holds
ORDER BY COUNT([SysHoldRequestID]) OVER(PARTITION BY [BibliographicRecordID])
Again… pardon the mess… this was one of my earlier queries in the Polaris DB, so I was really just poking around in the dark… and trying to make a transition for My SQL to T-SQL… though I still tend to just comment out columns I’m not using instead of removing them from my query so that I don’t have to look them up again later if I decide I need to add them back in… Not sure if this will be at all useful, but I figure it doesn’t hurt to post it anyway. I did try to comment things a bit to help with readability, but if you have questions, feel free to ask!
Thanks for the information. That’s what we’re doing, but of course, we have 100k+ of them, so we’re coding up something to do it for us using the same SQL stored procedures.
In case someone else stumbles on this and finds themselves in a similar position of needing to cancel/uncancel, don’t forget that you may have enabled settings so a cancelled hold loses its place in the queue. You’ll want to consider adjusting that before canceling and reactivating the holds.
Note: You might also be able to achieve this “jump to the next queue” by manipulating other elements of the hold request and re-running some of the RTF processes. But we felt in our situation canceling and reactivating the individual requests was the most reliable method in our system.
Yikes! Yeah, I wouldn’t want to run through that many holds manually… Sounds like you’ve got it under control, but best of luck to you anyway!