Sage 100

I have been working on a script for a customer that had JobOps to add some surcharges based on the lines that are in the Sales Order. They want to have the script recalculate based on changes made to the order so I used the method where you have it delete the line, recalculate, and reinsert. Script would work great, except with JobOps you get data validation errors when deleting a line this way. Any ideas on reworking this to recalculate the line and overwrite instead of deleting?

'This script adds lines to the order based on totals of other items on order

JobType = ""
ExtAmt = 0
ItemType = ""
ItemCode = ""
QtyOrd = 0
TotExt = 0
Override = ""
MiscType = ""

retVal = oBusObj.GetValue("JT158_WTClass$",JobType)
if JobType <> "SER" then
Exit Sub
End if

Set oLines = oBusObj.AsObject(oBusObj.Lines)
retVal = oLines.MoveFirst()
Do Until CBool(oLines.EOF) = True

retVal = oLines.GetValue("ItemCode$", ItemCode)
retVal = oLines.GetValue("UDF_OVERRIDE$",Override)

If ItemCode = "/HAZWASCHG" and Override = "Y" then
exit Sub
End if

If ItemCode = "/HAZWASCHG" and Override = "N" then
retVal = oLines.Delete()
End If
retVal = oLines.MoveNext()
Loop

retVal = oLines.MoveFirst()
Do Until CBool(oLines.EOF) = True

