3 ways to make xkzero part of your Sage Summit story

xkzero will be well-represented at Sage Summit 2015 in New Orleans!

Here are 3 ways to make xkzero a part of your Sage Summit story.

1. Visit our booth.

xkzero team members look forward to seeing you at booth 522!
Sage Summit Booth 522: xkzero

2. Take a class.

Alnoor Cassim, Director of xkzero Technical Services will conduct a 2-day advanced scripting class for 90 Minds.  

Alnoor has taught 4 sold out beginning-level scripting classes over the last couple of years–each session attended by around 20 consultants. This will be the first time he offers an advanced-level class, so you won’t want to miss it!

3. Attend a talk.

Sage Summit welcomes back xkzero co-founder Paul Ziliak as a part of the Thought Leadership speaker series.

This year, Paul is slated for two speaking sessions:

We hope you’ll be a part of our Sage Summit story! See you in New Orleans? Register here. (Remember, you also need to register for the sessions and classes!) 

Paul Ziliak at Sage Summit 2014: "Why Mobile, Why Now"

Paul Ziliak at Sage Summit 2014: “Why Mobile, Why Now”

 

Line Item Discount Calculations: Scripting Tip for Sage 100 ERP

Scripting Tips for Sage 100 ERP

Blogger: Alnoor Cassim, xkzero Technical Services

facebooktwittergoogle_plusredditpinterestlinkedinmail

A case for creating a script for line item discount calculations—
Let’s say you sell designer purses and handbags. During the month of June, you offer a 20% discount on Gucci orders of $300 or more—for loyalty program customers only.

Think of all the specific parameters involved in this promotion. Qualifications to receive the fixed percentage discount require the purchase be made during a limited timeframe, by a certain customer base, and only for orders of products by a particular vendor that exceed a designated amount.

Can Sage 100 ERP do the math?

The regular Sales Promotion feature has the option of applying a discount % to a date range. However, we cannot easily apply that percentage off to an entire product line, nor can we specify the customer type or add any other conditions.

To minimize the salesperson’s involvement in the calculation, you can automate Sage 100 ERP to set all the qualifying discount conditions. Yes, this is another case in which scripting can help!

Let’s assume the following:

  1. We have set up this miscellaneous charge code for the promotion:  /DISC20
    We would like to reflect the discount by having the script automatically add /DISC20 line as a negative amount (to offset the order total, subtracting 20 percent for the Gucci products).
    We will not use the line item discount percentage feature nor use the Discount Rate/ Amount fields on the Totals tab.
  2. In Customer Maintenance we will use the Customer Type field to represent the loyalty tier level where B=Blue, S=Silver, G=Gold, Z=Zero. For our promotion a value of B, S, or G qualifies.
  3. In Product Line Maintenance, we will set up GUCC as the product line for all Gucci items. Several items have been created using GUCC. (Others designers in our collection include Louis Vuitton, Coach, Nine West, and LeSportsac.)

To accomplish this task, you can create a script. For your own company and industry, add your own business rules and other complexities.

What kind of script do you need to create?
Create an event script (a.k.a. User Defined Script) that runs when the Totals tab is clicked in Sales Order Entry. The event type we are using is called Pre-Totals. As the “Pre” implies, a Pre-Totals script will run before any of the standard Sage 100 ERP programming (such as when the totals tab is clicked (e.g. calculating Sales Tax)).

How do you create this script?

Follow these steps:

  1. Go to the Custom Office/Main Menu, and make these selections:
    “User-Defined Field and Table Maintenance”
  2. Under the “Sales Order” heading, choose: “SO Sales Order Header”
  3. Right-click and choose “User Defined Scripts”
  4. In the “User Defined Script” window, click the “Add” button. This will open the “Add Script” window.
  5. In the “User-Defined Script–Add Script” window, as shown below [Image 1], make these choices:
    Event: “Table – Pre-Totals”
    Type in a name for your script, such as this (without the quotes):
    “Line Item Disc by Prod Line”

    Script-LineItem_Image1

    Image 1

  6. You will see a message indicating that the script file does not exist, followed by a question, “Do you want to create it? Yes/No?” Yes, you do want to create the script! Click: “Yes.”
  7. Now the “Edit Script” window will appear. Copy the following code and paste it into your script editor window. Note: It is important to use the code exactly as shown below, including line breaks and spaces.

'Alnoor Cassim - xkzero - Line_Item_Disc_By_Prod_Line

'Approach:
'1 Check the Order Date for June Orders
'2 Check for a qualifying Customer Type
'3 Check if last line has /DISC20 and remove as we need to recalc
'4 Scan all lines for at least $300 of qualifying Gucci product
'5 If order qualifies, add the /DISC20 line and apply 20% off

'Init VARs
sItemType = "" : sItemCode = "" : nXAmt = 0 : nTotXAmt = 0
sProdLine = "" : sOrderDate = "" : sCustType = "" : sDiv = "" : sCust = ""
sDiscProdLine = "GUCC" : nAmtToQualify = 300 : nDP = .20

