Infor Visual Enterprise Macros

Here are some macro information for Visual Enterprise.
Macros are basically vbscript and you can get more help from the Infor website.

Here are three examples that we use all the time.

This one takes the packlist from the shipping window and sends it to Microsoft SQL reporting services to create a report in PDF format.
Filename: VMSHPENT_Print Cert.vms

Dim ie,ss
sub pause(secs)
start = now
secs = cint(secs)
while datediff("s",start,now) < secs: wend
end sub

set ie = CreateObject("InternetExplorer.Application")

ie.Navigate "http://sharepoint/reportserver?%2fVisual+Enterprise+Shop+Reports%2fSkills+Cert+for+Laser+Printers&rs:Command=Render&rs:Format=PDF&PACKLIST=" & PACKLIST_ID

ie.Height = 50
ie.Width = 500
ie.Visible = false
pause 4
Set ss = CreateObject("WScript.Shell")
ss.AppActivate "File Download"
ss.sendkeys "%o"

I'm sorry if this blog chops some of this into more than one line.
Make sure the ie.Navigate is one line and then the next line is ie.Height = 50.

This next one just enters some data into a box for you.
That's right, just one line!
filename: VMMFGWIN_Submitted to Vendor.vms

udfString1 = "Submitted to Vendor"


This one goes into a database and pulls some information about the customer and enters it into a box for you.
filename: VMORDENT_Fill in product code.vms

Dim SQLString
Const DB_CONNECT_STRING = "Provider=SQLOLEDB.1;Data Source=MYSERVER; Initial Catalog=MYDATABASE;User Id=macro;Password=somereadonlypassword"
Set MyDB = CreateObject("ADODB.Connection")
MyDB.Open(DB_CONNECT_STRING)


SQLString = "select USER_9 FROM CUSTOMER WHERE (ID = '" & CUSTOMER_ID & "')"
set rsSystemName = MyDB.Execute(SQLString)
GetPC = rsSystemName.Fields("User_9")
MyDB.Close

Set OrderLns = LINES.Value

For i = 0 to OrderLns.Count -1

Set OrderLn = OrderLns(i)

sOrderLn = OrderLn("LINE_NO")

If OrderLn("PRODUCT_CODE")="" Then

OrderLn("PRODUCT_CODE")= GetPC

End If

Next

Set OrderLn = Nothing

Set OrderLns = Nothing

Comments

Unknown said…
Ed,

can you post a sample SQL.INI for connecting to a SQL Server DB? I'm migrating from SQLBase to SQL Server and I haven't been able to figure out the way to connect.
Ed Hammond said…
[win32client]
clientname=sql-server-name

[winclient]
clientname=sql-server-name

[win32client.dll]
comdll=sqlodb32

[sqlserver]
VisualDBName=sql-server-name,VisualDBName

[odbcrtr]
longbuffer=1048576
odbctrace=off
remotedbname=vq,DSN=vq
Ed Hammond said…
sql-server-name will be the name of your SQL server.

VisualDBName will be the name of your Visual Database.

The last line is for my Visual Quality database that is called vq.
Jasemhi said…
Hi Ed,

Thanks for posting info about Visual. I would like to write a macro to automate order notes with specific user input. For example, the macro would ask the user for the item NAME and release DATE and populate the order notes with something like "Item NAME will be sent on DATE" to make order entry easier. Do you have any ideas on how to learn to do this or where I can get started? I have taken a VBScript class so I understand the basics. Thanks for any help you can give.
Ed Hammond said…
I didn't test this. Just change Item and Date to match what the variable is for those boxes.
-------------------------------

Item = InputBox("Enter Item Name",,)

Date = InputBox("Enter Release Date",,)
Unknown said…
great post, and still relevent a few years later. I have a visual mfg isntallation that I am upgrading to sql 2008, but cannot get the link to Visual Quality to work now on my test server. Error I get is "cannot activate application VQ"

Please advise any tricks to help troubleshoot this issue.
thanks!
Ed Hammond said…
Is this a new server? Did you have to recreate all the users? We are working on an upgrade too. I'm moving to a new server with SQL 2008 R2 and Visual 7 and I had to recreate SYSADM in the SQL Server and then re-add all the users from Visual. Do you think you might need to re-create the IQS user in SQL Server? We dropped VQ, so I'm not testing my upgrade with VQ.
Hypernova said…
I've got a question for you, if you still check your page:

