FactoryTalk SE Datalogging and filtering for Display. VBA/SQL perhaps?

Join Date
Jul 2007
Location
Kiruna
Posts
600
Hi there,

I have an appplication where I was required to take a snapshot of a set of values (approx 50) and display on SCADA. When the next snap shot is taken the old set of values is moved to another set of registers up to 5 .

There was only a requirement for 5 sets of snapshots and this this done across 13 PLCs from an SE SCADA server.

Now the requirement has increased to 20. Upon a button click now I would like to store this in database and then call/filter this data upon request.

I dont have more money in budget for historian (prob overkill anyway) and my VBA is not exactly brilliant.

Has anyone got any suggestions on the bext way to approach this in SE
 
Hooookkkaaayyyyy.....
Take a DEEP breath Morphius.. Take another... Good, lets dive in.

Mind you, this is the easiest way I've found.

Step 1 - (Duh) Create the database and tables you will use, I also like to use database stored procedures for all interaction with FTView VBA. It really makes things easier in the long run.

Step 2 - Give the account that FTView runs under permissions to read/write/execute/bla bla on the new database.

Step 3 - In Windows on the FTView machine that you WANT to write data to the database from, go to administrative tools, ODBC, and create an ODBC topic to hook up with.
Note, that in FTView VBA, you can hard-code the DSN Connection string, with specific user and password if you want, but I'm being simple here.

Step 4 - In FTView Create a window, leave it blank for now. Open up VBA on that window. START with declaring OPTION EXPLICIT for VBA at the top somewhere. Also, for the VBA setup, go to Tools, References, and check off:
--Visual Basic for Applications
--RSView Display Client Object Model
--OLE Automation
--Microsoft ActiveX Data Objects 2.0 Library
as a very minimum.

Step 5 - (Urgent, if you have multiple clients for the HMI server) In the VBA General area type in:
Private Declare Function GetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long 



Define a bunch of globals... Soft of like this:

'*******************************************************************
' Global Things that require setting:
'*******************************************************************
Public Line_Num As Integer ' Globally set to the actual LINE number
Dim DBSource As String ' Set to the actual ODBC Data Source name.
Public IOTopic As String ' Set to the OPC IO Topic for direct reads
Public ReportFile As String ' Set to the full file path to the report.




'*******************************************************************
' Global flags to control actual logging and reporting:
'*******************************************************************
Public BypassReportFlag As Boolean
Public EnableReportAndLogging As Boolean

'*******************************************************************
' Global objects to handle IO Tag communications:
'*******************************************************************

Dim HMITag As Tag
Dim HMITagGlobal As Tag
Dim HMIGroupGlobal As TagGroup
Dim SampleGroup As TagGroup
Public Tag_str As String
Public ErrorTags As StringList
Public Pending As Variant
Public Results As Boolean
Public TagsInError As StringList

'*******************************************************************
' Global objects for Database Interactions:
'*******************************************************************
Public DBState As Long
Public DBConnSt As Long
Public DB_Curr_Index As Long
Public Last_Rec_Written As Long

Dim oConn As ADODB.Connection
Dim oCmdAddSample As ADODB.Command

'*******************************************************************
' Global Vars for general status returns
'*******************************************************************
Public RetVal As Integer



--- Continued ---
 
Step 5 - The VBA Code, Continued.

Create a handler for the "Display_Load" event, and do all initialization. I'm showing mine, with diagnostics and such included...

'*******************************************************************
' Sub: Display_Load()
' Called when display is first loaded, initializes overall
' VBA Code, and sets up all tag groups and database commands.
'*******************************************************************
Private Sub Display_Load()
On Error GoTo ErrHandler

'--------------------------------------------------------------
' Globals which MUST be initialized:
'
Line_Num = 12345
DBSource = "MyODBC"
IOTopic = "MyIoTopic"


LogDiagnosticsMessage "*****INITIALIZING LOGGING SUBSYSTEM
LogDiagnosticsMessage "***** Logging SubSystem Version 7.3d"

If ComputerName() = "LoggingPC" Then
EnableReportAndLogging = True
LogDiagnosticsMessage "My Host Name is: " + ComputerName() + " And Logging is True"
Else: EnableReportAndLogging = False
LogDiagnosticsMessage "My Host Name is: " + ComputerName() + " And Logging is False"
End If



