[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:
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?
Thanks!
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! ThisForm.GenerateEvent("LIO_EmptyPONum") Else LIO_PONumInvalid = False Try 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 ThisForm.GenerateEvent("LIO_InvalidNumPOs") ElseIf LIO_POsToGenerate > LIO_MaxPOsToGenerate Then ' Too many POs - ABORT! ThisForm.GenerateEvent("LIO_TooManyPOs") ElseIf LIO_POsToGenerate < 1 Then ' Too few POs - ABORT! ThisForm.GenerateEvent("LIO_TooFewPOs") ElseIf LIO_PONumStart_Prefix <> LIO_PONumEnd_Prefix Then ' Invalid Prefix - ABORT! ThisForm.GenerateEvent("LIO_InvalidPrefixPOs") 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 Else ' ERREUR PARAMÈTRE ! ThisForm.GenerateEvent("LIO_DebugParam") End If End If End If End Sub