Prevent Report generation for too many objects so that SQL Log size doesn't explode

[CSI 9.01.01 on-premise here]

After over 6 months of random SQL Log explosions making CSI/SyteLine offline for hours several times, we've finally pinpointed one of the culprits.

It was the PO report (PurchaseOrderDetailReport) being generated for too many POs at a time: either a clueless user going directly to POReport and just clicking on Preview without selecting Start and End POs; or wanting to generate a second PO by modifying only Start or End but not both.

What I just did to prevent this: I added a FormScript Sub that performs various checks on PONum Starting and Ending values (empty PONum; difference between both; different prefixes; non-numeric last 7 digits; Ending<Starting, etc.) and displays a message box for each case in order to guide the user in fixing their error.
I plugged this script on both Preview and Print buttons to bypass the normal behaviour; if no error is detected at the end of the script, it generates the original events that are normally tied to Preview/Print buttons.
Each message box is a Prompt event handler, so I created several new events in this Form.

I also set both Starting and Ending edits as Required so that the red asterisk shows users both fields are required, even though this property does nothing here (I guess because it's checked during a Save, but not when events are generated by buttons).

Everything works fine, but it was a quick fix that does not look very pretty to me.
Things I would have liked to manage easily before going for my solution instead:

  • Get the actual number of POs that would print, according to the Starting-Ending range.
  • Make the normal event handler stop ("report a failure"?) instead of executing each remaining handler in the sequence.

I'm curious: how did you fix these issues on your end (PO range; SQL Log explosion; getting actual number of objects from a range; report a failure to stop Event Handler sequence)?

Any other ways you have or would have done this?


PS: here's the sub, in case anyone is interested:

Sub LIO_CheckNumberOfPOs(ByVal LIO_PreviewPrint As String)
' Prevent generating the report for too many POs (otherwise the SQL Logs peak to several GBs and the RunReport.exe task on Util server never finishes)

Dim LIO_MaxPOsToGenerate As Integer
LIO_MaxPOsToGenerate = 10 ' Magic number of 10 PO max is defined here only (but LIO_TooManyPOs event handler's prompt needs to be adjusted too)

Dim LIO_PONumStart As Integer
Dim LIO_PONumEnd As Integer
Dim LIO_PONumStart_Prefix As String
Dim LIO_PONumEnd_Prefix As String
Dim LIO_PONumInvalid As Boolean
Dim LIO_POsToGenerate As Integer ' This can be wrong; strong assumption is that POs have same prefix and are consecutive in numbering (which is true for 99.9% of POs since end of 2019)

If ThisForm.Variables("POStarting").IsNull Or ThisForm.Variables("POEnding").IsNull Then ' At least one PONum is empty (Start and/or End) - ABORT!

LIO_PONumInvalid = False
LIO_PONumStart = CInt(ThisForm.Variables("POStarting").Value.Substring(ThisForm.Variables("POStarting").Value.Length - 7, 7))
LIO_PONumEnd = CInt(ThisForm.Variables("POEnding").Value.Substring(ThisForm.Variables("POEnding").Value.Length - 7, 7))
Catch ex As Exception
'Don't do anything with the exception, just set invalid variable to True
LIO_PONumInvalid = True
End Try
LIO_PONumStart_Prefix = ThisForm.Variables("POStarting").Value.Substring(1,2)
LIO_PONumEnd_Prefix = ThisForm.Variables("POEnding").Value.Substring(1,2)
LIO_POsToGenerate = 1 + LIO_PONumEnd - LIO_PONumStart
'Application.ShowMessage(CStr(LIO_POsToGenerate), Mongoose.Scripting.wsMessageStyle.wsOKCancel, Mongoose.Scripting.wsMessageStyle.wsQuestion) ' DEBUG

If LIO_PONumInvalid = True And ThisForm.Variables("POStarting").Value <> ThisForm.Variables("POEnding").Value ' Invalid format (7 last digits non-numeric), allowed only if Start=End

ElseIf LIO_POsToGenerate > LIO_MaxPOsToGenerate Then ' Too many POs - ABORT!

ElseIf LIO_POsToGenerate < 1 Then ' Too few POs - ABORT!

ElseIf LIO_PONumStart_Prefix <> LIO_PONumEnd_Prefix Then ' Invalid Prefix - ABORT!

Else ' CONTINUE with original process
If LIO_PreviewPrint = "Preview" Then
ThisForm.GenerateEvent("PreviewReport") ' Original Preview Event

ElseIf LIO_PreviewPrint = "Print" Then
ThisForm.GenerateEvent("GenerateReport") 'Original Print Event

End If
End If

End If

End Sub