LSO & Jscript

Scott has started posting about his development experiences with Lawson Smart Office and Jscript at http://potatoit.wordpress.com.  Worth a look.

Interfacing Excel with M3 via APIs

Automating many functions in M3 can be achieved through the use of APIs. APIs are defined in MRS001 and should be tested thoroughly using Lawson's MITest tool before you attempt to use them via Excel.

Note that my documentation below is based on Excel 2000, later versions can have slightly different menu names etc, but the functionality is the same.

Add the API dll to Excel
In Excel open the Visual Basic editor and choose Tools, References.  Add a reference to the M3 API dll

Open a connection to M3

In your VB code add the following:

'Init M3 API variables

Dim Sock As New MvxSockX
Dim transStr As String
Dim Result As String
Dim rc As Long
       
'Connect to M3.  Note that servername, port, username, password
'and apiprogram are the same values you would have used in MITest

rc = Sock.MvxSockConnect(servername, port, username, password, apiprogram, "")

'Check for connection error

If rc <> 0 Then Sock.MvxSockShowLastError ("Error while Initializing")

Build the API command string

When you used MITest to test the API command you wanted to use you will have noted a fixed-with string was sent to M3.  Here we will build this string.

'Obtain the lines from a PDS090 Matrix

transStr = Space(500)
Mid(transStr, 1) = "ListLine"
Mid(transStr, 16) = "100"              'Company
Mid(transStr, 19) = "TEST"             'Matrix

Send the command to M3 and process the result

Here we send the command we built above to M3 and look at the result

'Send API command to M3

rc = Sock.MvxSockTrans(transStr, Result)
       
'If the command resulted in an error, exist showing that error message

If rc <> 0 Then
  Sock.MvxSockShowLastError ("")
  Exit Sub
End If

Close the connection to M3 once you are done

Note that you should open a single connection per API and use this as many times in your program before you close it.  

'Close connection to M3

Sock.MvxSockClose

Complete examples

I tend to have a Parameters tab where the username, password etc are entered.

The standard layout of our mass-update tools looks like this

The Get Data button links to the GetClothMatrixData_Click script below, the Update Data button links to the UpdateClothMatrixData_Click script below.  The Add Data button links to a script similar to the Update Data script.  Finally the Clear Spreadsheet script simply removes the data in the spreadsheet.

This example opens a Matrix in PDS090 then gets all the information from that Matrix and returns it to Excel.

Private Sub GetClothMatrixData_Click()

    Application.Goto ("ClothMatrixDataStart")
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Value = "" Then
   
        Dim Sock As New MvxSockX
        Dim transStr As String
        Dim Result As String
        Dim rc As Long
       
        rc = Sock.MvxSockConnect(Sheets("Parameters").Range("Computer").Value, Sheets("Parameters").Range("Port").Value, Sheets("Parameters").Range("Username").Value, Sheets("Parameters").Range("Password").Value, "PDS090MI", "")
        If rc <> 0 Then Sock.MvxSockShowLastError ("Error while Initializing")
   
        ' Set max number of items returned. Default is 100
        rc = Sock.MvxSockTrans("SetLstMaxRec   49999", Result)
       
        Application.Goto ("ClothMatrixDataStart")
        ActiveCell.Offset(1, 0).Select
       
        transStr = Space(500)
       
        'List the items in the location
        Mid(transStr, 1) = "ListLine"
        Mid(transStr, 16) = Sheets("Cloth Matrix").Range("ClothMatrixCompany").Value        'Company
        Mid(transStr, 19) = Sheets("Cloth Matrix").Range("ClothMatrix").Value              'Matrix
        rc = Sock.MvxSockTrans(transStr, Result)
       
        If rc <> 0 Then
            Sock.MvxSockShowLastError ("")
            Exit Sub
        End If
       
        'If we have a response then enter the loop
        If Trim(Mid(Result, 1, 3)) = "REP" Then
            Do
                ActiveCell.Value = Trim(Mid(Result, 34, 15)) 'Cloth
                ActiveCell.Offset(0, 1).Value = Trim(Mid(Result, 24, 10)) 'Valid Date
                ActiveCell.Offset(0, 2).Value = Trim(Mid(Result, 214, 15)) 'Result
                ActiveCell.Offset(1, 0).Select
           
                'Get the next line of the result
                Result = Space(500)
                rc = Sock.MvxSockReceive(Result)
                If rc <> 0 Then
                    Sock.MvxSockShowLastError ("")
                    Exit Sub
                End If
           
            Loop Until Trim(Mid(Result, 1, 3)) <> "REP"
        End If
       
        Application.Goto ("ClothMatrixDataStart")
        ActiveCell.Offset(1, 0).Select
        Sock.MvxSockClose

    End If