If EnableReportAndLogging = True Then
'----------------------------------------------------------
' Add all Tag's to monitor to the appropriate IO tag group:
'
Add_SampleGroup


'----------------------------------------------------------
' Open the database, and build up the commands to handle
' the interaction to the SQL Server stored procedures:
'
Open_Database


Add_Sample_Cmd ' Add Stored Procedure call for new Indexed Sample Command

End If

Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Display_Load() ", ftDiagSeverityInfo 'Log error to the activity log

End Sub



Here is the function to get the computer name:

'*******************************************************************
' Function: ComputerName()
' Returns the network name of this computer as a string.
'*******************************************************************
Public Function ComputerName() As String
Dim sBuffer As String
Dim CName As String
Dim UpperCase As String

Dim lAns As Long

sBuffer = Space$(255)
lAns = GetComputerName(sBuffer, 255)
If lAns <> 0 Then
'read from beginning of string to null-terminator
CName = Left$(sBuffer, InStr(sBuffer, Chr(0)) - 1)
UpperCase = UCase(CName)
ComputerName = UpperCase
Else
Err.Raise Err.LastDllError, , _
"A system call returned an error code of " _
& Err.LastDllError
End If

End Function



Notice that I'm calling other functions to build up the tag groups, here there is only one, but you can have many. Let's build up the "SampleGroup". Here, we create a group of tags in the VBA app, that can interact with the PLC. This sample is based on the Control/Compact Logix platform, has 4 data tags, and the trigger tag (which isn't actually needed).

'*******************************************************************
' Sub: Add_CVSampGroup()
' Called when display is first loaded, to build up the global group
' of run sample Tracking Tags.
'*******************************************************************

Public Sub Add_CVSampGroup()

On Error GoTo ErrHandler

If EnableReportAndLogging = False Then
Exit Sub
End If

txt_DBStatus.Caption = "Adding Sample Group"

If SampleGroup Is Nothing Then
Set SampleGroup = Application.CreateTagGroup(Me.AreaName, 500)
If Err.Number Then
LogDiagnosticsMessage "Unable to create SampleGroup", ftDiagSeverityError
Exit Sub
End If

'-----------------------------------------------------------------------
' For all groups, the topic is built up of the global IOTopic value.
' This is to make it easy to update this VBA code for different
' machines and equipment. The actual tags still need to be hard-coded
'
SampleGroup.Add "{[" & IOTopic & "]CMD_Sample}"
SampleGroup.Add "{[" & IOTopic & "]DataTag1}"
SampleGroup.Add "{[" & IOTopic & "]DataTag2}"
SampleGroup.Add "{[" & IOTopic & "]DataTag3}"
SampleGroup.Add "{[" & IOTopic & "]DataTag4}"

SampleGroup.Active = True
End If
Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Add_SampleGroup() ", ftDiagSeverityInfo

End Sub





Okay, so we've added the group of tags we want to log, now we should open the database and perform some actions. Again, this is a SIMPLE example. Here, we are just going to open a database connection, and leave it open forever. In practice, it is usually wiser to only open the connection when needed, use it, and close it again. Here is the database connection code:


'*******************************************************************
' Sub: Open_Database()
' Called when display is first loaded, and opens the global connection
' to the SQL Database
' This database reference should be set up as a SYSTEM DSN using
' 'Data Sources' under 'Administrative tools' in the Windows
' control panel. Authentication should be 'Windows Authentication'
' which requires that the users also be given rights to access
' the database, or all logging calls will fail.
'*******************************************************************
Private Sub Open_Database()

On Error GoTo ErrHandler
Dim DSNName As String

DSNName = "DSN=" & DBSource

txt_DBStatus.Caption = "Opening Database"

If oConn Is Nothing Then
Set oConn = New ADODB.Connection
End If

oConn.CursorLocation = adUseClient
oConn.Mode = adModeReadWrite

oConn.Open DSNName

DBState = oConn.State
txt_DBState.Caption = DBState

LogDiagnosticsMessage "Database " & DBSource & " Has Been Opened"

Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Open_Database() ", ftDiagSeverityInfo

End Sub



Here is the code to attach a stored procedure for adding a sample (based on our sample groups) to the database connection above. Note that this sub here assumes that you have a stored procedure on the database that takes 4 values, inserts them into some table, and returns the row-index of the inserted row:

'*******************************************************************
' Add_Sample_Cmd
' Called when display is first loaded, and creates the interface
' to the database stored procedure to add Reel Report Records, then
' attaches it to the global database connection.
'*******************************************************************

Public Sub Add_Sample_Cmd()

Dim pPar As ADODB.Parameter

If pPar Is Nothing Then
Set pPar = New ADODB.Parameter
End If

On Error GoTo ErrHandler

If EnableReportAndLogging = False Then
Exit Sub
End If

If oCmdAddSample Is Nothing Then
Set oCmdAddSample = New ADODB.Command
End If

oCmdAddSample.CommandText = "sp_Add_Sample"
oCmdAddSample.CommandType = adCmdStoredProc
oCmdAddSample.Name = "Add_Sample"

Set pPar = oCmdAddSample.CreateParameter("RV", adInteger, adParamReturnValue)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag1", adInteger, adParamInput)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag2", adVarChar, adParamInput, 20)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag3", adVarChar, adParamInput, 20)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("DBDataTag4", adVarChar, adParamInput, 20)
oCmdAddSample.Parameters.Append pPar

