ph_dbwhereused PowerHome formula function
Description
Dynamically creates a SQL string that will search for the specified search string with the option to execute the SQL and return all instances where the search string is found 
Syntax
ph_dbwhereused ( as_search, ai_flags )
Argument Description
as_search String. The search parameter to looked for within the PowerHome database. Use the "%" character as a wildcard.
ai_flags Integer. Flags controlling how the data is to be returned.
Return value
String. Returns either a string containing the dynamically generated SQL statements to search the database or the actual results of executing the SQL statements against the PowerHome database 
Usage
Use this function to build SQL statements that allow you to search the PowerHome database for a specific search term and optionally perform the search and return the results. This function will use the PowerHome database dictionary to build appropriate SQL statements to search tables and character based columns for the search term. This function will not search numeric column datatypes or date datatypes and will only include character based columns in the generated SQL. This function is used by the menu item "Database Where Used" option under the "Reports" menu. 

Note: Most of the time your search term will be surrounded by the "%" wildcard character to make partial matches. Without wildcards, the search string must EXACTLY match the entire contents of the searched column

Valid values for the ai_flags parameter (multiple desired values are added together to get a final value) are:

  • 1 - SQL will include text descriptions of returned values
  • 2 - Exclude generating SQL for tables in the DBWUEXCLUDE table
  • 4 - Include searching tables that don't have a primary key
  • 8 - Return the result of actually executing the search SQL rather than the SQL itself
    • Examples
    The following examples demonstrate typical syntax/usage for this function.
  • ph_dbwhereused("%email%",15) - Generates SQL searching for the word "email" within PowerHome tables that have a primary key and are not excluded in the DBWUEXCLUDE table. The data returned will be the results of actually running the generated SQL and will include text identifiers for the returned column values