SO Invoice Line Addition

SOLVED

I have a good idea of how I picture this script working, never dealt with UDT's but from previous post it looks like the same rules.  I am just not positive on how to "loop" through the SO Invoice lines to find the value on the UDT in order to enter a value from the UDT to the SO Invoice lines.  Situation; we have a UDT_ArtCharges with columns SO#, Customer Name, ART_TOTAL.  When a SO is invoiced through "SO Invoice Data Entry" I want to loop through the lines to see if any of the lines contain a miscellaneous item code of "/ART" (user should add this in SO entry but sometimes doesn't), if no lines contain "/ART" I want to pull up the UDT and find that SO number being invoiced in the UDT and add a line to the invoice with the corresponding "ART_TOTAL" for that SO number.  The other feature I would like is to add a "yes" or "no" option before adding the line to the invoice, something else I haven't played with yet.  I've complied a mess of code from past post, which part should I focus on first to clean it up.


sCompany=oSession.CompanyCode
If oSession.Updating > 0 or oSession.StartProgram <> "SO_INVOICE_UI" THEN 

Exit Sub

	if sCompany = "ROI" Then

	retVal = 0
	invoiceType  = ""
	retVal = oHeaderObj.GetValue("InvoiceType$", invoiceType)

		If invoiceType = "IN" Then

		strTemp = ""
		Set oLines = oBusObj.AsObject(oBusObj.Lines)

		retVal = oLines.MoveFirst()

		Do While Not(cBool(oLines.EOF))
    		retVal = oLines.GetValue("ItemCode$", strTemp)
    		retVal = oSession.AsObject(oSession.UI).MessageBox(strTemp)
   		retVal = oLines.MoveNext()
		Loop

			If strTemp <> "/ART" then

			Set oMyObj = oSession.AsObject(oSession.GetObject("CM_UDTMaint_bus","CI_UDT_ArtCharges"))

			retVal = oMyObj.Find(SalesOrder)

			retVal = 0
			order = ""

			retVal = oBusObJ.GetValue("SalesOrderNo$", order)

				If retVal = order then

				retVal =0
				oArt = ""

				oArt = oSession.GetObject("CM_UDTMaint_bus", "CI_UDT_ArtCharges")
				retVal = oBusObj.AddLine()
				retVal = oBusObj.SetValue("ItemCode$", "/ART")
				retVal = oBusObj.SetValue("ItemType$", "5")
				retVal = oBusObj.SetValue("Ordered$", "1")
				retVal = oBusObj.SetValue("UnitPrice$", "UDT_ART_TOTAL")
				retVal = oBusObj.Write()
				r = oScript.ActivateProcedure("*ALL*")
					If retVal<>0 Then
					oScript.LinesAdded = 1

    					End If
    				End If
    			End If
    		End If
    	End If

  • 0
    SUGGESTED

    Something like this should get you on the right track.  I don't know the structure of your CI_UDT_ArtChanges UDT so I just guessed the field names that would hold the sales order number and art total (assuming you are getting the art total from the UDT even though your script was using a hardcoded string).  You need to replace C h r with Chr because the forum doesn't like the function in code.  This code uses an experimental method of mine to create the object handle as a child collection object so you don't have to constantly drop and get a new handle every time the script is triggered.  I would also suggest assigning this script to the SO_InvoiceHeader's Pre-Write or Pre-Total event as that is where I think it is most appropriate based on your post.

    aValidCompanies = Array("ROI")
    aValidStartPrograms = Array("SO_Invoice_UI")
    aValidInvoiceTypes = Array("IN")
    sItemCodeToFind = "/ART"
    If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then
    	If oSession.Updating = 0 Then
    		If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then
    			sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType
    			If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then
    				sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo
    				If sSalesOrderNo <> "" Then
    					Set oLines = oSession.AsObject(oBusObj.Lines)
    					oLines.MoveFirst
    					bItemCodeToFindLineExists = False
    					Do Until CBool(oLines.Eof)
    						If Not(bItemCodeToFindLineExists) Then
    							sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode
    							If UCase(sItemCode) = UCase(sItemCodeToFind) Then
    								bItemCodeToFindLineExists = True
    							End If
    						End If
    						oLines.MoveNext
    					Loop
    					If Not(bItemCodeToFindLineExists) Then
    						bAddLine = False
    						If oSession.UI = 0 Then
    							bAddLine = True
    						Else
    							sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?", "Style=YesNo, Icon=?")
    							If UCase(sRetVal) = UCase("Yes") Then
    								bAddLine = True
    							End If
    						End If
    						If bAddLine Then
    							If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ArtCharges")) = 0 Then
    								nCI_UDT_ArtCharges_Svc = 0 : nCI_UDT_ArtCharges_Svc = oSession.GetObject("CM_UDT_Svc", "CI_UDT_ArtCharges")
    								If nCI_UDT_ArtCharges_Svc <> 0 Then
    									oSession.DropObject nCI_UDT_ArtCharges_Svc
    									nCI_UDT_ArtCharges_Svc_ChildCollection = 0 : nCI_UDT_ArtCharges_Svc_ChildCollection = oBusObj.InitChildCollection("SalesOrderNo_ArtCharges", "CM_UDT_Svc", "SalesOrderNo$", "kPrimary", "CI_UDT_ArtCharges")
    								End If
    							End If
    							If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ArtCharges")) > 0 Then
    								nSalesOrderNo_ArtCharges = 0 : nSalesOrderNo_ArtCharges = oBusObj.GetChildHandle("SalesOrderNo_ArtCharges")
    								If nSalesOrderNo_ArtCharges <> 0 Then 
    									Set oSalesOrderNo_ArtCharges = oSession.AsObject(nSalesOrderNo_ArtCharges)
    									nRetVal = 0 : nRetVal = oBusObj.ReadAdditional("SalesOrderNo_ArtCharges")
    									If nRetVal <> 0 Then 
    										sUDF_SalesOrderNo = "" : oSalesOrderNo_ArtCharges.GetValue "UDF_SalesOrderNo$", sUDF_SalesOrderNo
    										If bArgDebugPrint Then oScript.DebugPrint "sUDF_SalesOrderNo: " & sUDF_SalesOrderNo
    										If UCase(sSalesOrderNo) = UCase(sUDF_SalesOrderNo) Then
    											nUDF_ArtTotal = 0 : oSalesOrderNo_ArtCharges.GetValue "UDF_ArtTotal", nUDF_ArtTotal
    											If bArgDebugPrint Then oScript.DebugPrint "nUDF_ArtTotal: " & nUDF_ArtTotal
    											nRetVal = 0 : nRetVal = oLines.AddLine()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.AddLine(): " & nRetVal
    											nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind)
    											oLines.SetValue "QuantityOrdered", 1
    											oLines.SetValue "QuantityShipped", 1
    											oLines.SetValue "UnitPrice", nUDF_ArtTotal
    											nRetVal = 0 : nRetVal = oLines.Write()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.Write(): " & nRetVal
    										End If
    									End If
    									Set oSalesOrderNo_ArtCharges = Nothing
    								End If
    							End If
    						End If
    					End If
    				End If
    			End If
    		End If
    	End If
    End If

    If you have issues with the above code, here is my other method to get an object handle once and allow it to be reused by multiple iterations of the script.

    aValidCompanies = Array("ROI")
    aValidStartPrograms = Array("SO_Invoice_UI")
    aValidInvoiceTypes = Array("IN")
    sItemCodeToFind = "/ART"
    If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then
    	If oSession.Updating = 0 Then
    		If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then
    			sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType
    			If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then
    				sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo
    				If sSalesOrderNo <> "" Then
    					Set oLines = oSession.AsObject(oBusObj.Lines)
    					oLines.MoveFirst
    					bItemCodeToFindLineExists = False
    					Do Until CBool(oLines.Eof)
    						If Not(bItemCodeToFindLineExists) Then
    							sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode
    							If UCase(sItemCode) = UCase(sItemCodeToFind) Then
    								bItemCodeToFindLineExists = True
    							End If
    						End If
    						oLines.MoveNext
    					Loop
    					If Not(bItemCodeToFindLineExists) Then
    						bAddLine = False
    						If oSession.UI = 0 Then
    							bAddLine = True
    						Else
    							sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?", "Style=YesNo, Icon=?")
    							If UCase(sRetVal) = UCase("Yes") Then
    								bAddLine = True
    							End If
    						End If
    						If bAddLine Then
    							nCI_UDT_ArtCharges_Svc = 0 : oScript.GetStorageVar "nCI_UDT_ArtCharges_Svc", nCI_UDT_ArtCharges_Svc 
    							If IsNumeric(nCI_UDT_ArtCharges_Svc) Then 
    								nCI_UDT_ArtCharges_Svc = CLng(nCI_UDT_ArtCharges_Svc) 
    							Else 
    								nCI_UDT_ArtCharges_Svc = 0
    							End If
    							If nCI_UDT_ArtCharges_Svc = 0 Then
    								nCI_UDT_ArtCharges_Svc = 0 : nCI_UDT_ArtCharges_Svc = oSession.GetObject("CM_UDT_Svc", "CI_UDT_ArtCharges")
    								If nCI_UDT_ArtCharges_Svc <> 0 Then
    									oScript.SetStorageVar "nCI_UDT_ArtCharges_Svc", nCI_UDT_ArtCharges_Svc
    								End If
    							End If
    							If nCI_UDT_ArtCharges_Svc <> 0 Then
    								Set oCI_UDT_ArtCharges_Svc = oSession.AsObject(nCI_UDT_ArtCharges_Svc)
    								nRetVal = 0 : nRetVal = oCI_UDT_ArtCharges_Svc.Find(sSalesOrderNo)
    								If nRetVal <> 0 Then
    									nUDF_ArtTotal = 0 : oCI_UDT_ArtCharges_Svc.GetValue "UDF_ArtTotal", nUDF_ArtTotal
    									nRetVal = 0 : nRetVal = oLines.AddLine()
    									nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind)
    									oLines.SetValue "QuantityOrdered", 1
    									oLines.SetValue "QuantityShipped", 1
    									oLines.SetValue "UnitPrice", nUDF_ArtTotal
    									nRetVal = 0 : nRetVal = oLines.Write()
    								End If
    								Set oCI_UDT_ArtCharges_Svc = Nothing
    							End If
    						End If
    					End If
    				End If
    			End If
    		End If
    	End If
    End If

    The script uses arrays at the beginning to define multiple valid values for the company code, start program, and invoice type in case you need to expand it in the future.

    It uses a single string variable to hold the item code you are looking for.

    Your script didn't really handle locating the line correctly because you were setting strTemp every time in your loop of the lines and only checked it against "/ART" AFTER the loop, so whatever line was read last would be in your strTemp variable.  The code I posted uses a boolean variable to track whether or not the line was found during the loop and it is set to True only if a line is found where the item code matches the value in the sItemCodeToFind variable.  After the loop, the boolean variable is evaluated to determine if it needs to bother with the UDT.

    Indentation goes a long way towards readability so you want to make sure your code is indented appropriately to make it easier to identify logical branches.

    I like to put criteria evaluated by If statements on their own line when possible instead of combining them so I separated the checks against oSession.Updating and oSession.StartProgram.

    I don't like to use Exit Sub because multiple scripts assigned to the same business object, event, and with the same priority get compiled into a single subroutine.  So using Exit Sub will cause it to exit the entire subroutine and you may have subsequent logic that doesn't execute because of this.  This is another case where proper If statements and indentation go a long way.

    Because of the sensitivity to data types in Sage 100's scripting feature (string and numeric) when interacting with the BOI methods, it is a common practice to prefix the variable with a character to indicate the intended data type (n = numeric, s = string) and you need to make sure to initialize the variable with a default value matching its intended data type before using it with a BOI method because the BOI methods do not support VBScript's Variant data type which is the default for new declared variables.  For this reason, I like to initialize the variable at the beginning of the line as seen in my code above.  I then use VBScript's compound line indicating character, a colon (":"), to perform the action that interacts with the variable.  This makes sure every variable is initialized before being used and in the case of large scripts, eliminates the chance of forgetting to initialize a variable at the beginning of the script.  In the case of variables repeated, it also makes sure they are reset to a default value if evaluating them is important.

    EDIT: Corrected a few issues with both code examples around 11pm central on 4/27/2022 so if you grabbed the code samples before then, you should grab the latest revisions.

  • 0 in reply to David Speck

    I corrected a few issues with both code examples in my original reply around 11pm central on 4/27/2022 so if you grabbed the code samples before then, you should grab the latest revisions.  I am posting this separate reply because I'm not sure if edits trigger an automated email to people who have posted in the thread.

  • 0 in reply to David Speck

    I should have played around with the UDT a little more to get a handle on it.  My first example was hypothetical, however I've created my UDT and ran some test imports, working fine.  So as I go through the code and replace with my correct field names, I am trying to understand what the field "SalesOrderNo_ArtCharges" should be, if I am following correctly this would be my primary key field in the UDT?  Then I get lost again when the code goes to the "UDF_SalesOrderNo" and I am assuming this would just be a UDF from the UDT like the UDF_ArtTotal.

    I was replacing "SalesOrderNo_ArtCharges" with my primary key "ART_ORDERNO" which is the SalesOrderNo$ from Sage that I am doing the lookup on, so would the "UDF_SalesOrderNo" change as well?  Trying to do as much work as I can without just asking for every answer.

  • 0 in reply to jland47

    "SalesOrderNo_ArtCharges" is the name I used for the child data source being linked between the sales order number and the UDT.  I named it this way so it stood out because there is another child data source that Sage 100 creates called "SalesOrderNo".  So this value should not be changed unless all references to it are changed to the same thing.  This value is what shows up when calling the GetDataSources() method and is used by name in the GetChildHandle() and ReadAdditional() methods.

    If "ART_ORDERNO" is the primary key in your UDT, then you would need to replace the "UDF_SalesOrderNo$" in the GetValue with "UDF_ART_ORDERNO$"

  • 0 in reply to David Speck

    I've tried both examples of script and inserted all my correct fields.  The lookup for the "/ART" item code works, however if the sales order # is not in the UDT I still get the pop up message.  Should this criteria be moved down in the code after validating that the SO# is in the UDT?  I need the message to pop up after the validation that the SO# on the invoice matches a SO# in the UDT.  If the SO# is in the UDT and I selected the "Yes" option on the message box, the script doesn't add the line.  I am thinking the UDT is not getting called up or looked at correctly.  This is your same script with my correct field names.  If you see any issues with my changes before I start to  move lines around.

    aValidCompanies = Array("ROI")
    aValidStartPrograms = Array("SO_Invoice_UI")
    aValidInvoiceTypes = Array("IN")
    sItemCodeToFind = "/ART"
    If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then
    	If oSession.Updating = 0 Then
    		If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then
    			sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType
    			If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then
    				sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo
    				If sSalesOrderNo <> "" Then
    					Set oLines = oSession.AsObject(oBusObj.Lines)
    					oLines.MoveFirst
    					bItemCodeToFindLineExists = False
    					Do Until CBool(oLines.Eof)
    						If Not(bItemCodeToFindLineExists) Then
    							sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode
    							If UCase(sItemCode) = UCase(sItemCodeToFind) Then
    								bItemCodeToFindLineExists = True
    							End If
    						End If
    						oLines.MoveNext
    					Loop
    					If Not(bItemCodeToFindLineExists) Then
    						bAddLine = False
    						If oSession.UI = 0 Then
    							bAddLine = True
    						Else
    							sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?",	"Style=YesNo, Icon=?")
    							If UCase(sRetVal) = UCase("Yes") Then
    								bAddLine = True
    							End If
    						End If
    						If bAddLine Then
    							If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) = 0 Then
    								nSO_UDT_ARTCHARGES_Svc = 0 : nSO_UDT_ARTCHARGES_Svc = oSession.GetObject("CM_UDT_Svc", "SO_UDT_ARTCHARGES")
    								If nSO_UDT_ARTCHARGES_Svc <> 0 Then
    									oSession.DropObject nSO_UDT_ARTCHARGES_Svc
    									nSO_UDT_ARTCHARGES_Svc_ChildCollection = 0 : nSO_UDT_ARTCHARGES_Svc_ChildCollection = oBusObj.InitChildCollection("SalesOrderNo_ARTCHARGES", "CM_UDT_Svc", "SalesOrderNo$", "kPrimary", "SO_UDT_ARTCHARGES")
    								End If
    							End If
    							If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) > 0 Then
    								nSalesOrderNo_ARTCHARGES = 0 : nSalesOrderNo_ARTCHARGES = oBusObj.GetChildHandle("SalesOrderNo_ARTCHARGES")
    								If nSalesOrderNo_ARTCHARGES <> 0 Then
    									Set oSalesOrderNo_ARTCHARGES = oSession.AsObject(nSalesOrderNo_ARTCHARGES)
    									nRetVal = 0 : nRetVal = oBusObj.ReadAdditional("SalesOrderNo_ARTCHARGES")
    									If nRetVal <> 0 Then
    										sUDF_SalesOrderNo = "" : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_ORDERNO$", sUDF_SalesOrderNo
    										If bArgDebugPrint Then oScript.DebugPrint "sUDF_SalesOrderNo: " & sUDF_SalesOrderNo
    										If UCase(sSalesOrderNo) = UCase(sUDF_SalesOrderNo) Then
    											nUDF_ArtTotal = 0 : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_CHARGE", nUDF_ArtTotal
    											If bArgDebugPrint Then oScript.DebugPrint "nUDF_ArtTotal: " & nUDF_ArtTotal
    											nRetVal = 0 : nRetVal = oLines.AddLine()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.AddLine(): " & nRetVal
    											nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind)
    											oLines.SetValue "QuantityOrdered", 1
    											oLines.SetValue "QuantityShipped", 1
    											oLines.SetValue "UnitPrice", nUDF_ArtTotal
    											nRetVal = 0 : nRetVal = oLines.Write()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.Write(): " & nRetVal
    										End If
    									End If
    									Set oSalesOrderNo_ARTCHARGES = Nothing
    								End If
    							End If
    						End If
    					End If
    				End If
    			End If
    		End If
    	End If
    End If

  • 0 in reply to jland47

    Try this.  Same warning regarding the Chr function.  Also, in your original post, your UDT reference began with CI but in your post above, it began with SO.  Make sure you are using the correct full name of the UDT.

    aValidCompanies = Array("ROI")
    aValidStartPrograms = Array("SO_Invoice_UI")
    aValidInvoiceTypes = Array("IN")
    sItemCodeToFind = "/ART"
    If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then
    	If oSession.Updating = 0 Then
    		If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then
    			sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType
    			If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then
    				sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo
    				If sSalesOrderNo <> "" Then
    					Set oLines = oSession.AsObject(oBusObj.Lines)
    					oLines.MoveFirst
    					bItemCodeToFindLineExists = False
    					Do Until CBool(oLines.Eof)
    						If Not(bItemCodeToFindLineExists) Then
    							sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode
    							If UCase(sItemCode) = UCase(sItemCodeToFind) Then
    								bItemCodeToFindLineExists = True
    							End If
    						End If
    						oLines.MoveNext
    					Loop
    					If Not(bItemCodeToFindLineExists) Then
    						If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) = 0 Then
    							nSO_UDT_ARTCHARGES_Svc = 0 : nSO_UDT_ARTCHARGES_Svc = oSession.GetObject("CM_UDT_Svc", "SO_UDT_ARTCHARGES")
    							If nSO_UDT_ARTCHARGES_Svc <> 0 Then
    								oSession.DropObject nSO_UDT_ARTCHARGES_Svc
    								nSO_UDT_ARTCHARGES_Svc_ChildCollection = 0 : nSO_UDT_ARTCHARGES_Svc_ChildCollection = oBusObj.InitChildCollection("SalesOrderNo_ARTCHARGES", "CM_UDT_Svc", "SalesOrderNo$", "kPrimary", "SO_UDT_ARTCHARGES")
    							End If
    						End If
    						If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) > 0 Then
    							nSalesOrderNo_ARTCHARGES = 0 : nSalesOrderNo_ARTCHARGES = oBusObj.GetChildHandle("SalesOrderNo_ARTCHARGES")
    							If nSalesOrderNo_ARTCHARGES <> 0 Then
    								Set oSalesOrderNo_ARTCHARGES = oSession.AsObject(nSalesOrderNo_ARTCHARGES)
    								nRetVal = 0 : nRetVal = oBusObj.ReadAdditional("SalesOrderNo_ARTCHARGES")
    								If nRetVal <> 0 Then
    									sUDF_Art_OrderNo = "" : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_ORDERNO$", sUDF_Art_OrderNo
    									If bArgDebugPrint Then oScript.DebugPrint "sUDF_Art_OrderNo: " & sUDF_Art_OrderNo
    									If UCase(sSalesOrderNo) = UCase(sUDF_Art_OrderNo) Then
    										bAddLine = False
    										If oSession.UI = 0 Then
    											bAddLine = True
    										Else
    											sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?",	"Style=YesNo, Icon=?")
    											If UCase(sRetVal) = UCase("Yes") Then
    												bAddLine = True
    											End If
    										End If
    										If bAddLine Then
    											nUDF_Art_Charge = 0 : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_CHARGE", nUDF_Art_Charge
    											If bArgDebugPrint Then oScript.DebugPrint "nUDF_Art_Charge: " & nUDF_Art_Charge
    											nRetVal = 0 : nRetVal = oLines.AddLine()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.AddLine(): " & nRetVal
    											nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind)
    											oLines.SetValue "QuantityOrdered", 1
    											oLines.SetValue "QuantityShipped", 1
    											oLines.SetValue "UnitPrice", nUDF_Art_Charge
    											nRetVal = 0 : nRetVal = oLines.Write()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.Write(): " & nRetVal
    										End If
    									End If
    								End If
    								Set oSalesOrderNo_ARTCHARGES = Nothing
    							End If
    						End If
    					End If
    				End If
    			End If
    		End If
    	End If
    End If

  • 0 in reply to David Speck

    Step closer, if the SO# is not in the UDT, no message pop up great.  If the UDT contains the values I am looking for and the invoice lines do not contain /ART, the message pops up and I select "Yes",  but no lines added.  I'll add a screenshot of my UDT fields to make sure I'm not missing anything.

  • 0 in reply to David Speck

    New revision to include more oScript.DebugPrint lines throughout the script to make debugging it easier.  The bArgDebugPrint variable at the beginning controls whether or not it will output to the trace window.  During development, I set it to True, once tested and ready for implementation, it can be set to False.

    bArgDebugPrint = True
    ' bArgDebugPrint = False
    aValidCompanies = Array("ROI")
    aValidStartPrograms = Array("SO_Invoice_UI")
    aValidInvoiceTypes = Array("IN")
    sItemCodeToFind = "/ART"
    If bArgDebugPrint Then oScript.DebugPrint "oSession.CompanyCode: " & oSession.CompanyCode
    If oSession.CompanyCode <> "" And InStr(UCase(Join(aValidCompanies, C h r(138))), UCase(oSession.CompanyCode)) <> 0 Then
    	If bArgDebugPrint Then oScript.DebugPrint "oSession.Updating: " & oSession.Updating
    	If oSession.Updating = 0 Then
    		If bArgDebugPrint Then oScript.DebugPrint "oSession.StartProgram: " & oSession.StartProgram
    		If oSession.StartProgram <> "" And InStr(UCase(Join(aValidStartPrograms, C h r(138))), UCase(oSession.StartProgram)) <> 0 Then
    			sInvoiceType = "" : oBusObj.GetValue "InvoiceType$", sInvoiceType
    			If bArgDebugPrint Then oScript.DebugPrint "sInvoiceType: " & sInvoiceType
    			If sInvoiceType <> "" And InStr(UCase(Join(aValidInvoiceTypes, C h r(138))), UCase(sInvoiceType)) <> 0 Then
    				sSalesOrderNo = "" : oBusObj.GetValue "SalesOrderNo$", sSalesOrderNo
    				If bArgDebugPrint Then oScript.DebugPrint "sSalesOrderNo: " & sSalesOrderNo
    				If sSalesOrderNo <> "" Then
    					Set oLines = oSession.AsObject(oBusObj.Lines)
    					oLines.MoveFirst
    					bItemCodeToFindLineExists = False
    					Do Until CBool(oLines.Eof)
    						If Not(bItemCodeToFindLineExists) Then
    							sItemCode = "" : oLines.GetValue "ItemCode$", sItemCode
    							If UCase(sItemCode) = UCase(sItemCodeToFind) Then
    								bItemCodeToFindLineExists = True
    							End If
    						End If
    						oLines.MoveNext
    					Loop
    					If bArgDebugPrint Then oScript.DebugPrint "bItemCodeToFindLineExists: " & bItemCodeToFindLineExists
    					If Not(bItemCodeToFindLineExists) Then
    						If bArgDebugPrint Then oScript.DebugPrint "InStr(UCase(oBusObj.GetDataSources()), UCase(""SalesOrderNo_ARTCHARGES"")) (1): " & InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES"))
    						If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) = 0 Then
    							nSO_UDT_ARTCHARGES_Svc = 0 : nSO_UDT_ARTCHARGES_Svc = oSession.GetObject("CM_UDT_Svc", "SO_UDT_ARTCHARGES")
    							If bArgDebugPrint Then oScript.DebugPrint "nSO_UDT_ARTCHARGES_Svc: " & nSO_UDT_ARTCHARGES_Svc
    							If nSO_UDT_ARTCHARGES_Svc <> 0 Then
    								oSession.DropObject nSO_UDT_ARTCHARGES_Svc
    								nSO_UDT_ARTCHARGES_Svc_ChildCollection = 0 : nSO_UDT_ARTCHARGES_Svc_ChildCollection = oBusObj.InitChildCollection("SalesOrderNo_ARTCHARGES", "CM_UDT_Svc", "SalesOrderNo$", "kPrimary", "SO_UDT_ARTCHARGES")
    							End If
    						End If
    						If bArgDebugPrint Then oScript.DebugPrint "InStr(UCase(oBusObj.GetDataSources()), UCase(""SalesOrderNo_ARTCHARGES"")) (2): " & InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES"))
    						If InStr(UCase(oBusObj.GetDataSources()), UCase("SalesOrderNo_ARTCHARGES")) > 0 Then
    							nSalesOrderNo_ARTCHARGES = 0 : nSalesOrderNo_ARTCHARGES = oBusObj.GetChildHandle("SalesOrderNo_ARTCHARGES")
    							If bArgDebugPrint Then oScript.DebugPrint "nSalesOrderNo_ARTCHARGES: " & nSalesOrderNo_ARTCHARGES
    							If nSalesOrderNo_ARTCHARGES <> 0 Then
    								Set oSalesOrderNo_ARTCHARGES = oSession.AsObject(nSalesOrderNo_ARTCHARGES)
    								nRetVal = 0 : nRetVal = oBusObj.ReadAdditional("SalesOrderNo_ARTCHARGES")
    								If bArgDebugPrint Then oScript.DebugPrint "oBusObj.ReadAdditional(""SalesOrderNo_ARTCHARGES""): " & nRetVal
    								If nRetVal <> 0 Then
    									sUDF_Art_OrderNo = "" : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_ORDERNO$", sUDF_Art_OrderNo
    									If bArgDebugPrint Then oScript.DebugPrint "sUDF_Art_OrderNo: " & sUDF_Art_OrderNo
    									If UCase(sSalesOrderNo) = UCase(sUDF_Art_OrderNo) Then
    										bAddLine = False
    										If oSession.UI = 0 Then
    											bAddLine = True
    										Else
    											sRetVal = "" : sRetVal = oSession.AsObject(oSession.UI).MessageBox("", "The """ & sItemCodeToFind & """ line does not exist." & vbCrLf & vbCrLf & "Do you want to add it?",	"Style=YesNo, Icon=?")
    											If bArgDebugPrint Then oScript.DebugPrint "MessageBox(): " & sRetVal
    											If UCase(sRetVal) = UCase("Yes") Then
    												bAddLine = True
    											End If
    										End If
    										If bArgDebugPrint Then oScript.DebugPrint "bAddLine: " & bAddLine
    										If bAddLine Then
    											nUDF_Art_Charge = 0 : oSalesOrderNo_ARTCHARGES.GetValue "UDF_ART_CHARGE", nUDF_Art_Charge
    											If bArgDebugPrint Then oScript.DebugPrint "nUDF_Art_Charge: " & nUDF_Art_Charge
    											nRetVal = 0 : nRetVal = oLines.AddLine()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.AddLine(): " & nRetVal
    											nRetVal = 0 : nRetVal = oLines.SetValue("ItemCode$", sItemCodeToFind)
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.SetValue(""ItemCode$"", """ & sItemCodeToFind & """): " & nRetVal
    											oLines.SetValue "QuantityOrdered", 1
    											oLines.SetValue "QuantityShipped", 1
    											oLines.SetValue "UnitPrice", nUDF_Art_Charge
    											nRetVal = 0 : nRetVal = oLines.Write()
    											If bArgDebugPrint Then oScript.DebugPrint "oLines.Write(): " & nRetVal
    										End If
    									End If
    								End If
    								Set oSalesOrderNo_ARTCHARGES = Nothing
    							End If
    						End If
    					End If
    				End If
    			End If
    		End If
    	End If
    End If

    To use the trace window feature, you need to enable debug mode in the appropriate sota.ini file depending on whether you are running Standard or Advanced/Premium.

    From the following KB article on how to enable debug mode.

    https://support.na.sage.com/selfservice/viewdocument.do?noCount=true&externalId=43033

    Once enabled, you can access the trace window like this.  Open the trace window BEFORE you expect the script to output to it.

  • 0 in reply to jland47

    Couple things.

    1. Which event do you have the script assigned to?
      1. It should be assigned to the SO_InvoiceHeader's Pre-Total event.
    2. Did you set up the miscellaneous item to hold the art charge?
      1. If yes, does it match the item code being used in the script?
      2. If no, you need to first set up the miscellaneous item.  For your purpose, now that I think about it, it doesn't sound like you are needing to use a quantity but rather just an amount, if this is the case, the item should be set up as a charge item (don't forget the GL accounts) and the script doesn't need to set QuantityOrdered, QuantityShipped, or UnitPrice, it should set ExtensionAmt instead.  An item set up as a miscellaneous item allows you to set the price and quantity but if quantity will always be 1 because it is a charge then it just makes more sense to use a charge type.
  • 0 in reply to David Speck

    Yes using script on the Pre-Total Event, I liked it working on that event better than the Pre-Write.

    -The miscellaneous item is set up as /ART and has a standard unit price associated with it, wanted to make sure the script could overwrite the standard price.

    - Now as I am reading, each time I tested different scenarios I was forced to enter the GL accounts on a SO for the /ART, didn't hard code the GL accounts in the Item Maintenance, I am going to check to see if that is the reason for the script not working properly.  Then move on to the debug steps.

    From other post I've read, prefixing your UDT with the module you associate it with is best practice, reason I changed it from CI to SO, is this correct?