Accessing PI AFTable Data in ACE Modules Using AFSDK and VB.NET

When working with OSIsoft PI AF (Asset Framework), it's common to store reference data or specifications in AFTables and want to retrieve them efficiently during automated calculations, such as in ACE (Advanced Calculation Engine) modules. This blog post explores how to access and filter data stored in AFTables from your ACE modules using the AF SDK and VB.NET, as well as alternative approaches using Table Lookup Data References in AF attributes.

Why Use AFTables in PI AF?

AFTables allow you to store tabular data (for example, equipment specifications, lookup factors, calibration data) within your AF database. These tables can then drive calculations or provide context to your PI System analytics.

Approach 1: Direct Access via AF SDK and DataTable

A powerful way to access AFTable data is by leveraging the System.Data.DataTable representation of your AFTable. The .NET DataTable class provides robust filtering and selection capabilities via the Select() method.

Example: Retrieving Rows with Criteria

Suppose you have an AFTable named SpecsTable with columns such as ID, Description, and Value. Here’s a generic approach to retrieve records where ID = 'Pump1':

Imports OSIsoft.AF
Imports OSIsoft.AF.Asset
Imports System.Data

' Establish connection to your AF Database
Dim myPISystems As New PISystems()
Dim afDb As AFDatabase = myPISystems.DefaultPISystem.Databases("Configuration")

' Access the AFTable and refresh data if necessary
Dim afTable As AFTable = afDb.Tables("SpecsTable")
afTable.Refresh()

' Convert AFTable to DataTable
Dim dt As DataTable = afTable.Table

' Filter rows: equivalent to SQL WHERE clause
Dim resultRows() As DataRow = dt.Select("ID = 'Pump1'")

' Access values from your results
For Each row As DataRow In resultRows
    Console.WriteLine($"Description: {row("Description")} - Value: {row("Value")}")
Next

Tip: Handling Column Names With Spaces

If your column name contains spaces (e.g., Returned Date), wrap it in square brackets:

dt.Select("[Returned Date] = '2023-06-15'")

Generalized Filtering Function

Here’s a utility function for filtering rows by column/value:

Public Function Read_AFTable(ByVal dt As DataTable, ByVal column As String, ByVal value As String) As DataRow()
    Return dt.Select($"[{column}] = '{value}'")
End Function

Approach 2: Table Lookup Data Reference in AF Attributes

If your goal is to retrieve a single value or simple lookups in calculations, consider configuring an AF Attribute with a Table Lookup Data Reference. This enables you to fetch values directly via attribute reads, simplifying your ACE code:

• Configure your attribute to use Table Lookup.
• Set up the row/column selection criteria.
• In ACE, just read the attribute value.

This method is less flexible than custom DataTable queries but is easier for straightforward lookups and improves AF-centric configuration.

Conclusion

Accessing and querying AF Table data within ACE modules is straightforward with the combination of AF SDK and .NET DataTable features. For complex logic or variable queries, use DataTable.Select(). For standard lookups, consider AF attribute table lookups. Both methods allow PI System users to harness tabular reference data for scalable, powerful calculations.

---

Keywords: PI AF, AFTable, ACE, AF SDK, VB.NET, DataTable.Select, Table Lookup Data Reference, OSIsoft PI

Further Reading:

• Microsoft Docs – DataTable.Select Method
• OSIsoft PI System Documentation