8 Replies Latest reply on Oct 26, 2018 9:04 AM by gregor

    Table lookup for PI Point not working when using AF SDK

    JasonRust

      Hi, I have a template set up that populates the PI Point using a table reference lookup.  In PI System Explorer the PI Point looks fine and when I check the table that is used for the lookup the value that is supposed to be interpolated is present.  However, when I use the SDK to retrieve the AF Attribute "DataReference.PIPoint" it throws a PIPointNotFound exception and a message that "server\MANHOLE_%@MH Number%_VELOCITY" is not found.

       

      The strange thing is that 10 of my assets work just fine with the correct, interpolated PI Point coming back.  The other 3 exhibit this error where, for any tag under the asset, the MH_Number variable is not being interpolated when requested by the SDK.  I've checked permissions and double-checked the table lookup and found nothing. Any ideas?

        • Re: Table lookup for PI Point not working when using AF SDK
          Rick Davin

          Hi Jason,

           

          Thanks for posting your question here.  We could use a bit more information.  Is it possible that you can share (obfuscate where needed) the following:

           

          • The design and maybe some data in the table, particularly data that works versus data that doesn't work.
          • The ConfigString of the attribute using the Table Lookup Data Reference.
          • The ConfigString of the attribute using the PI Point Data Reference.
          • The AF SDK code you are using to retrieve the problem attribute.

           

          Keep in mind that PI Square is a public forum, so anything you post will be seen to any member, not just OSIsoft.  If you feel you cannot obfuscate and also keep the examples meaningful, then you may consider opening a confidential case with Tech Support.

            • Re: Table lookup for PI Point not working when using AF SDK
              JasonRust

              Great questions Rick.  Design is:

              • There is a table with a MHNumber and SiteNumber columns.
              • A top-level template is applied to each asset that defines the PI Point as "server\MANHOLE_%@MH Number%_VELOCITY" and this
              • Another top-level template that is applied to each asset creates the "MH Number" attribute by using the following table lookup: "SELECT MHNumber FROM sites WHERE SiteNumber=%@SiteNumber%".
              • Each asset has a SiteNumber attribute which is a number such as 100.

               

              The SDK code looks like:

              try {   PIPath = afAttribute.DataReference.PIPoint.getPath(); } catch (PIException e) {      if (e.StatusCode == PIStatusCodes.PIPointNotFound) {
                      // This is where the warning is logged that server\MANHOLE_%@MH Number%_VELOCITY is not found
                  }
              }

              I've yet to find any difference between those assets that work and those few that fail to find the path.  Given that everything looks fine when inspecting in PI System Explorer my only guess is a permissions issue, but so far I haven't found any discrepancies.

              • Re: Table lookup for PI Point not working when using AF SDK
                JasonRust

                Found the answer/bug after much experimenting and it turned out be that there is a bug in PI's indexing of String type columns in lookup tables.

                 

                The PI Point is being resolved with a query like ""SELECT MHNumber FROM sites WHERE SiteNumber=%@SiteNumber%".  SiteNumber is a column of type String in the lookup table.  The reason it is a string instead of Int32 is to allow for a SiteNumber like "001" and "5A".  The lookup fails if the SiteNumber is 11.  However, I was able to make it succeed if I changed it to 11A or if I changed the column type to Int32.  I did try re-creating the row with SiteNumber of 11 from scratch to ensure that there were no hidden characters.  I can only guess that it fails the lookup when using the SDK, but works in PI System Explorer, because they are using different methods for finding the row.

                 

                For reference, I am using PI Data Archive 2016 R2 (3.4.405.1198) and PI AF Client 2016 R2 (2.8.5.7759).  Any idea if this is a known bug that has been fixed in later versions of PI?

                1 of 1 people found this helpful