Macros & Workflows

Written by Richard on December 10, 2013

Automatically link new work orders to line items in the customer order entry window

The New Work Order feature in Customer Order Entry (and Order Management Window) allows you to create and link work orders as you enter the sales order into VISUAL.

Infor VISUAL ERP

This feature can save you quite a bit of time for situations where creating work orders on the fly from the order entry screen makes sense.  It allows you enter the sales order, create the work order and link (allocate) it all in one step. 

If linking work orders to customer order lines is common practice in your work place you might want to consider this option - Again, if it makes sense for your business

If you do use this feature or plan to use it - this macro might be of use to you.

Chances are, not all line items of a sales order require a work order....

  • Some items may be purchased.
  • Some items may be stocked items
  • Some items fall under specific planning policies
  • Some items are service charges
  • etc...

So the decision to link work orders to customer order lines may rest on the person entering the sales order.  You can streamline this process (and make the decision automatically) coding the business logic into an OnSave macro.

The following macro will automatically create and link work orders to line items for FABRICATED parts.  Simple requirement - but you get the idea

Macro breakdown

The Database Connection parameters:  You will need this to for the database lookup - which we will be doing to verify whether the line item meets the criteria to create a new work order.

strServerName="E N T E R   Y O U R   S E R V E R    N A M E"
strDatabase="E N T E R    Y O U R    V I S U A L    D A T A B A S E    N A M E"
strUserName="E N T E R    Y O U R    R E A D O N L Y    U S E R    I D"
strPassword="E N T E R    Y O U R    P A S S W O R D"

'*** Connection string for SQL Server
strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"

set rs = CreateObject("ADODB.Recordset")

The remaining code uses the LINES object which allows us to read the line items (grid) of the customer order.  As we cycle through the lines, the macro will do a database lookup to determine whether the PART meets our requirement to create a new work order.  If a work order is required, we check the "New W/O" checkbox.  To do this we set the ADD field to "true".

FYI: In macro terms, the variable ADD represents the "New W/O" checkbox you see in the grid.

Now because whenever you set the New W/O checkbox, VISUAL prompts the user to set the status of the new work order (Unreleased, Firmed or Released).  To accept the defaults, we use the SendKey function to hit the enter key. 

Infor VISUAL ERP - New Work Order Defaults
New Work Order Prompt - Use the Sendkeys to "hit" the OK button

 

set objShell = CreateObject("WScript.Shell")

Set ORDERLINES = LINES.Value
Lcnt = ORDERLINES.Count

Set ORDERLINE = ORDERLINES(Lcnt-1).VALUE
Set OrderLines = LINES.VALUE

For i = 0 To (ORDERLINES.COUNT - 1)

    Set ORDERLINE = ORDERLINES(i).VALUE
    
    PartID=ORDERLINE("PART_ID")
    
    '*** Check SUPPLY_TYPE to make sure that line has not been allocated yet
    SupplyType=ORDERLINE("SUPPLY_TYPE") & ""
    
    '*** Verify that the line item is a VISUAL Part and that
    '*** it has not been allocated yet
    if PartID<>"" and SupplyType="" then
        
        '*** Database lookup to check whether part meets
        '*** new work order requirement
        '*** In this case if the part is FABRICATED
        
        SQL="SELECT FABRICATED FROM PART WHERE ID='" & PartID & "'"
        rs.open SQL, strcnn

        if not rs.eof then
            rs.movefirst
            if rs("FABRICATED")="Y" then
                
                '*** Part meets requirement.
                '*** Check New W/O checkbox
                ORDERLINE("ADD")=true

                '*** Send the ENTER key to accept
                '*** new work order defaults
                objShell.SendKeys("~")

            end if
        end if
        rs.close
    end if
Next

Download the macro and try it yourself

Written by Richard on February 7, 2013

Validating your custom drop-down list

The Customizable UDF (CUDF) feature in VISUAL allows you to create and define your own list of fields that you can integrate into the VISUAL interface and capture information particular to your business.  