'Get the Order Date and if not in June exit the script -> No Discount
retVal = oBusObj.GetValue("OrderDate$", sOrderDate)
If sOrderDate < "20150601" or sOrderDate > "20150630" Then
	Exit Sub
End If

'Find out if we have a qualifying loyalty customer
retVal = oBusObj.GetValue("ARDivisionNo$", sDiv)
retVal = oBusObj.GetValue("CustomerNo$", sCust)

'AR_Customer table available as Data Source so use GetChildHandle()
'The other way is to use GetObject()
Set oCustomer=oBusObj.AsObject(oBusObj.GetChildHandle("CustomerNo"))

'Concantenate the key fields for AR_Customer
FullCust = sDiv & sCust

'Find the Customer record. Note how oCustomer is referenced now.
retVal = oCustomer.Find(FullCust)

'Get the value of the Customer Type (Loyalty Tier)
retVal = oCustomer.GetValue("CustomerType$", sCustType)

'If not Blue, Silver, or Gold exit script -> No discount
If sCustType = "Z" or sCustType = "" Then
	Exit Sub
End If

'oLines passed in but we must explicity set as an object in VB
Set oLines = oSession.AsObject(oBusObj.Lines)

'Avoid Double Discount
'Move to last line first to check if /DISC20 already exists.
'If so delete as we need to recalc if order qualifies for discount
retVal = oLines.MoveLast()
retVal = oLines.GetValue("ItemCode$", sItemCode)
If sItemCode = "/DISC20" Then
	retVal = oLines.Delete()
End If

'Start at the top of the grid
retVal = oLines.MoveFirst()

'Loop through Lines grid. Check for lines that qualify for discount
Do Until cBool(oLines.EOF) = True

	retVal = oLines.GetValue("ItemType$", sItemType)
	retVal = oLines.GetValue("ItemCode$", sItemCode)
	
	'Check for regular inventory items only: ItemType = 1
	If sItemType = "1" Then

		'CI_Item table is available as a Data Source to get Prod Line
		'Note how we use oLines instead of oBusObj here
		If IsObject(oItem) = False Then
			Set oItem=oLines.AsObject(oLines.GetChildHandle("ItemCode"))
		End If
	
		'Get the Product Line and if qualifiying increment total
		retVal = oItem.Find(sItemCode)
		retVal = oItem.GetValue("ProductLine$", sProdLine)
		If sProdLine = sDiscProdLine Then
			
			'We have reached a qualifying line yea!
			'Get the Extension Amt and increment the total.
			retVal = oLines.GetValue("ExtensionAmt", nXAmt)
			nTotXAmt = nTotXAmt + nXAmt
		End If
	End If
	
	'Move to next line in the grid
	retVal = oLines.MoveNext()

Loop

'Final Test
'Check if we have at least $300 of qualifying product
'If so add the /DISC20 negative misc charge code

If nTotXAmt >= nAmtToQualify Then
	retVal = oLines.AddLine()
	retVal = oLines.SetValue("ItemCode$", "/DISC20")
	retVal = oLines.SetValue("ItemType$", "3")
	sCommentText = "Congrats you qualify for a discount"
	retVal = oLines.SetValue("CommentText$", sCommentText)
	retVal=oLines.SetValue("ExtensionAmt",-1*(Round(nDP*nTotXAmt,2)))
	retVal = oLines.Write()
End If
 
How does this script work?

To better understand the logic behind the script’s functionality, read this:

“Approach”
These comments lines represent the high level approach we will apply:

  1. Check the order date for June orders.
  2. Check for a qualifying customer type.
  3. Check if last line already has /DISC20 and remove if we need to recalculate.
  4. Scan all the lines for at least $300 of qualifying Gucci product.
  5. If the order qualifies, add the /DISC20 line and apply 20% off the price.

“Init VARs”
In the first section, we “initialize” (init) the variables to prepare for use later in the script.
Note: We are hard-coding the qualifying product line, qualifying amount, and discount pct. However, if in July a new promo starts for a different designer, we could create a User Defined Table (UDT) to store the “designer of the month,” the “qualifying amount,” and “Disc Pct.” This would allow the script to grab the values from the UDT to prevent needing to modify that script every time.

The next block of code enables the following preliminary steps:

  • If the Order Date is not June 1-30, 2015, it disqualifies the discount (exits the script).
  • It attains values from the AR_Customer table, by using the GetChildHandle() function to take advantage of a Data Source called “CustomerNo”
    Note: This has exactly the same result as using Custom Office, where in UDF Maintenance you map a UDF from AR_Customer table via the “CustomerNo” data source to SO_SalesOrderHeader table.
  • It locates the CustomerNo in AR_Customer table through the Find() function.
  • It gets the value of the CustomerType field through the GetValue() function.
  • It disqualifies the discount (exits the script) if CustomerType (the Loyalty Tier) is not Blue, Silver, or Gold.