Set pPar = oCmdAddSample.CreateParameter("Identity", adVarChar, adParamOutput, 20)
oCmdAddSample.Parameters.Append pPar

Set oCmdAddSample.ActiveConnection = oConn

Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Add_Sample_Cmd() ", ftDiagSeverityInfo


End Sub



Well, that takes care of the basic preparation. Next post, we'll get into actually triggering writes to the database based on PLC events.

--- Continued once more ---
 
Last edited:
Wow!!!!!!!!!!!

Thanks a million. I'll get stuck into this on Monday when i get back from site and let you know when I've got this far.
 
Step 5 - VBA Code continued. Finally, the meat.
Remember the display that this code is attached to? Go back there in FTView Studio for a moment. Looks pretty empty. We should change that. For the heck of it, put just a text label up there, type in "Sample Write Trigger", just so we know. Under that, put a numeric display. For the tag in the numeric display, make it the PLC tag "CMD_Sample". Oh, create the PLC tag as a BOOL if you haven't yet.

Now, right click on the new numeric display, and pick "Property Panel". There, go to "ExposeToVBA" and set it to "VBA Control". While still in the properties panel, the top line? Name? Give it a real name, like "nd_SampleDisplay". Go back to the numeric display, right click it again, and select "VBA Code". That should take you to a blank handler for the "On_Change" event. That means, every time that value changes, this will be called.

'#############################################################################################
' Event Handler Code - This code responds to changes of the numeric displays on the base
' window, and will control actual data processing
'#############################################################################################

'*******************************************************************
' Sub: nd_SampleDisplay_Change()
' Called when the numeric display linked to the "CMD_Sample"
' bit changes in the PLC. Initiates writing a database record of
' the current run sample.
'*******************************************************************
Private Sub nd_SampleDisplay_Change()

On Error GoTo ErrHandler

If EnableReportAndLogging = False Then
Exit Sub
End If

RetVal = 0

If nd_SampleDisplay.Value = 1 Then

Add_Sample 'Call the procedure to add a sample (see below)

'Reset the command flag to write the values.
Tag_str = "{[" & IOTopic & "]CMD_Sample}"
Set HMITag = SampleGroup.Item(Tag_str)
HMITag.PendingWriteValue = 0
Results = SampleGroup.WritePendingValues(TagsInError)

End If

Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: nd_SampleDisplay_Change() ", ftDiagSeverityInfo

End Sub





Here is the sub to actually post the data to the database...


'*******************************************************************
' Add_Sample
' Called when required to add a Sample to the Sample Table.
'*******************************************************************
Public Sub Add_Indexed_Sample()

Dim m_RR_Idx As Long
Dim m_Data1 As String
Dim m_Data2_Dia As String
Dim m_Data3 As String
Dim m_Data4 As String
Dim RowIndex As Long

On Error GoTo ErrHandler

If EnableReportAndLogging = False Then
Exit Sub
End If

RetVal = 0

txt_DBStatus.Caption = "Sample - Refresh Values"

SampleGroup.RefreshFromSource ErrorTags