The drop-down list is one such field you can add.  When you create the down-down CUDF you have the option to populate the list manually or link it to data in your VISUAL database.

Infor VISUAL ERP - Custom User Defined Fields

The drawback to these drop-down lists is that VISUAL does not validate the information entered against the available options in the list.  It's of no fault to VISUAL.  I've been calling this field a drop-down list but it's actually a ComboBox.  A combobox is a combination of a drop-down list and a single-line textbox, allowing the user to either enter data directly or choose from the list of existing items. 

So basically it's a free form field - allowing the user to enter anything.  This may be a problem if you are depending on your users to only enter values that are defined in the list.

To enforce this you will need to do this yourself via a database trigger, workflow, macro, or a big heavy stick.

So before you grab that stick - the example below may give you an idea how you can handle this using a macro. 

The macro validates the data entered in a CUDF by doing a database lookup on the USER_DEF_FIELDS table which contains down-down definitions.  Apart for the database connection information - you will need to provide the following macro variables:

Variable: UDF_Caption
This is the caption (label) of the drop-down field.  In my example I'm using "Customer Service Rep".  NOTE: I don't use the UDF-XXXXXXX identifier because I seen VISUAL renumber these IDs.

Variable: UDF_VEModule
Specify the VISUAL program/module (VMORDENT, VMPURENT, etc)

Variable: UDF_Area
Where in the module the CUDF is contained.
Header area = ""
Grid Line Item = "tblColLineItem"
Delivery Schedule = "tblEditDelSched"

Variable: UDF_VALUE
The CUDF string (value) to validate.  In my example, I'm using udfStrCombo1 which is the CUDF field variable containing the Customer Service Rep value

In my example, I'm validating the value entered in the Customer Service Rep field which is a drop-down list.  Download the macro

 

'********************************************
'* DATABASE CONNECTION TO SQL SERVER

strServerName="E N T E R    N A M E    O F    D A T A B A S E    S E R V E R"
strDatabase="E N T E R    N A M E    O F    D A T A B A S E"
strUserName="E N T E R    U S E R N A M E"
strPassword="E N T E R    P A S S W O R D"

strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"

'********************************************

'Variable for caption (label) of the CUDF to validate
UDF_Caption="Customer Service Rep"

' Variable to indicate where the CUDF is located in the module
' empty string = Header area
' tblColLineItem = Grid line item 
' tblEditDelSched = delivery schedule
UDF_Area=""

'Variable to indicate the VISUAL program(module)
'VMORDENT = Customer Order Entry
'VMPURENT = Purchase Order Entry
' etc....
UDF_VEModule="VMORDENT"

'Variable for string value (user input) to validate
UDF_VALUE=udfStrCombo1

set rs = CreateObject("ADODB.Recordset")
set rsCHK = CreateObject("ADODB.Recordset")

SQL="SELECT STRING_VAL  " & _
"FROM USER_DEF_FIELDS UDF " & _
"WHERE UDF.PROGRAM_ID='" & UDF_VEModule & "' AND UDF.LABEL = '" & UDF_Caption & "' AND UDF.DATA_TYPE = 1005 AND ISNULL(UDF.TABLE_ID,'') = '" & UDF_Area & "';"
rs.open SQL, strcnn
if not rs.eof then
 rs.movefirst
 arrList=split(rs("STRING_VAL"),",")
 if ubound(arrList)>0 then

  SELECT CASE arrList(0)
  
   CASE "l=1"
    '*** STATIC LIST
    SQL="SELECT UDF.STRING_VAL FROM USER_DEF_FIELDS UDF LEFT OUTER JOIN USER_DEF_FIELDS AS UDF1 ON UDF.ID = UDF1.ID WHERE (UDF.PROGRAM_ID = 'STATIC_DATA') AND (UDF.STRING_VAL = '" & CUDF_VALUE & "') AND (UDF1.LABEL = '" & UDF_Caption & "') AND  (ISNULL(UDF1.TABLE_ID,'') = '" & UDF_Area & "') AND (UDF1.DATA_TYPE = 1005) AND (UDF1.PROGRAM_ID = '" & UDF_VEModule & "')"

   CASE "l=2"
    '*** DYNAMIC LIST FROM DB
    UDF_DBTable=mid(arrList(1),3,100)
    UDF_DBCol=mid(arrList(2),3,100)
    SQL="SELECT " & UDF_DBCol & " FROM " & UDF_DBTable & " WHERE " & UDF_DBCol & "='" & CUDF_VALUE & "'"

  END SELECT

  rsCHK.open SQL, strcnn
  if rsCHK.eof then
   msgbox "Error: user input does not match available values in list"
  else
   msgbox "We're good"
  end if
  
  rsCHK.close

 end if