Preventing Double Discounts
Normally we would scan the grid sequentially from top to bottom. However, we want to jump to the last line first and remove /DISC20 in case it was still there from the last time we clicked Totals for this order. We need to calculate the discount but at the same time not have more than one instance of /DISC20.

Scanning the Lines Grid
We are now ready to position ourselves at the first line in the grid and start the “Do Until Loop.” We continue to move sequentially through each line via the MoveNext() function until we reach “EOF.” This technically means “End of File,” but in this case refers to the last row in our grid. We use the VB function CBool() to convert the condition into a True/False test.

We now check if the ItemType field is set to “1.” This means we landed on a regular inventory item. If so, then we continue processing.

We can use the GetChildHandle() function again instead of GetObject(). Connect to the data source called “ItemCode” to get to the ProductLine field.
Note: This produces exactly the same results as using Custom Office, where in UDF Maintenance you map a UDF from CI_Item table via the “ItemCode” data source to SO_SalesOrderDetail table.

Next step—
Check to see if we landed on an item belonging to GUCC product line. If so, store the extension amount and increment the Gucci total in a counter. Finally, we do the MoveNext() function to move to the next line in the grid.

Final Test
If the totals for all GUCC lines are at least $300, then go through the steps to add a new line for /DISC20. We set the ItemType field to “3″ for Miscellaneous Charge, set the ExtensionAmt field to negative to reduce the order total, and set a comment to make it clear a discount was taken. Finally we write the new line.

You’re almost done! 

Now you can accept the script.
This will return you to the “Add Script” window.

  1. To close this screen and return to the “User Defined Scripts” window, click “OK.”
  2. Next, to go back to the main “User Defined Field” and “Table Maintenance” window, click “Close.” Here, you must also click “Close” again to see the “Script Compile” window.
  3. IMPORTANT: When the “Script Compile” window appears, click the Compile” button.

Note that you can further edit the script, if necessary. To do so, from the “Custom Office/Main” menu, choose the “User Defined Script Maintenance” task. When done editing the script, to syntax check the script, click the Check Script button:

To save the script, click the Accept button.

The next step is to click the Compile button in the lower-left of the screen. When the Script Compile window appears, click the Compile button again followed by the Close button.


This is one of many Sage 100 ERP scripting examples we will share through the xkzero blog. If you need help with scripting or other programming or technical issues with your Sage ERP, no matter how complex, please feel free to contact us: 

xkzero Technical Services
Email: info@xkzero.com
Call: 847-416-2009

How to Prevent Duplicate Customer Names with Scripting

Scripting Tips for Sage 100 ERP

Blogger: Alnoor Cassim, xkzero Technical Services

facebooktwittergoogle_plusredditpinterestlinkedinmail

One of the primary reasons users make customizations in Sage 100 ERP is to add business rules to accommodate a business process. Often, you can do this yourself with a script. To illustrate, let’s say we have been asked to do the following:

A script can be just what you need to prevent an action!

When a new customer is entered in Customer Maintenance, you want the following steps to occur:

  1. Check the customer name against all other records for duplicates.
  2. Prevent any duplicate customer names from being repeated on the new customer record.
  3. Display a message to indicate that the name is already being used. Include the customer number.
  4. Audibly reinforce the message with an alert sound or spoken words.

Is it possible to create these steps with scripting? Most certainly!
Note that a well thought-out and researched script can accomplish the task in an efficient and optimal way. However, a less carefully planned script (or modification) can run into performance issues and may not return the correct results 100% of the time.

The good news is you will see the well-designed version! It is a short script but appears to be longer because it is heavily commented. Bonus: Many concepts applied in this script can be also be applied to other scripts you write.

What kind of script do you need to create?
Create an event script (a.k.a. User Defined Script) that runs when the customer name is entered in Customer Maintenance. The event type we are using is called Column Pre-Validate. All of the “Pre” events (Pre-Validate, Pre-Write, Pre-Delete) are designed for you to add a user-defined validation and prevent an action from occurring.

How do you create this script?

Follow these steps:

  1. Go to the Custom Office/Main Menu, and make these selections:
    “User-Defined Field and Table Maintenance”
  2. Under the “Accounts Receivable” heading, choose: “AR Customer Master.”
  3. Right-click and choose “User Defined Scripts.”
  4. In the “User Defined Script” window, click the “Add” button. This will open the “Add Script” window.
  5. In the “User-Defined Script–Add Script” window, as shown below, make these choices:
    Event: “Column–Pre-Validate”
    Type in a name for your script, such as this:
    “Find Dupe Cust Name Play Sound”
    Alnoor-Dups-Screenshot_ImageA
  6. You will see a message indicating that the script file does not exist, followed by a question, “Do you want to create it? Yes/No?”
    Yes, you do want to create the script! Click: “Yes.”
  7. Now the “Edit Script” window will appear. Copy the following code and paste it into your script editor window. Note: It is important to use the code exactly as shown below, including line breaks and spaces.

'Alnoor Cassim - xkzero - Prevent Duplicate Customer Names and Play Sound

'Init VARs
DupeCustNo = "" : DupeCustName = ""

