The following steps describe how to create a Crystal Report which will prompt for a value and if nothing is entered select the alternative value from the database record.
This could be helpful if you have a report which selects values from the database but occasionally requires a manual override to enter an alternative value.
In this example we are using a finance payment form where the ‘cost code’ value from the database needs to be replaced with an alternative value.
Instructions
- Open the report using Crystal Reports (in this example we are using version 12 / 2008)
- Locate the value which will be replaced, e.g. “cost_code”.
- Hover over it and take note of the full database name, e.g. “vwGrantPayment.Cost_Code” Take note of this name for later on.
- Remove the field from the report by selecting it and pressing the ‘Delete’ button on the keyboard
- Create the prompt by right-clicking on ‘Parameter Fields’ in the ‘Field Explorer’ and selecting ‘New’
- Under ‘Name’ enter an appropriate name, e.g. “cost_code_override”. Take note of this name for later on.
- Under ‘Prompt Text’ enter an appropriate instruction for the end user (they will see this each time the report is ran), e.g. “Enter manual cost code:”
- Now we need to tell the report to use the manual value but if it’s empty use the database value, this is done using a formula field.
- Right-click on ‘Formula Fields’ under ‘Field Explorer’ and select ‘New’
- Enter an appropriate name, e.g. “select_cost_code”
- Enter the following formula based on the names noted earlier
- Save and close the formula editor
- Insert the formula field into the report
- Refresh or run the report, you should be prompted for the override value.
IF {?cost_code_override} = ""
THEN {vwGrantPayment.Cost_Code}
ELSE {?cost_code_override}