end if
rs.close

 

Written by Brent on October 30, 2012

Keeping your macros and configuration (ini) files current across your VISUAL network

VISUAL macros are computer-specific - meaning if you create a macro on one computer it won't be accessible from another computer unless you copy it and place on that computer.  This may not always be true in a Citrix/Terminal Services environment - but let's assume this is not the case. 

The same holds true for the VISUAL configuration files.  I'm talking about those "ini" files that hold your default settings, grid layouts, screen sizes and position, etc.

If you want every VISUAL user to have access to all macros and also want them to have the same VISUAL configurations - you will need to physically copy the files (macros and INIs) to each computer on your network.  Now consider every VISUAL module can have it's own set of macros and configuration file - that's a lot of files to manage across your network!  Now think about keeping those files current of any changes or modifications - giving you a headache yet?

One way to manage this is through the use of a login script.  The script can copy over the default configuration files and all current macro revisions from a central repository down to the user's computer or profile.

Here's an example of what that script might look like - modify it to suit your environment.  In this script, it will copy over the current macro revisions and only the configuration files that do not exist in the user's local directory.    

Download LoginScript


Brent Fanguy is our special guest author at visualnuggets.com and is the IT Director at CORTEC Fluid Control. You'll find Brent contributing and assisting fellow VISUAL users on LinkedIn and Yahoo forums.

Connect with Brent online at LinkedIn 

Written by Erik on October 9, 2012

Macro-level Adjust-In control for Inventory Transaction Entry

VISUAL installations that run in Actual Costing live and die with the control of transactional costs and inventory layers in the system, and under normal conditions this is mostly a no-brainer with a little training and running some semi-automatic periodic processes. 

Sooner or later, you’ll have to manually Adjust In parts because of inventory, material moves, or some other reason.

And then there’s the human element.   Sigh.

infor ERP VISUAL - Inventory Adjustments
"Not a problem"

When you have non-consumable parts that you carry in your own inventory, these should generally be assigned a cost (if they don’t already have one) if you adjust them in through Inventory Transaction Entry, in order to make sure your inventory layers show the correct value.  If you have Customer-Supplied parts (or other materials you technically aren’t carrying inventory cost on), these typically SHOULD NOT have a cost entered in at transaction time.

Sounds easy, right? 

But since Inventory Transaction Entry has no controls to differentiate these parts by default, someone can accidentally enter in values for Customer-Supplied parts and zero value for regular inventoried goods.  If you don’t find these kinds of mistakes early, your part costing, and eventually your estimating / Engineering Masters will not reflect reality, inventory costs can get pretty screwy, and Accounting may show up at Receiving’s door with torches and pitchforks.

What to do?

We ran into this issue early on after migrating to VISUAL from our legacy ERP.  Since we were already coding our Customer-Supplied parts with a prefix of ‘CS’ to make them easy to spot, we came up with an easy way to help prevent the issues altogether with a simple macro:


*******************************
' Inventory Transaction Entry - OnSave Macro
' Prevent saving of a transaction entry with zero cost
' Erik Kjellquist