txt_DBStatus.Caption = "Sample - Retrieving Values"

m_RR_Idx = CStr(DB_Curr_Index)

'----------------------------------------------------------------------
' NOTE: It is important to format numeric tags appropriately in order to
' prevent ODBC/SQL from choking on them. Things that should be
' integers should have no trailing decimals. Things that should or
' could be floating point should NOT be represented in scientific
' notation, and should be limited to an appropriate number of
' decimal places. The "Format()" function handles this.

Set HMITag = SampleGroup.Item("{[" & IOTopic & "]DataTag1}")
m_Data1 = Format(HMITag.Value, "#######0")

Set HMITag = SampleGroup.Item("{[" & IOTopic & "]DataTag2}")
m_Data2 = Format(HMITag.Value, "#######0.#####")

Set HMITag = SampleGroup.Item("{[" & IOTopic & "]DataTag3}")
m_Data3 = Format(HMITag.Value, "#######0.#####")

Set HMITag = SampleGroup.Item("{[" & IOTopic & "]DataTag4}")
m_Data4 = Format(HMITag.Value, "#######0.#####")



txt_DBStatus.Caption = "Sample = Calling Update"

oConn.Add_Indexed_Sample m_Data1, m_Data2, m_Data3, m_Data4

RowIndex = oCmdAddSample.Parameters("Identity")
RetVal = oCmdAddSample.Parameters("RV")

txt_DBStatus.Visible = True
If RetVal = 0 Then
txt_DBStatus.Caption = "Sample Write Failure"
Else
txt_DBStatus.Caption = "Sample Written"
LogDiagnosticsMessage "Sample Added"
Last_RR_Written = RowIndex
End If


Exit Sub
ErrHandler:
LogDiagnosticsMessage Err.Number & " " & Err.Description & " Sub: Add_Sample() ", ftDiagSeverityInfo

End Sub




There! That was easy! Now, to make it work...

Step 6 - In FTView Studio, Set this windows display properties to "Allow Multiple Running Copies - NO, Cache After Displaying - YES, Always Updating - YES". Next, either create a macro for your home windows, or add an open command for this new display to it, with the "/B /ZA" switches:

Display MyLoggingWindow /B /ZA

That will keep it in the background, always running and updating.

Well, there it is in a nutshell. Does that help?
 
Wow!!!!!!!!!!!

Thanks a million. I'll get stuck into this on Monday when i get back from site and let you know when I've got this far.

No problem, well, some problem, trying to simplify it down from some of my production code. I use the same logging routines on multiple lines, that handle all kinds of things and generate a report at every counter reset.

I've posted bits and pieces before, but in searching the board to find them, I came up with too many posts, so decided to stuff em all together in something coherent. I'm usually poking around here if you need any other help or more detailed descriptions of things.
 
logging data in ftv se

hi, If any of you have sample code in vba to log the [FONT=&quot]total consumption of [/FONT] data[FONT=&quot] in one screen monthly + weekly + daily report. please send me a mail on this id: [email protected][/FONT]
 
Okay... To answer at the last 3 posts by one-hit wonders a year apart...
You see the code above. Use it, modify it, or don't. PM'ing me about it won't get you anything else with no effort expended on your end.

If you want a complete, turnkey solution, I need to be paid.
 
Since this thread is still linked often, and I cannot edit the earlier posts (too much time gone by), I want to add a modification here.

Way back when, I was using the computer name to determine if it was the server or not. While that works, it is cumbersome, and impractical, especially for code re-use, or when moving the application to another PC (Hard drive crash, technology update, whatever), especially when the "Idiot Technology" department will not allow a computer name to be reused.

Because of the above, I have switched to a different method of determining what computer should be doing the logging:

Code:
Private Sub Display_Load() On Error GoTo ErrHandler
[SIZE=4][COLOR=Blue]
Dim fs As Object[/COLOR][/SIZE]

 '--------------------------------------------------------------    
 ' Globals which MUST be initialized:    
 '    
 Line_Num = 12345    
 DBSource = "MyODBC"    
 IOTopic = "MyIoTopic"              
 LogDiagnosticsMessage "*****INITIALIZING LOGGING SUBSYSTEM     
 LogDiagnosticsMessage "***** Logging SubSystem Version 7.3d" 
