VB Script to compare and update the date values for 2 date fields

Question

Hi 
 I have a business requirement, where we have a Date Field1 and Date Field2 , while entry of the details data, Field1 will get the date auto defaulted from the header date field. And Field2 will be blank . 
 Later when there is an update on the Date Field1 a message box should pop up and the user should confirm to either populate the Old Value(field1) to Date Field2 -'Y'/'N', and likewise system should save new value for Date Field1 and old value to date Field2 on user confirmation. 
 Any experience with such scenario and possible solution will be helpful. 
 Thanks 
 AJ

Kevin M · Accepted Answer

Search the help files for messagebox which is a Sage function with a way to prompt users for a Y/N response (works with Standard / Advanced / Premium). GetValue / SetValue for reading / changing a field value. 
 Most of it seems to be straight VB logic, although you might need a 3rd UDF for what is being entered as a new value. Otherwise I don't know how you'd have the OldValue.

AJ@Marki · Answer

Thanks Kevin, I am very new to Sage Scripting, is there a way to have a bind variables to get/compare values on the fly rather than having an additional UDF. Please recommend and advise,. 
 Thanks 
 AJ

David Speck · Answer

Getting the old value is tricky, you can either; 
 
 get the original record and loop through the columns to determine the position of your column in the delimited string to get the value. 
 Use a new method in version 2018 called "ChangedRecordInfo". 
 Use a combination of scripts, one on the post-read event to get the value and set it into a storage variable like Kevin said using oScript.SetStorageVar then retrieve it in your pre/post validate or pre/post write event using oScript.GetStorageVar. 
 
 For "ChangedRecordInfo", you pass it three variables that have been set to blank ("") and it will return a comma delimited list of columns that have been modified in the first variable, a SEP (Chr(138)) delimited list of the original values in the second variable, and a SEP (Chr(138)) delimited list of the new values in the third variable. 
 You can then convert the returned column, old value, and new value strings into arrays and loop through the column array to determine the position of the original and new values in the old value and new value arrays. 
 For the GetStorageVar and SetStorageVar, you can find more info on them in the Scripting.docx that can be downloaded from sage, I think it even has a KB article on it now. I've copied it below as well.

retVal = oScript.SetStorageVar (id_desc as String, val)

Use this method to store any number of values that need to be accessible across function calls. Meaning they can be set in one procedure and obtained in another procedure for processing. Very useful for performance purposes when setting default values programmatically. (NOTE: object handles cannot be stored as objects, only numerics)

retVal = oScript.GetStorageVar (id_desc as String, val)

Complement to SetStorageVar, used to get a previously stored value.

Sage 100

VB Script to compare and update the date values for 2 date fields

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING