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.

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

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.

Customer Order Entruy Create New Job

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.VALUEFor 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

Copyright 2019 visualnuggets © All Rights Reserved