[SIZE=4][COLOR=Blue]    
 Set fs = CreateObject("Scripting.FileSystemObject")     
    
 If fs.FileExists("C:\Reports\LogEnable.txt") Then
[/COLOR][/SIZE]        EnableReportAndLogging = True    
       LogDiagnosticsMessage "This Computer is flagged as Server And Logging is True"   
 Else: EnableReportAndLogging = False         
       LogDiagnosticsMessage "This Computer is flagged as Client And Logging is False"   
 End If

.
.
.

The above method of determining what computer does the logging only requires that a text file (or, any other of your choosing) resides in a particular directory on the PC.

This also means that you can turn logging on and off by placing the named file in the target directory, or deleting it, and restarting the FTView client.

-----------------------
 
Last edited:
rdrast,

Nice post! I'm a database developer responsible for working with my clients Rockwell guys to get the data into FTView 8.10. It seems I might have the inverse skill set compared to many on these forums - SQL/ADO/VBA is the easy part -
TagGroups, hidden displays, etc. - not so much.

My first question relates to this:

'-----------------------------------------------------------------------
' For all groups, the topic is built up of the global IOTopic value.
' This is to make it easy to update this VBA code for different
' machines and equipment. The actual tags still need to be hard-coded
'
SampleGroup.Add "{[" & IOTopic & "]CMD_Sample}"
SampleGroup.Add "{[" & IOTopic & "]DataTag1}"
SampleGroup.Add "{[" & IOTopic & "]DataTag2}"
SampleGroup.Add "{[" & IOTopic & "]DataTag3}"
SampleGroup.Add "{[" & IOTopic & "]DataTag4}"

I like how you've made the IOTopic variable. Is there a syntax for TagGroup.Add method which would allow variable TagNames?

For example, If my recordset field names matched the tagnames exactly could I make this code more generic?

Last question, somewhat off topic: Do you have any suggestions as the where a Db guy like me could get more examples of this stuff? Is this the best forum for FTView VBA? I cannot find much in web search.

Thanks in advance.
Josh
 
Reading/writing to Access Database

I'm working on a project with a client that needs to be able to write batch information to a traceability database during production.

They are using an FTView ME with an Allen Bradley 1769-L32e PLC and Advantech touchscreen PC to run an assembly station.

I'm planning to use a Win7 PC with MS Access for the database.

As boxes of parts come in to be arranged in the assembly fixture, they will be scanned (by barcode on USB scanner) and the information about that batch of parts needs to be stored in a database. There are a couple of other calls for the database, including a few read requests and maybe two other write requests to do updates.

I'm an IT consultant who spends most of my time with web technologies and database work. I created an Access Database to meet the needs of the client. I now need to work on the part where the production system reads and writes to that database on it's PC.

That said, I'm pretty lost here. I think the awesome instructions that rdrast has provided are probably what I need to be looking at. However, I'm having trouble understanding what pieces of software are involved in this interaction, how they interact, and what exactly I need to be studying here. Can FTView ME send information directly to Access? Would OPC or ODBC be in the chain?

Another option I'm considering is having the production machine write to a text file. I can then have my database PC monitor the file for changes and import the information. That seems like it might make my life easier.

In short, how do I go about making this work?
 
I think you might have a problem using ME for this. ME doesn't support VBA in any way. Is this ME running on a PV+ or a PC running ME Station? There is a way I believe to get the FT Diagnostics to a DB, but I'm not positive. But this won't give you much flexibility on what is recorded.
 

Similar Topics

Hey guys how would you approach the spreadsheet screen part? I searched and everyone points out that using parameters to display this information...
Replies
0
Views
742
Hi folks, This looks like a long post but a lot of it it is just related/useful tech notes at the bottom. I'm trying to get data logging to work...
Replies
3
Views
3,624
Hello all I have a question, concerning datalogging in FTViewSE 8.0 I have 2 datalog models with 2 differents recording rates (30s for live...
Replies
0
Views
2,716
I have a client that is using an ODBC database to log a number of tags (110) every minute. This data is being displayed on a graph in the Client...
Replies
7
Views
2,596
Thanks for your time. I have added a new pen to a trend which works fine as long as you stay on the trend screen but resets when you navigate...
Replies
15
Views
25,500
Back
Top Bottom