Table Views

dbFront allows for the ability to add one or more views to a table. The purpose of table views is to make extra or formatted column information available. Examples are:

Summary counts or totals of child records. e.g. Invoice Totals,
Formatted address block for reports,
Include the most current Permit Document,
Include a picture of the Staff Member who last served the current customer.

View Fields can be used anywhere that a regular table field can be used. This includes Reports, Exports, Row Security Expressions, Dropdown Columns, everywhere...

An obvious warning is that a poorly designed view can cause significant performance issues.

Join Fields

The Views join automatically on any matching fields to the primary table. For this reason, you should name your view field names very carefully to ensure they only join on the fields you intend. dbFront displays the Key fields before you add the view to a table.

Note: Views can join on ANY regular field. This means you could use a view to bring the description for a selected License code, Permit type, or specific procedural documentation.

Views can return multiple records but a view must return only one record for each unique joined key. A view that is keyed on the field ProductID does not have to return a matching record for each ProductID, but it can't return more than one record per ProductID. dbFront will scan your view to ensure it does not violate this rule.

Data / Formatted Fields

You should rename Non-Join fields 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, add the new view 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.

Troubleshooting

The following are some TableView-related issues and solutions: