SyteLine Form Script Generator for WinStudio Forms

Option Explicit On
Option Strict On

Imports System
Imports System.Globalization
Imports Microsoft.VisualBasic
Imports Mongoose.IDO.Protocol
Imports Mongoose.Scripting

' Keep the Namespace and class header exactly as the Form Script editor generates them.
Namespace SyteLine.FormScripts
   Public Class Items
      Inherits FormScript

      Public Sub CheckItemOnHand()
         Dim oCache As IWSIDOCollection = ThisForm.PrimaryIDOCollection
         Dim sItem As String = oCache.GetCurrentObjectProperty("Item")

         If String.IsNullOrEmpty(sItem) Then
            Application.ShowMessage("No current record selected.")
            Return
         End If

         ' Double any single quote so the value cannot break the filter.
         Dim sFilter As String = String.Format("Item = '{0}'", sItem.Replace("'", "''"))

         ' IDO name, property list, filter, order by, record cap (0 = all rows)
         Dim oResp As LoadCollectionResponseData = Me.IDOClient.LoadCollection("SLItemWhses", "Whse,QtyOnHand", sFilter, "Whse", 0)

         Dim total As Decimal = 0D
         Dim qty As Decimal
         For i As Integer = 0 To oResp.Items.Count - 1
            If Decimal.TryParse(oResp.Items(i).PropertyValues(1).Value, NumberStyles.Any, CultureInfo.InvariantCulture, qty) Then
               total += qty
            End If
         Next

         ThisForm.Variables("vOnHand").Value = total.ToString(CultureInfo.InvariantCulture)
         Application.ShowMessage(String.Format("Item {0}: total on hand across warehouses is {1}", sItem, total))
      End Sub

   End Class
End Namespace

' 1. Open the form in Design Mode and open the Form Script editor; paste the Sub into the class.
' 2. Add a form variable named vOnHand (Form > Variables) if it does not exist,
'    and bind a display component to it if users should see the value.
' 3. Add a button and give it a custom event, for example CheckOnHand.
' 4. Add an event handler for that event: response type Script, method CheckItemOnHand.
'    (The wording of the response type list differs slightly between Mongoose versions.)
' 5. StdFormPredisplay runs before the primary collection has records,
'    so GetCurrentObjectProperty usually returns an empty value there.
'    Use it only for setup work, and run lookups like this one from a button or a later event.
' 6. Save, exit Design Mode, refresh the form and test with a known item.

' Replace the ThisForm.Variables line in block 1 with this when the value belongs on the record.
' This marks the row modified and refreshes bound components; the user still saves the record.
oCache.SetCurrentObjectPropertyPlusModifyRefresh("UfOnHandTotal", total.ToString(CultureInfo.InvariantCulture))

' Secondary collections on the same form are reached by index, for example:
' Dim oSec As IWSIDOCollection = ThisForm.GetSecondaryIDOCollection(2)
' Confirm the collection index on your form in Design Mode before relying on it.