'Run only for new customers by verifying EditState = 2
'An EditState = 1 means an existing record, 0 means no record loaded.
'oBusObj is the primary connection to AR_Customer table.

If oBusObj.EditState = 2 Then

'Create a separate new connection to AR_Customer
'Use the service object since we are not writing back data
'Use this syntax

Set oCustomer = oSession.AsObject(oSession.GetObject("AR_Customer_svc"))

'Searching one record at a time from top to bottom would be slow
'Set the KNAME index as the browse index to do a fast search of Customer Name	
'KNAME from File Layouts: CustomerName+ARDivisionNo+CustomerNo

retVal = oCustomer.SetBrowseIndex("KNAME", "")

'The variable called "value" represents what you typed in for the Customer Name.
'Assign "value" to your own variable. We will call our variable "Name".
'Strip any trailing spaces and force Upper Case to avoid a case sensitive search.

Name = UCase(RTrim(value))

'We do not have the values for all 3 columns of KNAME index.
'So create a filter to pass in just the CustomerName as a partial value.
'This is done with the SetBrowseFilter() function.

retVal = oCustomer.SetBrowseFilter(Name)

'Now this MoveFirst() command will both be filtered by name and use the KNAME index.
'This will result in a fast search!

retFound = oCustomer.MoveFirst()

If retFound = 1 Then

'retFound is the return value. It will hold either a 1 (found) or 0 (not found).
'If 1 then we found at least a partial match but now check if it is an exact dupe.

retVal = oCustomer.GetValue("CustomerNo$", DupeCustNo)
retVal = oCustomer.GetValue("CustomerName$", DupeCustName)

'A short dupe is OK. Ex: You typed "Road" and the search found "RoadRunner"
'Only reject if you typed in RoadRunner. This is how:

If Name = UCase(RTrim(DupeCustName)) Then

'The SetError() function will both show the message and prevent the dupe name.

sMsg = "This name is already used with Customer No " & DupeCustNo
retVal = oScript.SetError(sMsg)

'Play a Windows sound event using the ProvideX PRINT BEEP function.
'This plays back sound on the wkstn even in Sage 100 Advanced and Premium

oScript.Execute("PRINT "+"'BEEP'"+CHR(40)+CHR(34)+"*.Default,w"+CHR(34)+")")

'That was the registered default event sound in Windows.
'Look in registry for more registered sounds. Leave the "*" in front.
'HKEY_CURRENT_USERAppEventsEventLabels
'E.g. "*EmptyRecycleBin,w"

End If 'check if a duplicate name was found

End If 'allow for short duplicate names

End If 'check if on a new record


How does this script work?

To better understand the logic behind the script’s functionality, read this:

“Init VARs”
In the first section, we “initialize” (init) the variables to prepare for use later in the script.

“IF/THEN/ELSE” 
Through these stated conditions, we apply logic to check if we’re in a new record, then later to check if we have found a customer name match, and then later to check for an exact name match.

How do we find out if the customer is new or existing?
We do this by checking the “edit state” as indicated by the following values:

2 = new customer
1 = existing customer
0 = no customer on the screen

How to Use a Secondary Customer Object
When the script starts we already have an existing Customer object in memory called oBusObj. For this script we need to create a secondary connection to the same Customer object by using the GetObject() function. We will call that second object oCustomer. In our case we set oCustomer as the handle for AR_Customer_Svc. However, if we need, to write back data we use AR_Customer_bus instead.

How to Use an Alternate Index for Searching, Set a Browse Filter, and Find the Result
The primary index is called KPRIMARY and contains the ARDivisionNo and CustomerNo columns. We can use KPRIMARY for finding the duplicates but would have to check the CustomerName column on every row until a match is found. This type of sequential top to bottom search is very slow.

Instead, we look for an index that starts off with CustomerName. From the File Layouts link in Sage 100 ERP, under AR_Customer table, we see the KNAME index contains what we need. We will apply this to the oCustomer handle. Also, to make sure we only change the index for the purpose of searching, we use the SetBrowseIndex() function with “KNAME” passed in as 1st argument and an empty string as the 2nd argument.

One issue with KNAME is that it comprises three columns that make up this alternate key, yet we only want to search by the one CustomerName column. So, we must set a partial key by creating a filter to pass in the CustomerName. This is done with the SetBrowseFilter() function and we apply it to the oCustomer handle as well.

Now we use the MoveFirst() function. Normally it would just move to the first row of the record set. In this case it finds the first matching result based on our browse filter and searches in KNAME order.

The retFound variable represents a return value, just like the retVal variable does. We can use any numeric variable for return values.

If our search finds a match, then retFound = 1.
Otherwise, retFound = 0.

Partial Duplicate vs Exact Match
After using MoveFirst() and after retFound = 1, we can have a situation where we have a partial duplicate match instead of an exact match.

For example, if you type “Grass” for the customer name, the MoveFirst() search result may include “GrassHopper Pesticide Corp.” You don’t want this search to prevent you from using “Grass,” so to find an exact match, we do the GetValue() function to compare the actual name against the searched name.