We use an OnSave macro to make our packlist IDs the same as the order IDs to find orders easier (as well as BOLs, but the packlist is the issue here).

With delivery schedules, backorders can't be created onto a new order, so when you try to save onto a shipment that's already used the initial packlist number, you get an error.

I'd love to have it check and just add a -1, -2, etc to the packlist number (in a loop until it succeeds). Any idea how to check against already taken packlist IDs?
Ed Hammond said…
You can do a SQL query for that number. Write the query so that it returns the number of rows found. If it finds 1 row, then tell it to use the -1, if it returns 2 rows then tell it to use -2.
You would need to use some vb "if" statements for that.
I'm too busy this month to write any examples here...
Ed Hammond said…
I think your Select statement should be:
SELECT PACKLIST_ID FROM SHIPPER WHERE PACKLIST_ID='" & ORDER_ID & "'"

SHIPPER is the table where the PACKLIST_ID is found.
Hypernova said…
You have been a great help! I've got everything working except one final minor detail. I'm trying to get it to save the packlist ID that I want to use in a variable upon order load. I then want it to retrieve that ID from that variable when I use the OnSave macro, however I can't seem to get the variable to be truly global across macros. I've declared it Public instead of Dim in both the OnLoad and OnSave macros, but it isn't doing it OnSave.

Seems like I'm wasting your blog space with this question, but I can't find anything out there other than the Public declarations, which don't seem to be working.

Let me know either way, and I'll send you the finished macro (after ample testing, of course! Tested about a dozen times so far in a sandbox database.)

Thank you for all you've done,
Jeff
Hypernova said…
Funny thing happened yesterday. When I announced that I had closed in on a working macro, all of a sudden the higher-ups got a little scared that I would screw something up. They immediately had the company they asked earlier to write it up. It works well. Mine had a problem anyway. It got stuck on the -1. Wouldn't go past it regardless of the number of shipments with that number. I noticed they didn't require the use of a Database selection, so feel free to add that in yourself for anyone that wants it.

Dim Server, Username, Password
Dim rst1, strCnn1, sSQL1
Dim OrderNum, SuffixStr, SuffixNum, Cleared
Dim AttemptStr
OrderNum = ORDER_ID

Server = "E N T E R S E R V E R N A M E H E R E"
Username = "E N T E R U S E R N A M E H E R E"
Password = "E N T E R P A S S W O R D H E R E"

Set rst1 = CreateObject("ADODB.Recordset")
strCnn1 = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=" & User & ";Pwd=" & Password & ";Initial Catalog=" & DATABASE & ";Data Source=" & Server

Cleared = false
SuffixStr = ""
SuffixNum = 0
AttemptStr = "" & orderNum
While Cleared = false
sSQL1 = "SELECT PACKLIST_ID FROM SHIPPER WHERE PACKLIST_ID = '" & AttemptStr & "'"
rst1.Open sSQL1, strCnn1
If rst1.EOF then
Cleared = true
End if
rst1.Close
if Cleared = false then
suffixNum = suffixNum + 1
AttemptStr = "" & orderNum & "-" & suffixNum
End If
Wend

PACKLIST_ID = AttemptStr

So the moral of this story is threaten to do something yourself, and if they're scared enough, they'll get their butts moving.

Thanks again for your help!

Jeff
Unknown said…
Ed,

Have you ever created a link between Visual and some other application to print a label upon completion of a labor transaction? We’re looking at simplifying our Work Order Travel with the bare minimal information of the op and once the employee has ending the labor transaction through Barcode Labor, a label be automatically generated that can be affixed to the WO Traveler and the employee would then initial the transaction and move it onto the next op. Could you please let me know if you have any experience with this? Thank you. Mary
Unknown said…
Ed,

Have you ever created a link between Visual and some other application to print a label upon completion of a labor transaction? We’re looking at simplifying our Work Order Travel with the bare minimal information of the op and once the employee has ending the labor transaction through Barcode Labor, a label be automatically generated that can be affixed to the WO Traveler and the employee would then initial the transaction and move it onto the next op. Could you please let me know if you have any experience with this? Thank you. Mary

Popular Posts