set oItem = oLines.AsObject(oLines.GetChildHandle("ItemCode")
retVal = oItem.GetValue("UDF_LAB_SUPPLY$",MiscType)
if MiscType = "Labor" then
retVal = oLines.GetValue("ExtensionAmt", ExtAmt)
TotExt = TotExt + ExtAmt
End if
retVal = oLines.MoveNext()
Loop

retVal = oLines.AddLine(
)retVal = oLines.SetValue("ItemCode$","/HAZWASCHG")
if TotExt <= 900 then
retVal = oLines.SetValue("QuantityOrdered", TotExt)
else
retVal = oLines.SetValue("QuantityOrdered", 900)
end if

retVal = oLines.Write()

------------------------------
Jesse Braun
------------------------------

I worked on something similar last year, with a customer who has scripted discount / charge lines, which needed to be updated instead of replaced (when details changed from the source line).  In my case, each added line had a "parent", so it was trickier, but this seems to be one special charge line for the transaction.
For yours, remove the delete part.  Go straight to the calculation loop.  After that, loop through lines again.  When you find your code, update it (or exit, if set as override).  If you get through all lines without finding the code, add it.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON
------------------------------

I think this is going to work perfect! The only thing I am unsure on how best to do is to tell it to write a line if it didn't find the item in the above part of the script. Would you just assign a value to a line that matches the item, add that up, then execute the write if the value is 0? It seems like this would work but not sure if it is the easiest way to do it. Thank you so much!

------------------------------
Jesse Braun
------------------------------

Original Message:
Sent: 01-14-2019 13:25
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

I worked on something similar last year, with a customer who has scripted discount / charge lines, which needed to be updated instead of replaced (when details changed from the source line).  In my case, each added line had a "parent", so it was trickier, but this seems to be one special charge line for the transaction.
For yours, remove the delete part.  Go straight to the calculation loop.  After that, loop through lines again.  When you find your code, update it (or exit, if set as override).  If you get through all lines without finding the code, add it.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON
------------------------------

Set a flag like LineFound = "N" at the top of the script, and if you update a row in the second loop... LineFound = "Y"...
Test this value after the second loop, and add when needed.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON
------------------------------

Original Message:
Sent: 01-14-2019 14:01
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

I think this is going to work perfect! The only thing I am unsure on how best to do is to tell it to write a line if it didn't find the item in the above part of the script. Would you just assign a value to a line that matches the item, add that up, then execute the write if the value is 0? It seems like this would work but not sure if it is the easiest way to do it. Thank you so much!

------------------------------
Jesse Braun
------------------------------


Original Message:
Sent: 01-14-2019 13:25
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

I worked on something similar last year, with a customer who has scripted discount / charge lines, which needed to be updated instead of replaced (when details changed from the source line).  In my case, each added line had a "parent", so it was trickier, but this seems to be one special charge line for the transaction.
For yours, remove the delete part.  Go straight to the calculation loop.  After that, loop through lines again.  When you find your code, update it (or exit, if set as override).  If you get through all lines without finding the code, add it.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON

Thanks so much! I think this is all going to work. Appreciate the help and fast response!

------------------------------
Jesse Braun
------------------------------

Original Message:
Sent: 01-14-2019 14:06
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

Set a flag like LineFound = "N" at the top of the script, and if you update a row in the second loop... LineFound = "Y"...
Test this value after the second loop, and add when needed.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON
------------------------------


Original Message:
Sent: 01-14-2019 14:01
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

I think this is going to work perfect! The only thing I am unsure on how best to do is to tell it to write a line if it didn't find the item in the above part of the script. Would you just assign a value to a line that matches the item, add that up, then execute the write if the value is 0? It seems like this would work but not sure if it is the easiest way to do it. Thank you so much!

------------------------------
Jesse Braun


Original Message:
Sent: 01-14-2019 13:25
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

I worked on something similar last year, with a customer who has scripted discount / charge lines, which needed to be updated instead of replaced (when details changed from the source line).  In my case, each added line had a "parent", so it was trickier, but this seems to be one special charge line for the transaction.
For yours, remove the delete part.  Go straight to the calculation loop.  After that, loop through lines again.  When you find your code, update it (or exit, if set as override).  If you get through all lines without finding the code, add it.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON

Hoping for a little more assistance as I don't seem to have this quite right. I am getting it to write the line if it isn't there, but I can seem to get it to update. This is the section that it should write if it finds the line. Any help would be appreciated!

retVal = oLines.MoveFirst()
Do Until CBool(oLines.EOF) = True

set oItem = oLines.AsObject(oLines.GetChildHandle("ItemCode"))
retVal = oItem.GetValue ("ItemType$",ItemType)

if ItemType <> "1" then
retVal = oLines.GetValue("ItemCode$", LineItem)
If LineItem = "/HAZWASCHG" and TotExt <= 900 then
retVal = oLines.SetValue("QuantityOrdered", TotExt)
LineFound = "Y"
else
retVal = oLines.SetValue("QuantityOrdered", 900)
LineFound = "Y"
end if
end if

retVal = oLines.MoveNext()
Loop

------------------------------
Jesse Braun
Vrakas/Blum Computer Consulting, Inc.
------------------------------

Original Message:
Sent: 01-14-2019 14:52
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

Thanks so much! I think this is all going to work. Appreciate the help and fast response!

------------------------------
Jesse Braun
------------------------------


Original Message:
Sent: 01-14-2019 14:06
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

Set a flag like LineFound = "N" at the top of the script, and if you update a row in the second loop... LineFound = "Y"...
Test this value after the second loop, and add when needed.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON


Original Message:
Sent: 01-14-2019 14:01
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

I think this is going to work perfect! The only thing I am unsure on how best to do is to tell it to write a line if it didn't find the item in the above part of the script. Would you just assign a value to a line that matches the item, add that up, then execute the write if the value is 0? It seems like this would work but not sure if it is the easiest way to do it. Thank you so much!

------------------------------
Jesse Braun


Original Message:
Sent: 01-14-2019 13:25
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

I worked on something similar last year, with a customer who has scripted discount / charge lines, which needed to be updated instead of replaced (when details changed from the source line).  In my case, each added line had a "parent", so it was trickier, but this seems to be one special charge line for the transaction.
For yours, remove the delete part.  Go straight to the calculation loop.  After that, loop through lines again.  When you find your code, update it (or exit, if set as override).  If you get through all lines without finding the code, add it.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON

You're missing the Write() after the SetValue so nothing is saved.
retVal = oLines.Write()

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 01-23-2019 15:51
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

Hoping for a little more assistance as I don't seem to have this quite right. I am getting it to write the line if it isn't there, but I can seem to get it to update. This is the section that it should write if it finds the line. Any help would be appreciated!

retVal = oLines.MoveFirst()
Do Until CBool(oLines.EOF) = True

set oItem = oLines.AsObject(oLines.GetChildHandle("ItemCode"))
retVal = oItem.GetValue ("ItemType$",ItemType)

if ItemType <> "1" then
retVal = oLines.GetValue("ItemCode$", LineItem)
If LineItem = "/HAZWASCHG" and TotExt <= 900 then
retVal = oLines.SetValue("QuantityOrdered", TotExt)
LineFound = "Y"
else
retVal = oLines.SetValue("QuantityOrdered", 900)
LineFound = "Y"
end if
end if

retVal = oLines.MoveNext()
Loop

------------------------------
Jesse Braun
Vrakas/Blum Computer Consulting, Inc.
------------------------------


Original Message:
Sent: 01-14-2019 14:52
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

Thanks so much! I think this is all going to work. Appreciate the help and fast response!

------------------------------
Jesse Braun


Original Message:
Sent: 01-14-2019 14:06
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

Set a flag like LineFound = "N" at the top of the script, and if you update a row in the second loop... LineFound = "Y"...
Test this value after the second loop, and add when needed.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON


Original Message:
Sent: 01-14-2019 14:01
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

I think this is going to work perfect! The only thing I am unsure on how best to do is to tell it to write a line if it didn't find the item in the above part of the script. Would you just assign a value to a line that matches the item, add that up, then execute the write if the value is 0? It seems like this would work but not sure if it is the easiest way to do it. Thank you so much!

------------------------------
Jesse Braun


Original Message:
Sent: 01-14-2019 13:25
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

I worked on something similar last year, with a customer who has scripted discount / charge lines, which needed to be updated instead of replaced (when details changed from the source line).  In my case, each added line had a "parent", so it was trickier, but this seems to be one special charge line for the transaction.
For yours, remove the delete part.  Go straight to the calculation loop.  After that, loop through lines again.  When you find your code, update it (or exit, if set as override).  If you get through all lines without finding the code, add it.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON

Thank you! What a dumb thing to be missing! It is so easy to get wrapped up in a script and miss the simple things. I really appreciate the help, thanks again!

------------------------------
Jesse Braun
Vrakas/Blum Computer Consulting, Inc.
------------------------------

Original Message:
Sent: 01-23-2019 16:04
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

You're missing the Write() after the SetValue so nothing is saved.
retVal = oLines.Write()

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 01-23-2019 15:51
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

Hoping for a little more assistance as I don't seem to have this quite right. I am getting it to write the line if it isn't there, but I can seem to get it to update. This is the section that it should write if it finds the line. Any help would be appreciated!

retVal = oLines.MoveFirst()
Do Until CBool(oLines.EOF) = True

set oItem = oLines.AsObject(oLines.GetChildHandle("ItemCode"))
retVal = oItem.GetValue ("ItemType$",ItemType)

if ItemType <> "1" then
retVal = oLines.GetValue("ItemCode$", LineItem)
If LineItem = "/HAZWASCHG" and TotExt <= 900 then
retVal = oLines.SetValue("QuantityOrdered", TotExt)
LineFound = "Y"
else
retVal = oLines.SetValue("QuantityOrdered", 900)
LineFound = "Y"
end if
end if

retVal = oLines.MoveNext()
Loop

------------------------------
Jesse Braun
Vrakas/Blum Computer Consulting, Inc.


Original Message:
Sent: 01-14-2019 14:52
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

Thanks so much! I think this is all going to work. Appreciate the help and fast response!

------------------------------
Jesse Braun


Original Message:
Sent: 01-14-2019 14:06
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

Set a flag like LineFound = "N" at the top of the script, and if you update a row in the second loop... LineFound = "Y"...
Test this value after the second loop, and add when needed.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON


Original Message:
Sent: 01-14-2019 14:01
From: Jesse Braun
Subject: Scripting to replace rather than delete a line

I think this is going to work perfect! The only thing I am unsure on how best to do is to tell it to write a line if it didn't find the item in the above part of the script. Would you just assign a value to a line that matches the item, add that up, then execute the write if the value is 0? It seems like this would work but not sure if it is the easiest way to do it. Thank you so much!

------------------------------
Jesse Braun


Original Message:
Sent: 01-14-2019 13:25
From: Kevin Moyes
Subject: Scripting to replace rather than delete a line

I worked on something similar last year, with a customer who has scripted discount / charge lines, which needed to be updated instead of replaced (when details changed from the source line).  In my case, each added line had a "parent", so it was trickier, but this seems to be one special charge line for the transaction.
For yours, remove the delete part.  Go straight to the calculation loop.  After that, loop through lines again.  When you find your code, update it (or exit, if set as override).  If you get through all lines without finding the code, add it.

------------------------------
Kevin Moyes
Technical Systems Analyst, Munjal-White Consulting
Toronto ON