Rejecting the Customer Name
Once an exact match has been found, now we have to actually reject/prevent the customer name that was entered in the screen from being used. This is accomplished with the SetError(“User Defined Message”) function. It will both show your user defined message and prevent the name you enter on the screen from being used.

Playing a Windows System Sound
Use the PRINT BEEP command noted in the script to play a Windows sound event. In Advanced and Premium environments the sound will play on the workstation.

You’re almost done! 

Now you can accept the script.
This will return you to the “Add Script” window.

  1. To close this screen and return to the “User Defined Scripts” window, click “OK.”
  2. Next, to go back to the main “User Defined Field” and “Table Maintenance” window, click “Close.” Here, you must also click “Close” again to see the “Script Compile” window.
  3. IMPORTANT: When the “Script Compile” window appears, click the Compile” button.

Note that you can further edit the script, if necessary. To do so, from the “Custom Office/Main” menu, choose the “User Defined Script Maintenance” task. When done editing the script, to syntax check the script, click the Check Script button:
checkscript

To save the script, click the Accept button.

The next step is to click the Compile button in the lower-left of the screen. When the Script Compile window appears, click the Compile button again followed by the Close button.

How to Use the Windows Text To Speech (TTS) API to Speak the Words
As an alternative to playing a system sound you can play back the spoken words of the error message using a Windows Accessibility feature called Text To Speech (TTS). This is challenging to use in Sage 100 Advanced and Premium because the playback needs to occur at the workstation, not the server. [Credit goes to Sage 100 VAR Vrakas/Blum for coming up with the TTS idea and asking how to play the voice on the workstation.]

Sage 100 ERP Advanced/Premium:
CHALLENGE & SOLUTION

It turns out running any event script for Advanced/Premium is a challenge, since by design it executes on the server yet we need it to execute on the client. Button scripts have a nice option to “Execute Script on Client” vs “Execute Script on Server” but event scripts do not.

This is when we need to pull out our bag of tricks.

We will need a total of 2 scripts. The 1st script is actually the same one as above but we substitute the PRINT BEEP code with the following script:

'Text To Speech version of Playing Sound

'Check if we are on Sage 100 Advanced or Premium edition
If oSession.CS Then  

'Set location for Workstation Script. Change the file name as needed.
WkstnScript=oSession.PathCSRoot+"CMScriptDupeCustNamexkZ_PlaySoundWkstn.vbs"

WSH = "WScript.exe "  'This is the Windows Scripting Host
vbsCMD = CHR(34) & WSH & WkstnScript & CHR(34) 'Form the o/s cmd to run a VB Script
oScript.Execute("ShellCMD$ = " & vbsCMD) 'Pass the cmd into a pvx variable ShellCMD$

'Fire off the script at the workstation. The [wdx] indicates run from the client.
oScript.Execute("CALL "+CHR(34)+"[wdx]SYZDLL;SHELL_AND_WAIT"+CHR(34)+",ShellCMD$")

Else 
'We are on Sage 100 Standard so it will already run from the client.

'Tap into TTS feature. More info can be found here: http://goo.gl/cM6dYJ
If IsObject(oVoice) = False Then
Set oVoice = CreateObject("SAPI.SpVoice") 
End If

Words = "This customer name is already used."
retVal = oVoice.Speak(Words)

End If


The Text to Speech workstation script (for Advanced and Premium) will look like this:

'Workstation Script but physically located on server here
'..Mas90CMScriptDupeCustNamexkZ_PlaySoundWkstn.vbs
'NOTE: Rename the file as needed but you must save it as VBS not TXT.

'Tap into TTS feature. More info can be found here: http://goo.gl/cM6dYJ
If IsObject(oVoice) = False Then
Set oVoice = CreateObject("SAPI.SpVoice") 
End If

Words = "This customer name is already used."
retVal = oVoice.Speak(Words)


This is one of many Sage 100 ERP scripting examples we will share through the xkzero blog. If you need help with scripting or other programming or technical issues with your Sage ERP, no matter how complex, please feel free to contact us: 

xkzero Technical Services
Email: info@xkzero.com
Call: 847-416-2009

 

How to Use Scripting to Require Additional Fields on a Screen 

Scripting Tips for Sage 100 ERP
by Alnoor  Cassim, xkzero Technical Services

facebooktwittergoogle_plusredditpinterestlinkedinmail

One of the unique features of Sage 100 ERP is the ability to set up business rules, similar to a built-in requirement such as entering a salesperson code to add a new customer within Customer Maintenance.

An example of a customized business rule is not allowing a customer to be created until the Price Level and Email Address fields are filled in. Also, you may put rules in place to temporarily place the customer on credit hold and apply a $1 credit limit until the credit hold is cleared by the finance department.

