Forum Discussion

jbennett0108's avatar
jbennett0108
New Contributor II
3 years ago

Check to see if an object (lookup table) exists

I am modifying a business rule that uses the entity name to search a similarly named (Entity_Acct_Description) table and return a value.  I want to verify that the table exists before attempting the TransformText to prevent errors in the case of new entities or name changes.  Is there a way to verify the named object exists before executing the code?

 

Else
Dim LookupTable As String = TEntity & "_Acct_Description"

''''IF Lookuptable exists do this''''
SAccountDesc = BRApi.Utilities.TransformText(si, SAccount, LookupTable, True)

5 Replies

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor

    There are two ways to do this.
    The first is to use Exception Handling, and catch the Exception.
    Once that has been done you can use methods like GetBaseException() and test if it was caused by an item not being found. Here is a code example that tries to perform as TransformText on a non-existent RuleGroupName (Lookup table name), but *without* bombing out with an error:

    '--------------
    Dim strRuleGroupName As String = "E101_Acct_Description"
    
    Dim strResult As String = String.Empty
    Dim bTableExists As Boolean
    Try
      strResult = brapi.Utilities.TransformText(si, "11000", strRuleGroupName, True)
      bTableExists = True
    Catch ex As XFException
      Dim baseException = ex.GetBaseException()
      brapi.ErrorLog.LogMessage(si,"Inside exception, ex.Message:" & baseException.Message)
    
      If baseException.Message.Equals(GeneralStrings.ItemWasNotFound) Then
        bTableExists = False
      Else
        Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
      End If
    End Try
    brapi.ErrorLog.LogMessage(si, "Lookup table " & strRuleGroupName & " exists? : " & bTableExists.ToString() )
    '-----------------------------

    The example above checks the originating Exception is in fact caused by the lookup table not existing, by comparing with the pre-defined message constant : GeneralStrings.ItemWasNotFound , which would be thrown deep inside one of the internal WCF classes. This way if another type of exception occurred (which has nothing to do with the lookup table not existing) then it would generally throw an error otherwise, which is what you probably want.  Basically, when I catch an exceptions never assume the cause of it, hence the test on the BaseException's cause.
    And *obviously* take out the LogMessage statements when you are done testing!

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor

    The other option would be to lookup the StageRuleGroups directly.
    You could either use plain old SQL for this (just ensure you don't open/close connections inside a loop) or the BRAPI methods to retrieve a datatable, using table name of "StageRuleGroups" and the lookup column "RuleGroupName".  If doing this in a loop across multiple entities , in an Extender Rule, then obviously you should pull all the rows into a DataTable first, before entering the loop, and then lookup the row in a Dictionary object if you need to perform lookups inside a loop aross multiple entities. You don't want to continually be running SQLs inside a loop, or on every data unit being calculated ( For Finance Business Rules then cache the DataTable in the Globals object, or use the api.Functions.GetCustomBlendDataTable() to automatically read and cache a data table , which doesn't have to be a BI Blend table despite the name of the function ).

    • JackLacava's avatar
      JackLacava
      Community Manager

      On this note, it's annoying that the lovely LookupRowFieldValue method, which (I believe) will return a single value for a particular field in a particular table, is only available for external databases...

  • jbennett0108's avatar
    jbennett0108
    New Contributor II

    Thanks!  I had thought about the exception but was hoping for something more preemptive.  That should work with any set of objects though.  I'll probably try both and see which works better here.  

  • ChrisLoran's avatar
    ChrisLoran
    Valued Contributor

    Just a heads-up, if anyone's thinking of using
      brapi.Database.GetCustomDataTable( )
    ... and then just passing it the name of a standard XF table, the function actually checks if the table name you passed is a standard table or not, and just returns (nothing) if it is a standard XF table.  So you'd either have to use unsupported internal object methods , or regular SQL (and cache the resulting DataTable if calling repeatedly).