if (ADJUSTMENT_IN = True) then

    dim text
    dim prefix

    prefix = Left(PART_ID,2)   ' Determine if part has a ‘CS’ prefix

    ' Flag non-CS parts that have a zero cost
    if ((MATERIAL_UNIT_COST + FIXED_COST) = 0) AND (NOT (prefix = "CS")) then

        MACRO_MESSAGE="Total cost for an Adjust In cannot be zero for " & PART_ID & _
        " because it is NOT a customer-supplied part."
        MACRO_SUCCESS=False

    ' Flag CS parts that have a greater than zero cost
    elseif ((MATERIAL_UNIT_COST + FIXED_COST) > 0) AND (prefix = "CS") then

        MACRO_MESSAGE="Total cost for an Adjust In MUST be zero for " & PART_ID & _
        " because it is a customer-supplied part."
        MACRO_SUCCESS=False

    end if

end if
*******************************
 

This macro uses the OnSave function to allow or prevent a user from saving a transaction based on the details.  We’re looking only at Adjust Ins, in this case the ADJUSTMENT_IN variable, and evaluating whether a CS part is being adjusted in with a zero cost or a non-CS part with a non-zero cost, respectively.  If either is false, MACRO_MESSAGE is given an appropriate string, and MACRO_SUCCESS is set to false, which prevents the transaction from saving.

The macro assumes that parts with a ‘CS’ prefix are customer-supplied in our case, but many companies may not be able to label their parts as such.  You could also label parts as Customer-Supplied using User-Defined fields, Commodity codes, store them in a specific warehouse/location, etc in Part Maintenance or another linked table.

However you define CS parts, this will help anyone adjusting in inventory from making mistakes relative to costing, should help relieve a few headaches down the road, and will prevent those square pegs from going into round holes!


Erik Kjellquist is our special guest author at visualnuggets.com and is the IT Manager at Audiosears Corporation. You'll find Erik contributing and assisting fellow VISUAL users on LinkedIn and Yahoo forums.

Connect with him online at LinkedIn 

 

Written by Richard on October 5, 2012

Setting the default expense account for consumables

You and I both know that, out of the box, VISUAL does not allow you to specify a default GL expense account on a consumable part.  And that you have to manually specify the expense GL account every time you purchase it.   AUGH!

Yes, there is a work around where you can set the default GL expense account at the VENDOR level.  This may be okay if you expense all consumables to the one GL account.

Infor VISUAL ERP - Vendor Maintenance
Setting the "Default Expense Account ID" in Vendor Maintenance (under the ACCOUNTING tab)

However, this workaround doesn't work if you buy more than one type of consumable from the same vendor and each type of consumable needs to be expensed differently.  It would be great if VISUAL allowed us to define the GL expense account at the PART level - this wouldn't be an issue.

Here's one way to get around this. 

It involves using a user defined field (UDF) in PART MAINTENANCE and a OnSave macro in the PURCHASE ORDER ENTRY window.  The UDF can be used to enter the expense account and we can have the macro look this up when entering a purchase order for a consumable part. 

  1. In PART MAINTENANCE, designate one of the standard UDFs to be used to enter the expense GL account for a consumable part.  You could also use a custom user defined field and create a drop down list linked to your GL accounts table.

    Infor VISUAL ERP - Part Maintenance

  2. Create an OnSave macro in the PURCHASE ORDER ENTRY window that will look up and use the GL account entered in the UDF (created in step 1) to populate the GL Expense Account ID column of the purchase order.

An OnSave macro like the following would work: (mind the line wraps!)

strServerName="E N T E R    N A M E    O F    D A T A B A S E    S E R V E R"
strDatabase="E N T E R    N A M E    O F    D A T A B A S E"
strUserName="E N T E R    U S E R N A M E"
strPassword="E N T E R    P A S S W O R D"

strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"

set rs = CreateObject("ADODB.Recordset")

Set ORDERLINES = Lines.Value
LCnt = ORDERLINES.Count

