Just search Sage City Community for the string ADO SQL, however you should give consideration to doing this with User Defined Scripting (or UDS's) methods instead. I believe it is likely that your enhancement may be more efficient and supportable if you use these.

Where is your UDF_Quantity_To_Ship field? What version of Sage are you using?

The quantity to ship field is on SO_SalesOrderDetail, we are using Sage 100 Advanced ERP 2014 (Version 5.10.1.0)

Would it be possible for you to give me a super simplified example of accomplishing this type of thing using UDS methods? I can usually take a small piece of good information and run with it but I am at a loss as to where to start. I did find some stuff for ADO SQL and can try that path if needed but if you think there is a better way than I would prefer to try that.

I've never gotten anywhere with the scripting in Sage so I'm no help with that. But I've made a number of Access background routines that update user defined fields every few minutes. And if you can use the SQL Administrator you can build a view with what you want and then treat that like a read-only user defined table.

There are many examples here in the community and since your needed logic is not clear to me, it would be easiest to search for the particulars you are looking for or you may wish to give us more details of what and why and maybe someone will supply a snippet for you.

Connex, the simple version of what I would like to do is this.. On the Item Maintenance screen, Main tab, in the Quantities grid I would like to summarize a numeric field, UDF_Quantity_To_Ship located in SO_SalesOrderDetail. I greatly appreciate your help getting me to a starting point.

This would not be a simple script. It would be at least two to increment and then decrement the item related counter. The complexity is in the support of changes to quantities. Just because a picking sheet has been printed doesn't mean that quantities can be subsequently changed. One would have to track, likely in a separate UDF the previous quantity so that the difference could be determined with which to adjust the item's counter. You would have to also track deleted orders and then there is always the chance that an error is encountered that could cause the counter to become out of step with the actual printed orders. Thus you would have to have another script to reset and re-synchronize. Not a small script. I recommend you hire an experienced script writing consultant or seek out available training information.

I'm thinking, if there are not too many lines in the SO detail table, something like this could work:
- trigger on update of your UDF field.
- read in all rows for that item / warehouse in the SO detail table (key KITEMWHSE) and add up all the values. This might not work if you use Master / Repeating / Quote order types, without an extra step to check for that to exclude those rows.
- when you have a result, open IM_ItemWarehouse and update the correct row (if it exists...).

If dealing with + / - at a transaction level, you'd have issues whenever an order is placed for something that has never been received (and therefore there is no existing row in IM_ItemWarehouse).

I didn't see the question answered, so here is a bare-bones way to connect to a SQL database and get values. I don't know if this is the best approach for what you are doing, but I hope this answers your first question.

Dim oConnection, orec, fieldValue
Set oConnection = CreateObject("adodb.connection")
Set orec = createobject("adodb.recordset")

oConnection.Open "Driver={SQL Server};" & _
"Server=serverName;" & _
"Database=databaseName;" & _
"Uid=userID;" & _
"Password=Password"

'Sql command/statement goes here...
orec.open "SELECT * FROM Table_Name WHERE Field_Name = 'filter_value'", oConnection


While Not orec.EOF
'Get Values Here
fieldValue = orec("FieldName")
orec.MoveNext
Wend
'Make sure to close your connection!
orec.close
oConnection.close
Set oConnection=Nothing