Goal
You have created a Dynamics SL 2011 Custom Screen using the Tools for Visual Basic. Your Screen processes information using the Status Function from the SWIMAPI.dll. The Status Function uses the process screen for SL and allows you to log messages in a log file that is placed in the eventlog folder of SL. At the end of processing, if there were errors, it will say process is complete and the log file is in that eventlog directory. The filename is generated by the process.
This is all fine but some users do not go surfing for files. A better solution may be to open the log file in notepad automatically instead of making the user find it and open it.
The goal here is to open the log file created after a dynamics SL process in notepad.
Environment
SQL Server 2008 or 2012 and Dynamics SL 2011.
We are developing in Visual Studio 2010. Please see my other post on how to get around the 64 bit issue.
Issue
If you are like me, you are very familiar with the Launch function in vba but how to open notepad in vb.net is not something I do all the time. Also knowing the directory and name of the log file, how to get that from SL? This is not difficult just requires some logistics to do.
Resolution
In order to accomplish all of this:
1) I used the pstatus table. This is in all application databases and everytime you log something to a log file in sql, the new file name is saved there in the zfilename field, the screen, user, date and time is stored too
2) I identify the record by taking the records in pstatus with user = current user (bpes.userid) and processdate = today and screen = current screen (bpes.ScrnNbr) and process time is the latest in the table for all the fields just listed.
3) Finally I use the System.Diagnostics.Process.Start() to launch NotePad.exe.
The Sub used to open the file looks like below:
Private Sub LaunchError()
Dim d As DateTime = Now
Dim sFile As String = ""
Call SqlCursor(csr_PStatus, NOLEVEL + SqlFastReadOnly)
serr9 = SqlFetch1(csr_PStatus, "Select * from PStatus where userid = "& SParm(bpes.UserId) & " and PID = " &
SParm(Trim(bpes.ScrnNbr) & "00") & " and ExecDate = '" &d.ToString("MM/dd/yyyy") & "' and ExecTime in (Select Max(ExecTime) from PStatus where userid = " & SParm(bpes.UserId) & " and PID = " &SParm(Trim(bpes.ScrnNbr) & "00")& " and ExecDate = '" & d.ToString("MM/dd/yyyy")& "')", bPStatus)
If serr9 <> NOTFOUND Then
sFile = Trim(bPStatus.zfilename)
End If
Call SqlFree(csr_PStatus)
If Trim(sFile) <> "" Then
System.Diagnostics.Process.Start("Notepad.Exe", sFile)
End If
End Sub
The code I use to launch this sub is:
' call final screen (process complete with log file at following location)
Call MessBox(sStuff, MB_OK, bpes.ScrnNbr)
' After user closes the above screen (dialog screen) check for any errors in ‘ ‘process and if there are then call the laucherror
If bErrors Then
LaunchError()
End If
Conclusion
It is standard procedure to use the process screen when processing data in Dynamics SL. You use the status call to use it and it helps you log data to a log file in the eventlog directory of SL. The problem is that most users are not savy enough to surf folders for a log file and figure what is wrong. As step in the right direction is to open the file in notepad so the user can easily read it. That is what we do here.