If LCnt>0 then
    For i = 0 To (LCnt - 1)
        Set ORDERLINE = ORDERLINES(i)

        if len(Orderline("PART_INFO") & "")>0 then

            PartID=Split(Orderline("PART_INFO")," - ")

            SQL="SELECT USER_1, CONSUMABLE FROM PART WHERE ID='" & PartID(0) & "';"
            rs.open sql, strcnn

            ExpAcctID=""

            if not rs.eof then
                rs.movefirst

                if rs("CONSUMABLE")="Y" then
                    ExpAcctID=rs("USER_1")
                end if

            end if

            if len(ExpAcctID & "")>0 then
                Orderline("EXPENSE_GL_ACCT_INFO")=ExpAcctID
            end if

            rs.close

        end if

    Next

end if

This macro assumes that we're using the standard user defined field 1 for the expense GL account.  You may want to beef up this script to include validation checks - ie ensure that the expense GL account id exists, prevent the changing of the GL accounts on PO lines that have already been received, etc.  This should get you going in the right direction.

Now you're able to set your default expense account on each consumable part.

Written by Anne Jan on October 2, 2012

How to run a macro from the manufacturing window toolbar

For some of you techies this may be a well know option in VISUAL, but it has taken me forever to figure this out so I am posting it anyway.

Some of the programs in VISUAL won't allow you to execute a .vms macro from a button in a User toolbar (ie: concurrent scheduler, manufacturing window).  For the manufacturing window, for example, this means you need to open the header card and click on the macro button to run a macro.  Not particularly user-friendly.

However, there are ways to execute a .vbs (vbscript) file instead.  Obviously, this will not enable you to do as much with the data in your screen buffer as a .vms macro file, BUT each VISUAL program does send a number of parameters to the macro you execute from the toolbar and for some cases this may be all the functionality you need.

Here's how you set it up:

  • Create a macro and save it as a .vbs file.
  • Infor VISUAL ERP - Toolbar MaintenanceIn toolbar maintenance, make an entry for the manufacturing window:

    Select the program you want, enter a ToolbarId and position. As a Bitmap, I usually pick one of the standard VISUAL bmp files - that way you are sure it has the correct dimensions. As execute command, use wscript.exe "[path to your macro]\[macro file name.vbs]".  Make sure you tick the Use Key in Cmd Line tick box.
  • You will need to include UserToolbar=Y in the Visual Mfg part of your Visual.ini or Preference Maintenance.
  • This will give you a new icon in the toolbar. Click this icon to execute your macro.
  • In the macro script, you will have to include some logic to retrieve the separate parameters from the arguments that VISUAL provides. See the code below for an example how you can do this.  This example is for the Manufacturing Window - in other VISUAL programs the arguments will be different, for example: a Customer Order Id and the line you selected.

 

Infor ERP VISUAL - Toolbar Maintenance window
Entry in Toolbar Maintenance


Example macro code (save as .vbs)

cntArgs = WScript.Arguments.Count

If cntArgs > 0 Then

        'Display the total string of arguments:
        msgbox WScript.Arguments.Item(0)

        'Split the arguments on the , :
        arrSplitLine = Split(WScript.Arguments.Item(0), ",")
 
        'Retrieve separate parameters:
        DataBase = right(arrSplitLine(0),len(arrSplitLine(0))-1) 'Database = first argument
        UserId = arrSplitLine(1) 'User Id = second argument
        PassWord = arrSplitLine(2) 'Password = third argument
        WoType = arrSplitLine(3) 'Workorder Type = fourth argument
        WoBaseId = arrSplitLine(4) 'Workorder Base Id = fifth argument
        WoLotId = arrSplitLine(5) 'Workorder Split = sixth argument
        WoSplitId = arrSplitLine(6)'Workorder Split Id = seventh argument

Else
        
        msgbox "No arguments"

End If


Anne Jan Breman is our special guest author at visualnuggets.com. Anne Jan is a consultant for Quartess SCS, an Infor Channel Partner based in the Netherlands. 

Connect with Anne Jan online at LinkedIn 

Written by Richard on August 19, 2012

Macros in VISUAL CRM? Well, not exactly...

