dbFront allows for the ability to add one or more views to a table. The purpose of table views is to make extra column information available. Examples are:
- Summary counts or totals of child records. e.g. Invoice Totals,
- The most current Permit Document.
- Include a picture of Staff Member who last served the current customer.
View Fields can be used everywhere that a regular table field can be used. This is includes, Reports, Exports, Row Security Expressions, Dropdown Columns, everywhere...
An obvious warning is that a poorly designed view can cause significant performance issues.
The Views are joined automatically on any matching fields to the primary table. For this reason you should name your view field names very carefully to ensure it only joins on the fields you intend. dbFront displays the Key fields before you add the view to a table.
Note: Joins are allowed on ANY regular field. This means you could use a view to bring in all kinds of information related to any aspect of a record.
The only requirement is that the view must return a single record for each key value. What this mean is, if your view is keyed on a ProductID, then it should return only one record for each different ProductID. It can return nothing but it can't return more then one record. dbFront will scan your view to ensure it does not violate this rule.
Data / Formatted Fields
You should renamed Non-Join fields in order to avoid unintended joins and to make it easier to recognize formatted fields. My current standard is to precede each Non-Join field with a lowercase "v". You can pick whichever standard works for you.
Renaming or Replacing Views
Renaming or replacing views can be a big pain if not done correctly because if you remove a view first then dbFront will force you to remove all references to that view before you can proceed.
The best way to replace or rename a view is to proceed in the following order:
- Create or copy the new view with the same fields as the old view.
- Open the table preferences and add the new view and position it above the old view and then save.
dbFront will then rebind all of the view fields to the new view and mark the old view fields as duplicates.
- As the last step you can open the table preferences and remove the old view.
Once you have verified that the old view is no longer used by dbFront then it is safe to remove it from the database.
The following are some TableView related errors and solutions: