View

A view is a "virtual table that represents the data in one or more tables in an alternative way."

You can use a Database View as a List From Table to open up complicated netFORUM data to a Query and Search.

Steps to Add View

To add a SQL Database View as a Table in netFORUM do the following:

Create the View in SQL. For a client, name the view vw_client_abc_*** where abc is the client abbreviation. Adhere to general naming conventions, in particular the 3-character column prefix rule. Generally, your view should have one column that will ultimately map 1:1 to a Primary Key column of another table. Example, vw_ac_invoice.sum_inv_key matches 1:1 with ac_invoice.inv_key. This is needed to set up List Table appropriately.
In other instances, you might have a view that can be thought of as one-to-many relationship.
See Sample View below for an example.
Tip: if you are creating a summarized view of a custom client table with a column prefix of x03, then make your view’s prefix be v03. This makes it easy to associate x03 table columns with v03 view columns. If you are making a summarized view based on baseline tables, then you must be more creative with your naming conventions. Remember that you must always add a number to your 3-character column prefix (for custom client views), and we recommend making the first character of the 3-character column prefix be the letter V to signify it is a view.
Grant Select permissions. Be sure to grant SELECT permissions to the netForumReport group as well in order for the exports to work on the List Result because these processes run through the netForumReportUser user:
GRANT SELECT ON dbo.viewName TO netForumUser,netForumAdmin,AvectraAdmin, netForumReport
go
Add the view manually in the Table area of netFORUM in the Admin module. Be sure to enter a friendly table description, the key column name and enter the 3-character column table prefix.
Run these three SPs to:
- Populate columns (md_column)
- Initialize Group Table Privilege and Group Column Privilege
- Populate user privileges based on these Group Table/Column settings
exec dbo.md_column_populate
go
exec dbo.fw_group_table_column_privilege_populate
go
exec dbo.md_privilege_populate_by_table_quick '<<< View Name >>>'
go
Go back to netFORUM and refresh your Table view.
Now you should see the columns from the Columns child form. Edit each column and choose the correctdata type if it's wrong and enter a description. In many cases, the SP md_column_populate is unable to map native SQL datatypes to the corresponding netFORUM User-Defined-Datatypes so you must do that yourself. This is necessary in order for netFORUM to show the appropriate Operators to the user when writing query conditions.

See the chart on Data Type List for a mapping of the "front end" column data type descriptions with the corresponding Abila user-defined data types.
To make your new view available as a queryable table in the Query tool, go to any main List Table and add a From Table to your view. You will probably want to make it a "Left Join" and the "Join Expression" will probably use the one column described in the first step (the column that relates to a PK of the other table).
If needed, you may add a From Column, so the column will appear on List Results as well, but use caution as this could impact performance negatively if the column you chose has complicated calculations. Remember that this column will be called on every list by any user.
To make your new view available in the Sub-Query, then add the view as its own main List Table and also a From Table to itself. This is required for the table to appear in the Sub-Query list. Please do this to make the view more useable in the query tool.
If desired, you may add columns from the View as a Search Control so it will appear on a Search page.

Sample View

In our example, our view is intended to be a one-to-one JOIN from the Customer to the View. In other instances, you might have a VIEW that can be thought of as one-to-many relationship.

In our example, we have a VIEW that summarizes certain registrant information for one customer. The “primary key” of the view is v50_ind_cst_key and in practice it operates as a “foreign key” to [co_individual].[ind_cst_key] in a one-to-one relationship.

IF EXISTS(SELECT name FROM sysobjects WHERE name = 'vw_av_ev_registrant_summary')
begin
DROP VIEW vw_av_ev_registrant_summary
end
go

CREATE VIEW dbo.vw_av_ev_registrant_summary

AS

SELECT
v50_ind_cst_key = cst_key,

v50_last_event_attended_date = (SELECT max(reg_registration_date)
FROM ev_registrant (nolock)
WHERE reg_cst_key = cst_key
AND reg_attendance_flag = 1),

v50_events_attended_count = (SELECT count(*) FROM ev_registrant (nolock)
WHERE reg_cst_key = cst_key
AND reg_attendance_flag = 1),

v50_reg_fees_net = (SELECT sum(v.[Net Total])
FROM vw_ac_invoice_detail_summary_net v (nolock)
WHERE v.ivd_cst_key = c.cst_key
AND v.ivd_key IN (SELECT r.reg_ivd_key FROM ev_registrant r (nolock)
WHERE r.reg_cst_key = c.cst_key))

FROM
co_customer c (nolock)

WHERE
cst_type = 'Individual'
go

-- permissions

GRANT SELECT ON dbo.vw_av_ev_registrant_summary TO netForumUser, netForumAdmin, AvectraAdmin, netForumReport
go

-- view in action:

SELECT * FROM dbo.vw_av_ev_registrant_summary

Sample View Setup

Based on the sample view above, we show you how to configure this view in netFORUM.

Go to the List Table from which you want your view to appear and add a List From Table that points to this new view. In this instance we add the List From Table to the List Table for the Individual [co_individual] table.

Observe the last from table in this list and its join expression.

If needed you may add a From Column as well, but use caution as this could impact performance negatively if the column you chose has complicated calculations. Remember that this column will be called on every list by any user.

Now this table is available for queries on the Individual query.

Not only can these columns be displayed, but they can be used as query conditions.

Examples of Baseline Views

To see some examples of baseline views working this way, do a search in the Table area for any "tables" that begin with vw_. Good examples are:

vw_ac_invoice
vw_ac_invoice_detail
vw_customer_member_flag - Customer Receives Member Benefits (vst)
vw_pricing_control_pcc View - Pricing Control (pcc)
vw_product_search
vw_co_customer_credit_balance_caa

Use of SP `md_view_populate`

Deprecated Notice:
In the past, we used md_view_populate as one of the steps. This SP should be considered deprecated and should not be used. Use the steps above instead.

Note: The SP md_view_populate, has been overhauled. At this point, we recommend using the steps described until the SP can be fully evaluated in the 2007.01 build and new SOPs can be developed for developing views in that build.

Add Additional Column(s) to Existing View

If you already have set up a view as described above, and you need to add more columns to it, then you should add the additional columns to the view and then run steps #4, #5, #6 above.

Important! Do not add custom columns to baseline views.