The use of macros in VISUAL is common practice as it allows you to do many things inside and outside of VISUAL, such as, automate tasks, enforce business rules, interact with external applications, validate data, and much more.

 

Why Not
Why Not?!

But VISUAL Macros are only available from within the VISUAL Enterprise application modules - i.e. Manufacturing.  You will not find the MACRO menu in VISUAL CRM

 

But don't fret...there something else.

It's called ACTION MENUS.  While they may not be exactly macros - you can do quite a bit with a little programming know how.  Action Menus allow you to run executables from VISUAL CRM and pass parameters.  The available parameters are dependent of the area you happen to be in within CRM.

As usual, I always like to show features/functions using examples.   And this post is no different...

A while back in the VISUAL forums, someone asked whether VISUAL CRM had any macro capabilities as they were wanting to push account information from the Contact Center of CRM to the  Estimation Window of VISUAL.  Responses quickly came back stating that MACROs were not available in CRM (a true fact).

But David Sheriff, from Visual Business Solution, offered up his solution of using CRM's Action Menu - which, I'm sure, opened up a whole new world of opportunities for VISUAL users.

So, making it simple, let's say I want to create new quote using VISUAL's Estimation window from the accounts grid in CRM's Contact Center.  I also want CRM to "push" the account ID, NameContact First and Last Name into the new estimating window.

The solution involves the following elements:

  1. An executable file to "push" the CRM data
  2. An Action Menu item in CRM
  3. A VMX file to launch the VISUAL's Estimation window
  4. An OnLoad macro which will execute and read the flat file containing the "pushed" data

 
The Executable File

The executable file is written in vbscript to do the following:

  1. Query the database using the ID parameter passed by the ACTION MENU.  The query will return the information needed to be pushed - Account Name, Contact First and Last Name.
  2. Create a flat file of the data that will, in turn be read by the Estimation Window's OnLoad macro.
  3. Create and execute a VMX file to launch the Estimating Window.

Download the the zipped executable file
Be sure to modify the script to include you database credentials and file directory path


The OnLoad macro

The Onload macro file is fired automatically when the Estimating Window loads from the CRM call.  The macro reads the data from the flat file that was created from the executable file and places the values in the following fields:

  • USER DEFINED 1 --> CRM's Account ID
  • NAME --> CRM's Account Name
  • Contact Information --> CRM's Contact First and Last Name

Download the zipped Onload macro
You will need to create a quote in the Estimation window using Quote ID "CRM_QUOTE".  You only need to do this once in your setup and nothing should be in this quote.  It's simply used as a template to fire up the OnLoad macro.


Infor ERP VISUAL - CRM navigationPutting it all together

  1. Place the OnLoad macro file in the proper VISUAL directory so that the system recognizes it
  2. In the navigation area of CRM, select the "Navbar and Plug-Ins" item from the ADMIN men.
  3. In the Navigation Bar and Plug-In Customization window, click on the ACTION MENU tab to add the new MENU that will launch the executable file.
  4. Be sure to enter the following parameters.  Please note that some information will need to be changed as per your VISUAL environment.  The hit the OK button to complete.

    Menu Text: New Estimate for Account   <-- change text if you like
    Show For: Account
    Command: "C:\visual\VMX\NEW_ESTIMATE.vbs" %USER% %ID% %DB%

    *** The path to the executable file - change as necessary ***

    The %USER% %ID% %DB% are the parameters that we're passing to the executable file.  Be sure to place in exact order as shown


    Infor ERP VISUAL - CRM Navigation Bar and Plug-In Customization

 
Seeing it in action

  1. From the CONTACT CENTER, hightlight one of your prospects or accounts in ACCOUNTS window grid.
  2. Now if you go to the ACTIONS menu, you should now see the menu item you created.  -  In this case "New Estimate for Account".  Go ahead and select it.

    Infor ERP VISUAL - CRM Accounts
  3. You will then see VISUAL launch the ESTIMATING window with information pre-populated with the information "pushed" from CRM.  In this case, the name, contact and the CRM account id.

    Infor ERP VISUAL - Estimating Window

 