Or, perhaps you have not taken advantage of the customization options in Sage 100 ERP, and have an imprecise, manual process in place that relies on all users to follow and remember these rules. You may have even defined tasks and security events in Role Maintenance to accomplish a portion of the automation, then thought, “I wish there were a clean way to enforce all these rules!” In fact, there is a way! Once again (like we showed you in the previous blog), scripting can save the day! The answer to your frustrations could be creating a fairly straightforward script.

How to Use Scripting to Require Additional Fields on a Screen

What  kind of script  do you need to create?  

Create an event script (a.k.a. User Defined Script) that runs in Customer Maintenance when the Accept button is pressed.

Note that you are not limited to requiring only the fields mentioned above. You are also not limited to Customer Maintenance. The event type you will create is called Table Pre-Write. All of the “Pre” events (Pre-Write, Pre-Validate, Pre-Delete) are typically used to add your own user defined validation and prevent an action from occurring.

How  do  you create this script?  

Follow  these  steps:

1. Go to the Custom Office/Main Menu.
Make these selections:
“User-Defined Field”
“Table Maintenance”

2. Go to the  “Accounts Receivable”  heading.
Choose:  “AR Customer Master.”
Right-click and choose:  “User Defined Scripts.”

3. You are now in  the  “User Defined Script”  window.
Click:  “Add”

4. This will open the “User-Defined Script – Add Script”  window. (See image below.)
Choose:  Event: “Table – Pre-Write”
Type a name for your script, such as this:  “Required Fields for New Customer”

Alnoor-SC_Script031815

5. This will bring you to a  message  indicating that  the script file does not  exist.
It will be followed by a question, “Do you want to create it? Yes/No?”
Yes, you do want to create the script! 
Click:  “Yes.”

6. Now the “Edit Script” window will appear.
Copy the following code and paste it into your script editor window:
NOTE: It is important to use the code exactly as shown below, including line breaks and spaces.

'Alnoor Cassim - xkzero - Require Additional Fields in Customer Maintenance

'Init VARs
sEmailAddress = "" : sPriceLevel = "" : sCustomerNo = "" : nCreditLimit = 1

'Check if current user is member of the Finance_Dept role.
In_Finance = oSession.AsObject(oSession.Security).IsMember("Finance_Dept")

'Enforce new customer rules for any non-Finance_Dept roles (In_Finance = 0)
'If we wanted to run only for Finance_Dept check for In_Finance > 0

If In_Finance = 0 and oBusObj.EditState = 2 Then

	retVal = oBusObj.GetValue("EmailAddress$", sEmailAddress)
	retVal = oBusObj.GetValue("PriceLevel$", sPriceLevel)
	retVal = oBusObj.GetValue("CustomerNo$", sCustomerNo)

	If sPriceLevel = "" Then

	sMsg =	"The Price Level is blank." & vbCrLf & _
		"Please enter a Price Level for customer " & sCustomerNo
	'retVal = oSession.WriteLog ("M", Replace(sMsg,vbCrLf,CHR(138))'Write to Activity Log
	retVal = oScript.SetError(sMsg) 'Prevent the Accept and show the message
	retVal = oScript.InvokeButton("fldr.pAddl") 'Click the Additional tab folder
	Exit Sub

	End If

	If sEmailAddress = "" Then

	sMsg =	"The Email Address is blank." & vbCrLf & _
		"Please enter an Email Address for customer " & sCustomerNo
	'retVal = oSession.WriteLog ("M", Replace(sMsg,vbCrLf,CHR(138)) 'Write to Activity Log
	retVal = oScript.SetError(sMsg) 'Prevent the Accept and show the message
	retVal = oScript.InvokeButton("fldr.pMain") 'Click the Main tab folder

	Exit Sub

	End If

	'Put the Customer on Credit Hold and set the Credit Limit = $1.
	'This will overwrite whatever was previously entered for these 2 fields.
	retVal = oBusObj.SetValue("CreditHold$", "Y")
	retVal = oBusObj.SetValue("CreditLimit", nCreditLimit)

End If

How does this work? 

To better understand the logic behind the script’s functionality, read this:
“Init VARs”
In the first section, we “initialize” (init) the variables to prepare for use later in the script.

“IF/THEN/ELSE” 
Through these stated conditions, we run our main logic. In this particular script, we want the conditions to be as follows:

Check if we’re a member of the role called “Finance_Dept.”

Check if the customer on the screen is a “new customer.”

How  do we find out if the current user is a member of “Finance_Dept” role?  

Use the special IsMember() security function you see in the code block. If the value to the left of the equals sign is more than 0 the current user is a member of that role. Otherwise, they are not a member.

How  do we find out if the customer is new or existing?

We do this  by checking the  “edit state” as indicated by the  following  values:

2 = new customer
1 = existing customer
0 = no customer on the screen