End Sub
 

This example parses through a list in Excel then updates the associated records in a M3 PDS090 Matrix

Private Sub UpdateClothMatrixData_Click()

    Application.Goto ("ClothMatrixDataStart")
    ActiveCell.Offset(1, 0).Select
    If ActiveCell.Value <> "" Then
   
        Dim Sock As New MvxSockX
        Dim transStr As String
        Dim Result As String
        Dim rc As Long
       
        rc = Sock.MvxSockConnect(Sheets("Parameters").Range("Computer").Value, Sheets("Parameters").Range("Port").Value, Sheets("Parameters").Range("Username").Value, Sheets("Parameters").Range("Password").Value, "PDS090MI", "")
        If rc <> 0 Then Sock.MvxSockShowLastError ("Error while Initializing")
   
        Do
       
            transStr = Space(500)
       
            Mid(transStr, 1) = "UpdateLine"
            Mid(transStr, 16) = Sheets("Cloth Matrix").Range("ClothMatrixCompany").Value 'Company
            Mid(transStr, 19) = Sheets("Cloth Matrix").Range("ClothMatrix").Value 'Matrix
            Mid(transStr, 24) = ActiveCell.Value 'Cloth
            Mid(transStr, 114) = ActiveCell.Offset(0, 1).Value 'Valid from
            Mid(transStr, 124) = ActiveCell.Offset(0, 2).Value 'Result
           
            rc = Sock.MvxSockTrans(transStr, Result)
           
            If rc <> 0 Then
                Sock.MvxSockShowLastError ("")
                Exit Sub
            End If
           
            ActiveCell.Offset(0, 4).Value = Result
            ActiveCell.Offset(1, 0).Select
       
        Loop Until ActiveCell.Value = ""
       
       
        Application.Goto ("ClothMatrixDataStart")
        ActiveCell.Offset(1, 0).Select
        Sock.MvxSockClose


    End If

End Sub

Using Excel and APIs we are able to manipulate tens of thousands of records quickly and easily while still using the M3 business engine.  When APIs don't offer the functionality required you can also make use of Web Services from Excel which I will discuss in my next post.

Creating a M3 Web Service

I recently needed to use Lawson Web Services (LWS) for M3 to address some automation issues that APIs did not exist for.  LWS allows Web Services to programmatically access most M3 programs and function.

In this post I will show how to create a Web Service.  In a subsequent post I'll show how to consume this Web Service from Microsoft Excel.

Creating a Web Service to calculate the Cost of a variant (PCS230)


In this example we will start by loading PDS001, choose Related Options, Product Costing to start PCS230 and then run Costing for type 3 (standard):


Step 1 - Set programs to use A panel if possible
LWS works best with programs that can use the A panel.  We'll set PDS001 to use this:

Step 2 - Document the field names to enter data in programs/panels
When creating the Web Service we will need to specify the field names in panels we want to change

Step 3 - Document the key presses required
The Web Service will follow almost exactly the same key presses as is used in Workplace/Smart Client/Smart Office, so document the panel sequence, fields and keys pressed.

