How to easily update linked workorder want dates

I often get asked…

Why Use Macros? What are they good for?

A Macro is one of the many components (features) in VISUAL ERP that you can use to drive productivity and efficiency within your organization.  When carefully designed and deployed, macros can have a tremendous impact on your operations – with immediate benefits.

Why?  Well they can facilitate in areas where you may want to:

  • Automate tasks
  • Follow (enforce) business rules
  • Reduce (or eliminate) data entry errors
  • process things faster – time savings opportunities
  • Integrate VISUAL with other business applications

And this is just to name a few.

I would say that with any continuous improvement initiates that involves using the VISUAL user interface – a macro element should be involved.

just sayin…

Here’s a snippet of a macro that will update the want date of linked work orders to equal the desired ship date of the customer order.  So if you have customer orders that are constantly changing desired ship dates AND the have linked work orders attached – this macro makes it easy to keep dates aligned (if needed).  You certainly don’t what to work on work orders early if the CO has been pushed out.

This can be used as an “On Demand” macro or be integrated into a “OnAfterSave” macro.  You can take this further by prompting the VISUAL on whether they want to update the linked work orders with a YES OR NO – whatever makes sense for your business

If you like this snippet – and would like access to more and other nuggets, please be sure to subscribe below.  

 

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”

Const adParamInput = &H0001
Const adVarChar = 200

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

if TRIM(ORDER_ID & “”)<>”” and Lines.Value.Count>0 then

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

     Set cmd = CreateObject(“ADODB.Command”)
Cmd.ActiveConnection = conn

     SQL=”UPDATE WORK_ORDER SET DESIRED_WANT_DATE = ISNULL(COL.DESIRED_SHIP_DATE,CO.DESIRED_SHIP_DATE) ” & _
“FROM DEMAND_SUPPLY_LINK AS DSL ” & _
“INNER JOIN CUST_ORDER_LINE COL ON DSL.DEMAND_BASE_ID = COL.CUST_ORDER_ID AND DSL.DEMAND_SEQ_NO = COL.LINE_NO ” & _
“INNER JOIN WORK_ORDER WO ON DSL.SUPPLY_BASE_ID = WO.BASE_ID AND DSL.SUPPLY_LOT_ID = WO.LOT_ID ” & _
“AND DSL.SUPPLY_SPLIT_ID = WO.SPLIT_ID AND DSL.SUPPLY_SUB_ID = WO.SUB_ID ” & _
“INNER JOIN CUSTOMER_ORDER CO ON COL.CUST_ORDER_ID = CO.ID ” & _
“WHERE (DSL.DEMAND_BASE_ID = ?) AND (DSL.DEMAND_SEQ_NO = ?) and WO.STATUS NOT IN (‘X’,’C’) “

     Cmd.Parameters.Append Cmd.CreateParameter(“@OrderID”,adVarChar,AdParamInput,15)
Cmd.Parameters.Append Cmd.CreateParameter(“@LineNo”,adVarChar,AdParamInput,15)

    cmd.CommandText=SQL

     For i = 0 To (Lines.Value.Count – 1)

          cmd(“@OrderID”)=ORDER_ID
cmd(“@LineNo”)=LINES(i)(“LINE_NO”)
cmd.Execute

     Next

     conn.close

end if

 

Copyright 2019 visualnuggets © All Rights Reserved