Just to confirm: is M_Linked_Table an AF Table within your AF database?
It looks like a substitution parameter that returned the value of an attribute estipulated by the user (named Unit_Name, in your case) would fit perfectly in your case. You would use it in the SQL query of an attribute with data reference of type AF Table Lookup.
Unfortunately, the substitution parameter %Attribute% won't do it, because it returns just the name of the attribute that holds this data reference, instead of the value of attribute which you need to refer to (Unit_Name, in your case).
Therefore, this seems to require the creation of a custom AF Data Reference plug-in. The vCampus Library has a white paper with some examples of implementation of AF Data References that you might be interested in. It is under:
vCampus Library > White Papers and Tutorials > PI AF > White Paper - Implementing AF 2.0 Data References
I'll work on an example that is a little more specific to your case and I'll post it as soon as it is ready.
Within the config string of the Table Lookup Data Reference, we currently only support substitution parameters on the WHERE clause. I'll add this to the requested enhancements list for the Table Lookup Data Reference. Additionally, we have had a few requests to support substitution parameters on the Tabel Name in the past.
Thanks for your posts, Steve. Found a workaround that works.... I'll try to explain in words.
The goal: I wanted to look up data tags from a table (in Excel) and reference the GT unit as part of the SQL syntax in order to maximize use of templates. I created an attribute for the GT unit name... you'll see after the "=" sign.
I did it using the following syntax: SELECT [Row Name] FROM [Linked_Table] WHERE UNIT = @[|A reference to a value in an attribute]
It was working fine. However, a co-worker need to transpose the linked file in Excel for various reasons, and so the syntax had to change. I thought needed to find a way to reference the unit, (the value in the attribute) after the "SELECT" statement in the syntax and that is not allowed.
Workaround: If I name the Element exactly the name of the unit (which in the transposed table is also the name of the column), then SELECT [%Element%] FROM [Linked_Table] WHERE......
So I no longer need the attribute that holds the GT unit name.
Glad you found a work around. Would it still work if, in the future, you would like to repeat the same things for more elements in the same unit?
That's a good question. I will have to have one element for one unit. I will not be able to reference more than one unit in an element with %Element%. Thankfully, we plan to do it this way. It will make the most sense to our users. :)
Thanks for sharing your use case with us. I was going to ask you about that too!
I am glad you found a workaround, even before I finished the sample code I was preparing for your use case! For what it's worth, I got finished with the sample code now and decided to post it for future reference, for whoever finds oneself in a situation where building a custom DR is the most appropriate solution.
This is to be used in a Class Library project in VB. Besides the references added automatically by Visual Studio in a new Class Library project, it is required to add a reference to the AF-SDK COM interop (OSIsoft.AFSDK.dll) and compile it against .NET 3.5 (or earlier, but it can't be .NET 4.0).
As every Data Reference plug-in, it expects its user to setup its configuration string property (ConfigString), either directly in the ConfigString itself or indirectly via a provided GUI. The off-the-shelf DR plug-ins (as Formula, PI Point and Table Lookup) have a GUI that will setup the ConfigString automatically for the user.
As it is today, this sample plug-in (that I "nicknamed" AF_DR_TableLookupPlus) needs to be configured directly in its ConfigString.
It expects a string with 3 fields separated by semicolon:
A = name of the attribute whose value is the name of the column that will be requested in the AF Table
B = name of the AF Table that will be queried
C = clause of the AF Table query
In order to test it, I created a table in AF named M_Linked_Table, with columns PROPERTY, ARC1, ARC2 and ARC3, where ARC1, ARC2 and ARC3 are names of process equipments (units).
Also, I created an element with attribute named Unit_Name, whose value will contain the name of some process equipment (units).
Hope it helps whoever needs to develop a custom AF Data Reference plug-in.
af_dr_tablelookupplus.zip 56.7 KB
This is a great solution. Thanks for the time you have spent on it. Of course, this solution is much more elegant. I will try to implement it asap.
I am glad the sample code can still be useful for you!