Get the values of the Price Level, Email Address, and Customer No fields.
If either of the first 2 fields are blank, take these steps to prevent the customer from being saved:

  1. Check for a blank price level.
    Notice the If / End If block to check for a blank Price Level.
    Execute the SetError(msg) function.
    This is a 2-part function that will both prevent the Accept button from being clicked and show a message box to the user with your own user defined message. A good message could be this, “Oops! The Price Level is blank.”
  2. Auto-click the Additional tab folder.
    Run the InvokeButton() function as a way of auto-clicking the Additional tab folder.
  3. Exit the script.
    To immediately exit the script, run the Exit Sub command This is not required for preventing the Accept.
  4. Check for a blank email address.
    To check for a blank email address, the If / End If block runs similarly, except the Main tab is auto-clicked.
  5. Write message to the Activity Log.
    Do you want to write the message to the Activity Log? Then remove the single quote character from the line where you see the WriteLog() function.
  6. Put the customer on Credit Hold and set the Credit Limit.
    Next, Put the Customer on Credit Hold and set the Credit Limit = $1
    The two SetValue() lines at the end accomplish these tasks.
    Note: You should not follow this up with a Write() command because the Pre-Write script runs before the standard Sage 100 Write() command runs, which will write / save the record for us. If we ran on Post-Write event then we would issue a Write().

    You’re  almost  done!   

  7. Accept the script.
    Now you can “Accept” the script.

    This will return you to the “Add Script” window.
  8. Return to the User Defined Scripts window.
    Close this screen and return to the “User Defined Scripts” window.
    To do so, click:  “OK.”
  9. Go to the Script Compile window.
    Next, go back to the main “User Defined Field” and “Table Maintenance” window.
    To do so, click:  “Close.”Here, you must also click “Close” again to see the “Script Compile” window.
    IMPORTANT:  When the “Script Compile” window appears, click the Compile” button.
    You can further edit the script, if necessary.  To do so, from the “Custom Office/Main” menu, choose the “User Defined Script Maintenance” task. When done editing the script, to syntax check the script, click “Check Script,” then to save the script, click “Accept.”
  10. Compile. Close.
    The next step is to click the “Compile” button in the lower-left of the screen. When the Script Compile window appears, click the Compile button again followed by the Close button.
    You’re done!

This is the second of many scripting examples we will share through the xkzero blog. If you need help with scripting, programming or technical issues with your Sage ERP, no matter how complex, please feel free to contact us: 

xkzero Technical Services
Email: info@xkzero.com
Call: 847-416-2009

 

Use Scripting to Change All Shipping Warehouses on an Order

Scripting Tips for Sage 100 ERP

Blogger: Alnoor Cassim, xkzero Technical Services

When creating Sales Order Entries in Sage 100 ERP, have you ever keyed in many lines of shipping information on multiple orders only to learn that the product needs to ship from a different warehouse than the location originally entered?

If so, you know that manually correcting the warehouse code on each order line is quite time consuming (and not exactly fun). You may have even thought, “If only there were a way to change the warehouse information just once to apply the update to all the line warehouses… Good news! This wish can come true by creating a fairly straightforward script.

What kind of script do you need to create to change all warehouses in an order?

Create an event script that runs when the Warehouse Code on the Header is changed.

How do you create this script?

Follow these steps:

  1. Go to the Custom Office/Main Menu, and make these selections:
    “User-Defined Field”
    “Table Maintenance”
  2. Under the “Sales Order” heading, choose “SO Sales Order Header.”
  3. Right-click and choose “User Defined Scripts.”
  4. In the “User Defined Script” window, click the “Add” button. This will open the “Add Script” window.
  5. In the “User-Defined Script – Add Script” window, as shown below, make these choices:
    Event: “Column Post-Validate”
    Field: “WarehouseCode”
  6. Type in a name for your script, such as this:
    “Update Line Whse from Hdr Whse”
    Scripting-Warehouse-1
  7. You will see a message indicating that the script file does not exist, followed by a question, “Do you want to create it? Yes/No?”
    Yes, you do want to create the script! Click “Yes.”
  8. Now the “Edit Script” window will appear. Copy the following code and paste it into your script editor window. Note: It is important to use the code exactly as shown below, including line breaks and spaces.
'Init VARs 
newWhse = value : sItemCode = "" : sItemType = "" 

'If an existing sales order and not during update 
If oBusObj.EditState = 1 and oSession.Updating = 0 Then 
	Set oLines = oBusObj.AsObject(oBusObj.Lines) 
	
	retVal = oLines.MoveFirst() 
	
	sMsg = "Would you like to update the Whse Lines to Whse Code " & newWhse & "?" 
	retMsg = oSession.AsObject(oSession.UI).MessageBox("",sMsg,"Style=YesNo") 
	
	If retMsg = "NO" Then 
		Exit Sub ' 
	Else 
		retVal = oLines.MoveFirst() 
		'Loop through all the lines and choose regular inventory item lines only 
		Do Until CBool(oLines.EOF) 
			retVal = oLines.GetValue("ItemCode$", sItemCode) 
			retVal = oLines.GetValue("ItemType$", sItemType) 
			
			If sItemType = "1" Then 
				retVal = oLines.SetValue("WarehouseCode$", newWhse) 
				retVal = oLines.Write() 
			End If 
			
			retVal = oLines.MoveNext() 
		Loop 
	End If 
End If

How does this work?