This was obviously a simple example (using basic fields) - but I'm sure you can see how powerful this can be.

 

Written by Richard on June 18, 2012

Using workflow to prevent an order from being deleted

VISUAL macros are great.  I use them all the time for automating tasks and validating data.  And with their ability to fire automatically - it makes them an invaluable feature of the application.

However, one thing that macros can't do is prevent an order from being deleted.

That's where the workflow comes in.

With VISUAL workflows you can capture the ACTION against an order, in this case, the DELETE.  With that you can base your workflow on how to proceed.  For instance, you can create a process where only AUTHORIZED personnel can delete an order.

Below is a workflow that checks whether the user is a supervisor before deleting an order.

 

Infor VISUAL ERP Express - Workflow  
 
In the RULE captioned "CO Delete", I capture the ACTION (the delete) using the following:

  Table:  WORKFLOW_STEP 
  Column: ACTION 
  Comparison:  "="    (equal sign) 
  VALUE:  Delete 

Here's a screenshot of the Rule:

Infor VISUAL ERP Express - Workflow Rule Property
Workflow Rule Property window

 
So once you capture this ACTION, you can then proceed and enforce your business process with the rest of your workflow.

Now stop users from "accidentally" deleting orders or at least control who can and who can't delete orders  
 
Infor VISUAL ERP Express - Workflow Return Error

 

Written by Richard on May 25, 2012

Calling a stored procedure from a macro

Whenever possible, I like to use stored procedures in macros when the process involves some sort of database update or manipulation.  This way it limits access to the code and reduces the chance of someone "accidentally" changing it within the macro dialog window.  Yes - they can still throw a wrench into the macros script  - but at least I can contain it.

Calling a stored procedure is relatively easy to do. 

Here's a walk through...

Keeping it simple, the following script will create a stored procedure (named SP_UpdateUDF) to update the USER_10 field for a part in the PART table.  The stored procedure has ONE parameter which is the part id to update.  The script also grants access to PUBLIC to give everyone permission to execute the store procedure.

CREATE proc dbo.SP_UpdateUDF
@I_PartID VARCHAR(30)
AS
SET NOCOUNT ON
--For synchronization machine id placement
UPDATE PART SET USER_10='COMPLETE' WHERE  ID = @I_PartID
SET NOCOUNT OFF
GO
GRANT EXECUTE ON dbo.SP_UpdateUDF TO PUBLIC
GO

 
Now here's the VISUAL macro to call the stored procedure:

Const adParamInput = &H0001
Const adVarChar = 200

Dim strServerName
Dim strDatabase
Dim strUserName
Dim strPassword
 
strServerName="E N T E R    N A M E    O F    D A T A B A S E    S E R V E R"
strDatabase="E N T E R    N A M E    O F    D A T A B A S E"
strUserName="E N T E R    U S E R N A M E"
strPassword="E N T E R    P A S S W O R D"

strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"

Set Conn = CreateObject("ADODB.Connection")
conn.open strcnn

Set Cmd = CreateObject("ADODB.Command")
Cmd.ActiveConnection = conn

' Set up the parameter for our Stored Procedure *** watch the line wrap ***
Cmd.Parameters.Append Cmd.CreateParameter("I_PartID", adVarChar, adParamInput , 30, "E N T E R    Y O U R    P A R T    I D")
Cmd.CommandText = "SP_UpdateUDF"
Cmd.Execute

 

Written by Richard on January 9, 2012

Notifying the shipping department that a customer is ON HOLD sooner rather than later

With your shipping schedule in hand, you call up the next sales order to go out the door.  You print up the picking list and start pulling parts from stock.  Some items require you to go out on the shop floor with the forklift.  No problem - you're an experienced forklift truck driver.  You then start preparing the shipping documents and perhaps call in a carrier for the special pickup. 

Great.  You've got everything lined up.  You're a superstar.

