1214114 - Error: 'This field name is not known' when attempting to save or verify a formula in Crystal Reports

SAP Knowledge Base Article - Public

1214114 - Error: 'This field name is not known' when attempting to save or verify a formula in Crystal Reports

Symptom

  • This field name is not known.
  • When attempting to save or verify a formula in Crystal Reports, it generates the error: "This field name is not known".

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016

Reproducing the Issue

  1. In Crystal Reports, create a report off any data source.
  2. Create a formula.
  3. When attempting to save or verify the formula, it generates the error: "This field name is not known."

    KB4.png

Cause

  • The reason this error occurs is because a database field used in the formula is using French brackets ({}).
    NOTE: If you are receiving this error and the database field(s) contained in the formula does not contain the French brackets then the formula contains a syntax error.
  • Crystal Reports uses French brackets as field delimiters. Therefore, when a database field also contains French brackets, Crystal Reports cannot determine the end of the field name or formula because the close French bracket appears before the end of the database field name.

    For example:  The database table name is Customer. The 'Customer' table contains the following fields:

    - Name
    - Address
    - Address {2}

    In the Crystal Reports formula editor, 'Name' is selected and appears in the following format:

    {Customer.Name}

    Crystal Reports embraces the table and field name with French brackets. When the formula evaluates it looks for the open French bracket to find the start of the database field name until it reaches the closing French bracket. Once the entire contents contained in the French brackets are found, they will be replaced with the value held in that database field.

    In the Crystal Reports formula editor, 'Address {2}' is selected and appears in the following format:

    {Customer.Address{2}}

    The formula tries to find Address {2} and cannot. The first close French bracket appears after the 2, therefore, the database field name looks like Customer.Address {2. When you verify the formula, the error message "Field name is not known" appears.

Resolution

  • In order to resolve this situation, you can complete one of the following:
    1. Create an 'SQL Expression Field to use in a formula, rather than using the database field directly; or
    2. Rename the database field name on the database side; or
    3. Create a command object or stored procedure and create an alias for database field that contains French bracket.
          
  • Creating an SQL Expression Field in Crystal Reports.

    To create an SQL Expression field and use it in the formula instead of using the database field directly, complete the following steps:
    1. From the 'Insert' menu, select 'Field Object'. This launches the 'Field Explorer'.
    2. From the 'Field Explorer', select 'SQL Expression fields' and then click the 'New' button. Please note, that the 'SQL Expression fields' option only appears when ODBC is used to connect to the database.
    3. From the 'SQL Expression Name' box, enter a name such as Address2 and then click 'OK'.
    4. From the 'SQL Expression Editor' select the database field name that contains the French brackets.
    5. Save and close the SQL Expression.

      Now that you have created a SQL Expression for the database field that contains French brackets, create a formula and use the SQL expression field instead of using the database field. SQL Expression fields are prefixed with the % symbol in Crystal Reports.

      NOTE: The following suggestion requires that you have good knowledge of your database and SQL syntax. If you are unsure, please contact your Database Administrator for the following solution.

  • Renaming the database field name on the database.

    To rename a database field name so that it does not contain French brackets, consult the database manual or your the database administrator.
    Once the database field name has been modified, you must verify the database in Crystal Reports.

    To verify the database, complete the following steps:  
    1. Log on to the database.
    2. From the 'Database' menu, select 'Verify Database'.
    3. After the database is verified and the changes updated, create a formula based on the newly rename field.

  • Create a command object or stored procedure and create an alias for database field that contains French bracket.

    To create a stored procedure or command object that uses alias, consult the database manual or your the database administrator.


Keywords

"FIELDS NOT KNOWN", CR , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016