Generally, to maintain referential integrity, avoid deleting items from a Domain that reports need to use. Sometimes, removing an item or set from a Domain is unavoidable. For example, you included an item that exposes confidential data to unauthorized users, or the underlying database has changed, making the item invalid.
In a typical scenario, an administrator or data analyst creates a Domain and many end-users create Topics and reports based on it. When deleting Domain items in such a situation, tell users beforehand to modify reports to prevent reports from using the item. Inevitably, a user fails to modify a report as requested, and needs to fix the broken report. The server provides a method for fixing the report. The user can replace the deleted item with a placeholder, open the report, and edit it, even after the original item has been removed.
The following procedure assumes a user created a report based on a Domain. The Domain is similar to the one described in Example of Creating a Domain. The Domain exposes the accounts_opportunities table that contains no useful columns and is necessary only to join the accounts and opportunities tables. Unfortunately, some users were confused by the additional items exposed by the Domain, and used the date_modified item in their reports. You want to delete the accounts_opportunities table from Sets and Items, so it is no longer exposed to users.
To fix referential integrity problems:
| 1. | Locate the problematic Domain in the repository, right-click, and choose Edit. The Edit Domain page appears. |
| 2. | Click Edit with Domain Designer. |
| 3. | In the Domain Designer, click the Display tab and select the public_accounts_opportunities set in Sets and Items: |
| Deletion of Items in Use in Reports from a Domain |
| |
| 4. | Click Delete Item. The Confirm dialog box appears, indicating that the item is in use and that a potential referential integrity problem exists. Click See items that will be deleted to access the Detail dialog. |
| |
| Indication of Referential Integrity Problem |
| 5. | Close the Detail dialog box and click OK in the Confirm dialog box. |
| 6. | Click the Calculated Fields tab. |
| | Because a calculated field is a custom column of a user-selected type, it can serve as a placeholder. The value of the custom column is irrelevant, but it needs to be recognized as a placeholder when it appears in a report. |
| 7. | Enter a field name, type, and an expression that returns a constant value of the same type as the deleted item, as shown in “Creation of Calculated Field as Placeholder for a Deleted Item”, and click Save Field. |
| |
| Creation of Calculated Field as Placeholder for a Deleted Item |
| | The ZZ prefix in Name distinguishes the placeholder from other columns in the Domain. The value in Type must match the datatype of the deleted item, in this case a timestamp. For information about calculated field datatypes, see Datatypes. |
| 8. | Click the Display tab. |
| | A placeholder item must be located in placeholder set to mimic the structure of the deleted item, and generally, in exactly the same path of nested sets as the original item. You deleted the accounts_opportunities set, so you need to create a set for the placeholder item. |
| 9. | In Sets and Items, click New Set to create the placeholder set. |
| 10. | In Resources, expand the list of Constants and select ZZdate_modified: |
| |
| Calculated Field Set Up as a Placeholder |
| 11. | Click  |
| 12. | In Sets and Items, select the placeholder set, newSet1. |
| 13. | In Properties, click Edit and change these properties: |
| Property | New Value |
| ID | accounts_opportunities |
| Label | ZZDeletedSet |
| Description | Placeholder for deleted set |
| 14. | In Sets and Items, select ZZdate_modified. |
| 15. | In Properties, click Edit and change these properties: |
| Property | New Value |
| ID | date_modified1 |
| Label | ZZDeletedField |
| Description | Placeholder for deleted item |
| | Distinguishing the placeholders from real items by using the ZZDeletedSet and ZZDeletedField labels discourages their use in building new Ad Hoc views. The IDs, although invisible to users, must be identical to those of the deleted item and set, so the server can open and run the view. |
You can see the modified properties for the placeholder item in “Modification of ID Properties to Match the Deleted Item”.
| |
| Modification of ID Properties to Match the Deleted Item |
| 16. | Click OK in the Domain Designer and then Submit on the Edit Domain page. |
Alternatively, you can edit the XML design file of a Domain to fix referential integrity problems. The modifications are the same as described in this procedure, but the order is slightly different.
To fix referential integrity problems in the XML design file of a Domain:
| 1. | Instead of deleting the item, first create the constant calculated field. You can do this in the Domain Designer even before exporting the design file. You must be familiar with the design file syntax to create the calculated field with the correct type and expression, as described in Structure of the Design File. |
| 2. | Next, locate the item definition and change its resourceID so that it references the newly created calculated field. This removes the reference to the unwanted column, and replaces it with an item that references the placeholder field. |
| 3. | Update the other properties of the item and its enclosing set, using the values shown in “Modification of ID Properties to Match the Deleted Item”. For more information, see Representing Sets and Items in XML. |
| 4. | Save the design file and upload it as described in Uploading a Design File to a Domain. |
Recommended Comments
There are no comments to display.