Our process is as follows

• PDS001/A - Set WWFACI (Facility), W1PRNO (Product Number)
• PDS001/A - Choose function 25 (Calculate Cost)
• PCS230/E - Set WWPCTP (Costing Type), WWPCDT (Costing Date), WWCOUP (Calc purch/dist), WWPPRD (Purch price dt), WWSTRD (Structure Date), WWALVL (All levels)
• PCS230/E - Press ENTER, then ENTER again (to accept the warning that the standard cost will be updated)
• PCS230/F - Press ENTER
• PDS001/A - Press F3 to exit the program

Step 4 - Build the Web Service in LWS
Note that you need to have LWS up and running.  Follow Lawson's instructions in the LWS Instllation Guide & User guide for this.  Check diagnostics in LWS to confirm that your setup is correct.  It should look something like this:

4.1 Create a new Web Service


4.2 Then define the parameters for the program to be called and test. This video is too large to embed here so click to see Web Service Creation Walkthrough

A few final notes about creation of Web Services:

• When creating Web Services you can turn on the Interactive option for particular program/panels.  This is very useful to diagnose problems.
• Web Services do not allow for branching logic within the Web Service. If you need to perform different actions based on the data, you will need to create a different Web Service for each action.
• Not all programs allow for Web Services.  There appears to be a way to turn the warning off within LWS for this, however I have not yet tested this.

Extending the configurable BOM

In my last post I showed how to create a configurable BOM to allow the selection of different embroidery options during order entry.  The structure I ended up with was:

This worked well for the scenario where there would only ever be two embroidery options offered against this product.  However if the embroidery options would change over time this structure is not ideal as it would require the addition of a material line in the BOM for each additional embroidery option.

In this post I show two alternative approaches to addressing the maintainability issue: using PDS090 matricies or using the item group in MMS001.

Selecting an embroidery option from a Matrix
Matricies allow a list to be created which links data together.  In this case we will have a list of options and the associated raw material for embroidery.

The matrix is created in PDS090.  We'll create a matrix type 3 to return an Item code from MMS001.

The feature EMBR was setup in my previous post.  This has the options LOGO1 and LOGO2 specified against it.

We can then specify the item codes we want to be returned when the option is specified in PDS091

Back in PDS002 we can now specify a material line that will consume this Matrix.

Note that the Select tp comp value has been set to Matrix and the Select ID comp has been set to MEMBR.  This tells the BOM line to look at the Matrix MEMBR to obtain the Item value when this BOM is indented.

We can see this by Indenting the BOM from PDS001

Note that line 5003 is being called and the two options we had set up against the feature specified in the Matrix are shown.

The value YB1362-89 is returned as this was associated with option LOGO2 in the matrix MEMBR.

Ongoing maintenance of this product (adding additional embroidery options) now is performed by adding the option to the feature EMBR in PDS055 and associating an Item code with the option in PDS090.

Selecting an embroidery option based on Item Group
Another approach to providing a list of options for embroidery is to set these up as items in MMS001.

We can then specify a feature that consumes this item group to provide a list of options

Note that the Feature type is set to 2-Item Number and the Item group is set to TRI14 (our embroidery Items).

Back in PDS002 we set up a new material line which consumes this feature

Note that the Select tp comp value has been set to 3-Feature and the Select ID comp has been set to the feature defined above IGEMB.

If we now indent the BOM from PDS001 we see that all the items with an Item Group of TRI14 appear as options in our list for embroidery.

This approach significantly reduces the maintenance overhead for adding new embroidery options.  As soon as the embroidery item is added to MMS001 with an item group of TRI14 it will appear as a configuration option.


A key benefit of both approaches shown above is that the maintenance of embroidery options is separated from the design of the product structure, allowing delegation of different functions to different team members.  The Matrix approach is best used when only a subset of the potential options are to be offered.  The Item Group approach is best used when all potential options are to be offered.