You then proceed with entering the shipping details in the Shipping Entry window.  The last thing you need to do is save the shipment and print out the packlist for the shipment. 

Click

Infor ERP Express VISUAL - Shipping Entry

Now I know I'm not alone in this.  There's nothing more aggravating than preparing an order for shipment only to find out that the order is on hold or that the shipments are on hold.

It's certainly not efficient.

Below is an OnLoad macro you can use in the Shipping Entry window.  This macro will execute as soon as you bring up the order.  It does a lookup on the order status as well as status of the customer.  If either one is ON HOLD, the macro will display a popup message informing you of that - and thus allowing you to move on to the next order without missing a step.

Be sure to save this macro as OnLoad so that it fires every time you call up an order.  Don't forget to enter you database connection parameters (where indicated)


'******OnLoad macro in Shipping Entry Module.

'****** Verify if order or customer is ON HOLD
'****** Mind the line wrap!!!

Dim x
Dim strCnn
Dim SQL
Dim rs
 
Dim strMsg
 
Dim strServerName
Dim strDatabase
Dim strUserName
Dim strPassword
 
strServerName="E N T E R    N A M E    O F    D A T A B A S E    S E R V E R"
strDatabase="E N T E R    N A M E    O F    D A T A B A S E"
strUserName="E N T E R    U S E R N A M E"
strPassword="E N T E R    P A S S W O R D"

strcnn="Driver={SQL Server};Server=" & strServerName & ";Database=" & strDatabase & ";Uid=" & strUsername & ";Pwd=" & strPassword & ";"
set rs = CreateObject("ADODB.Recordset")
 
SQL="SELECT STATUS FROM CUSTOMER_ORDER WHERE ID='" & ORDER_ID & "'"
rs.open SQL, strcnn
 
if not rs.eof then
    rs.movefirst
    If rs("STATUS")="H" then
       strMsg=strMsg & "Customer Order is ON HOLD"
    end if
end if
rs.close

SQL="SELECT CREDIT_STATUS FROM CUSTOMER WHERE ID='" & CUSTOMER_ID & "'"
rs.open SQL, strcnn
 
if not rs.eof then
    rs.movefirst
    If rs("CREDIT_STATUS")="H" or rs("CREDIT_STATUS")="S" then

       if len(strMsg & "")>0 then
          strMsg=strMsg & chr(13)
       end if

       strMsg=strMsg & "Customer is ON HOLD - no shipments allowed"

    end if
end if
rs.close
set rs=nothing
 
if strMsg<>"" then
    Msgbox strMsg
end if

'**** End of macro

 

WHO IS VISUALNUGGETS?

Richard Ferlatte
Results Driven VISUAL Specialist

VISUAL WEBINARS - Updated!

Check out our 50+ Training Webinars focusing on the various VISUAL modules and concepts. There's sure to be one to interest you!
24 Manufacturing Webinars
15 Quality Webinars
13 Financial Webinars

VISUAL EXTENSIONS

Extend the functionality of your VISUAL ERP system with these proven and supported VISUAL apps
Go Paperless App
Integration of the Go Paperless App solution and VISUAL results in Paperless ERP that provides a way for VISUAL users to increase efficiencies and thereby, the cash conversion cycle.
Inventory Management Tool
Provides you the ultimate freedom to quickly filter data, drill-down and track inventory/work in process discrepancies, easily reconcile actual inventory/wip to financial records for current or past periods.
eBoard – Financial Dashboard
A powerful Financial Dashboard allowing you to access live Financial data, drill down to supporting details and export the financial statements to Microsoft Excel in one click.
VE Mobile
The premier mobile extension to the VISUAL, adding flexibility and productivity improvements through the introduction of mobile scanning technology to the warehouse.
Logicity™ Automatic Invoicing
Automatically generate e-mailed or printed invoices out of VISUAL
EDI Integrator
A powerful alternative to the VMDI interface for EDI transactions (inbound and outbound).
Web Store Integration
e-Commerce solution integrating the web to your VISUAL system.