To better understand the logic behind the script’s functionality, read this:

“Init VARs”
In the first section, we “initialize” (init) the variables to prepare for
use later in the script.

“IF/THEN/ELSE” 
Through these stated conditions, we run our main logic. In this particular script, we want one of the conditions to be “existing order.”

How do we find out if the order exists?

We do this by checking the “edit state” as indicated by the following values:

2 = new order
1 = existing order
0 = no order on the screen


Next, to find out if you have landed on a regular inventory item, follow these steps:

  1. Go to the Lines object. Move to the first line and start a loop.
  2. Check the value of the “Item Type” field.
  3. If it is a regular inventory item, set it with the new value and then write the line. Continue until all the lines are completed.

You’re almost done! 

Now you can accept the script. This will return you to the “Add Script” window.

  1. To close this screen and return to the “User Defined Scripts” window, click “OK.”
  2. Next, to go back to the main “User Defined Field” and “Table Maintenance” window, click “Close.” Here, you must also click “Close” again to see the “Script Compile” window.
  3. IMPORTANT: When the “Script Compile” window appears, click the Compile” button.

Note that you can further edit the script, if necessary. To do so, from the “Custom Office/Main” menu, choose the “User Defined Script Maintenance” task.


This is the first of many scripting examples that we will share through the xkzero blog. If you need help with scripting or other technical issues with your Sage ERP, no matter how complex, please feel free to contact us: 

xkzero Technical Services
Email: info@xkzero.com
Call: 847-416-2009

Alnoor Cassim to Lead Sage 100 ERP Scripting Class at 90 Minds Conference

90 Minds 2014

The 90 Minds Consulting Group annual conference in San Diego is fast approaching. Scheduled from Thursday evening February 19 through Saturday February 21, the Meeting of the Minds 2015 conference is a members-only gathering for this group of highly experienced Sage 100 ERP consultants.

This year’s conference will kick off with a two-day (Wednesday 2/18 and Thursday and 2/19) Basic Scripting class taught by xkzero Director of Technical Services, Alnoor Cassim.

The 90 Minds Consulting group is a group of independent ERP consultants located across North America focused primarily on Sage 100 ERP (formerly MAS 90/MAS 200).

The group meets daily via online private messaging to brainstorm and collaborate on more difficult client issues. The 90 Minds conference is open to members only. Find more about membership and its benefits here.

 

facebooktwittergoogle_plusredditpinterestlinkedinmail

xkzero Hires New Director for Sage 100 ERP Technical Services

Chicago, IL (December 3, 2014)—

Chicago-based software developer xkzero today announced the hiring of Alnoor Cassim as director of technical services. Cassim will serve a leadership role in xkzero Technical Services, a new initiative that provides technical services, custom software development and advanced system support for Sage 100 ERP business partners and end user customers.

Alnoor-LinkedInImage“Alnoor has an unparalleled reputation throughout the Sage community to solve complex business and technology problems,” said Paul Ziliak, xkzero co-founder.

“Beyond his high level technical acumen, Alnoor is known for his professionalism and an incredibly generous nature when working with other Sage business partners. His leadership will help companies grow by maximizing their business systems capabilities using the most advanced technologies in our industry,” said Ziliak.

Cassim will direct an experienced and growing team offering solutions and services to the Sage VAR/business partner community and end user companies. Offerings will include custom ProvideX programming, custom mobile ERP development, diagnosis, scripting, BOI, SQL programming, and system integrations with SData and xkzero Web Services, .NET and ASP web development.

“I’ve had my eye on xkzero for the last several years. I am passionate about growing businesses, identifying and creating solutions to streamline processes, improve productivity, and increase profitability. Few other companies have invested like xkzero has in the development of new technologies such as mobile, web services and universal search,” Cassim said.

Cassim said, “Having these leading edge tools along with traditional resolute services like .NET, scripting, SQL development, integrations, and hardware will unquestionably help grow Sage business partners and their customers. I’m definitely looking forward to this and very excited!”

Cassim spent nearly 15 years as Senior Customer Support Engineer for Sage North America, specializing in the Sage 100 ERP product. As a sole proprietor since 2009, Cassim has also been an integral part of the 90 Minds Consulting Group, a nationwide collective of primarily Sage 100 ERP (formerly MAS 90/200) consultants. Beyond consulting and development, he has provided classroom and webinar training on scripting, Crystal Reports, SQL Server, and advanced technical support techniques.

About xkzero:
xkzero is a mobile app developer and provides advanced technical services, programming, development, support and consulting services to other Sage Partners and resellers for Sage 100 ERP, Sage ERP X3 and Sage 500 ERP. xkzero has a long history of working well with other Sage Resellers providing outsource services to supplement their current team. xkzero’s applications include iSales 100, xkzero Mobile Commerce, GetX Search for Sage 100 ERP, xkzero LTL Shipping powered by Echo, and EBM 100. xkzero delivers technology solutions that are easy to deploy and have a powerfully positive impact on the bottom line of small and mid-sized businesses.