10 things you did in 10 minutes with the toolkit
TRANSCRIPT
10 Things You Did in 10
Minutes with the Toolkit
10 Things in 10 Minutes Part IV
• This year, for the first time, all 10 Things submitted by attendees
• Showcase for ingenuity and creativity of netFORUM Developers
using netFORUM development platform
• This presentation will be available for download following the
conference
Roll Up Child Forms
Kate Evans Pollin
Manager, Application Development & Consulting
Information Systems #410
American Speech-Language-Hearing Association (ASHA)
Background:
• Members accumulate professional development hours but have to
pay a fee to have them included in printed transcripts.
• If more than one year is unpaid, members on a budget want to pay
only for the years with the most credits
Solution Requirements
• Summarize professional development activity annually
• Clearly display what will be on transcript and what will not
• Quickly answer questions about how many credits in year X
“Roll Up” Child Forms
• The User Experience
• User sees 2 forms which divide history by whether they occur
within paid Registry years or not.
• History is grouped by year with totals for number of courses
and number of credits.
“Roll Up” Child Forms
The Child Form SQL
• Creates a temporary table for the current customer w/ sum() &
counts()
• Results are selected from the temp table sorted by year Desc
“Roll Up” Child Forms
Grandchild Form SQL
• Uses variables cru_cust_key and Year to select records
• Lists the course offering information associated with each year
“Roll Up” Child Forms
eWeb Panel with Conditional Visibility
Dan Hickey
Manager of Website & Database Operations
American Massage Therapy Association
eWeb Child Form
• In the example we have an eWeb child form that is only visible to a
specific non-terminated member type based on membership dates.
• Add a new content row to the eWeb page where the child form will
reside:
List SQL
• Enter the List SQL
for the information
that will be
displayed in the
child form:
Visibility SQL
• In the http://wiki.avectra.com/Visibility_SQL box create the SELECT
statement that will pull the membership key based on the
membership requirements that are set. In our case it is pulling a
specific non-terminated, non-deleted member type whose expiration
date is greater than the current date.
User View
• RESULT: Here is a screen shot
of the Individual’s eWeb page
who does not meet the criteria,
the visibility SQL statement will
not return a result:
User View
• Here is a screen shot of an
Individual whose
membership information returns a mbr_key from
the visibility SQL
statement:
CMS Setup
• You can now create new content (or
link to previously created content) and
link to this child form. In our example
we created a custom wizard and form
to add, and edit, the information in the
child form.
Form Extensions
Pradnya Devare
Software Solutions Specialist
National Association for the Specialty Food Trade,
Inc.
New York, NY
The Goal
• To enable staff to identify the sales person and service
representative assigned to a company
*on the organization profile page*
The Problem
• The representative name had to be accessed from the User table (fw_user), based on the ste_usr_key from
the sales territory table (sf_territory_assignment).
• There are only a set number of tables available to select
columns from, from the ‘Control to Add’ list on the designer
profile page. These columns are based on the Data
Objects of the Form’s Object.
The Solution
• Create a form extension to be available on the ‘Control
to Add’ list so that the field will be accessible in the list.
Steps to create form extensions
• Navigate to the form on which you want to display additional text. Click on the ‘Edit this form content’ icon to go directly to the dynamic form.
• To this dynamic form, add a Profile Detail containing XML (sql), Parameters and XSL under the Dynamic profile detail child form.
• When you finish creating the profile detail form, edit the form again and copy the primary key of the form.
• Create an extension, and specify the primary key of the profile detail form created above as part of its definition.
Steps to create form extensions
Dynamic Form
Steps to create form extensions
Dynamic Profile detail definition
Steps to create form extensions
Profile text definition (3 parts)
1. XML to return data:
{BeginXml}
SELECT
[Representative] = usr_friendly_name
from sf_territory_assignment (nolock)
left join co_customer OrgCst ON ste_cst_key = OrgCst.cst_key
left join fw_user Rep ON ste_usr_key = usr_key
where OrgCst.cst_key = {parameter_cst_key} and usr_delete_flag = 0
Order by [Representative]
FOR XML PATH ('individual'), ROOT ('root')
{EndXml}
Steps to create form extensions
2. Parameters to pass to XML command in step 1:
{BeginParameters}
<Parameter>
<Name>parameter_cst_key</Name>
<Value>{org_cst_key}</Value>
</Parameter>
{EndParameters}
For reference, after it has been passed a parameter, the SQL in Step 1 will output XML that looks like this:
<root>
<individual>
<Representative>Tammy Butcher</Representative>
</individual>
</root>
Steps to create form extensions
3. XSL template to format XML results{BeginXsl}
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/root">
<DIV>
<xsl:apply-templates select="individual" />
</DIV>
</xsl:template>
<xsl:template match="individual">
<DIV class="tinytxt">
<xsl:value-of select="Representative" />
</DIV>
</xsl:template>
</xsl:stylesheet>
{EndXsl}
Steps to create form extensions
Form Extension definition
The red arrow points to the 3rd
parameter, which is the primary key of the Profile Detail that has just been added.
• Check the ‘has extensions’ field on the profile of the parent form
• Also remember to put metadata locks on these 2 controls.
Steps to create form extensions
What you should see when you are done:
Steps to create form extensions
After you’ve created the Profile Detail and Form Extension, design the form
and add the extension to the form. The extension will now be available on
the ‘add to control’ list.
REMEMBER: Metadata lock the label and extension on the form.
In Conclusion
The use of form extensions opens up netFORUM,
and the possibilities for almost any customization
that you may want to do!
Reports for Scheduling SP Runs
Rajesh Atitkar
American Chemical Society
Quandry
• In netFORUM’s user interface, there is no way of scheduling a
Stored Procedure to run. The only way we can do it is by using
scheduled task, but this needs to happen at the server level.
• We wanted an interface for an iWeb user to submit the parameters
for the SP and also specify the run time. But do do this, we would
need to create a form and then write C# code to create a scheduled
task. We did not want to write any code to do this.
• Hence this is what we came up with:
Solution
• We created an empty shell report and setup the required
parameters for it. The parameters included the parameters for the
SP as well as the date when we wanted to run the process.
• The code was added to the report SQL so that when the report ran,
it took all the parameters entered by the user and then created a
SQL Job on the database server to run at the time entered by the
users. The remaining parameters were passed to the SQL Job as
parameters for the SP.
• We use this to run a Monthly Snapshot Process as all it does is
creates the data for Snapshot reports but we wanted users to
specify when it was run.
Report Metadata Setup
Report Definition in RDL File in MSRS
SQL in Report Stored Procedure
/****** Object: StoredProcedure [dbo].[client_ACS_mb_membership_monthly_generate_submit]
Script Date: 02/25/2011 11:24:06 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure [dbo].[client_ACS_mb_membership_monthly_generate_submit]
@p_year varchar(4),
@p_month varchar(2),
@CurrentUserName varchar(100)
as
set nocount on
set transaction isolation level read uncommitted
declare @parm varchar(1000),
@p_email_id varchar(100)
select @p_email_id = usr_email from fw_user where usr_code = @CurrentUserName
set @parm = 'Exec client_ACS_mb_membership_monthly_generate ' + @p_year + ',' + @p_month + ','''+ @p_email_id + ''',''' +
@CurrentUserName+''''
--select @parm
exec msdb.dbo.sp_update_jobstep
@job_name = 'Generate MMR and Demographics data',
@step_id = '1',
@step_name = 'Step 1',
@command = @parm
exec msdb.dbo.sp_start_job @job_name = 'Generate MMR and Demographics data'
GO
Searchable White Paper Website
William Keenan
Information Technology Manager
Craft & Hobby Association
Challenge
• Develop an inexpensive searchable eWeb white paper application
Web Postings
• Staff add white papers through iweb as web postings
eWeb Page Search Page
• Find.ascx for the search function
• List results rendered by XslGenerator.ascx
Detail Page
• eWeb detail page uses XslGenerator.ascx
CBSA Codes
Brian Kurihara
Database Administrator
Association for Financial Professionals
What is CBSA?http://www.zipcodedownload.com/Products/Product/CBSAUSA/Standard/Overv
iew/
http://en.wikipedia.org/wiki/Core_Based_Statistical_Area
Upload CBSA codes
• Zipcode, CBSA code, CBSA name
Add extender to co_address_ext
Set CBSA name for all U.S.
begin transaction
update co_address_ext
set adr_cbsa_name_ext=cbsa_name
from co_address_ext
join co_address on adr_key_ext=adr_key
join client_afp_cbsa on LEFT(adr_post_code,5)=LEFT(zipcode,5)
where adr_country='UNITED STATES'
rollback
commit
Create trigger
ALTER trigger [dbo].[upd_cbsa]
/* Updating CBSA codes */
on [dbo].[co_address]
for insert, update
as if (@@rowcount=1)
begin
update co_address_ext
set co_address_ext.adr_cbsa_name_ext =
--Post code has matching CBSA
case when inserted.adr_country = 'UNITED STATES'
then case when left(inserted.adr_post_code,5) in
(select left(zipcode,5) from client_afp_cbsa)
then (select cbsa_name from client_afp_cbsa
where left(inserted.adr_post_code,5)=left(zipcode,5))
--Post code not in CBSA
when left(inserted.adr_post_code,5) not in
(select left(zipcode,5) from client_afp_cbsa)
then 'none'
end
else 'none'
end
from co_address_ext, inserted
left join client_afp_cbsa on left(inserted.adr_post_code,5)=left(zipcode,5)
where co_address_ext.adr_key_ext=inserted.adr_key
end
Add column to netFORUM Form
Add search/run query capability to Individual
CBSA in action
Using a Child Form in an Email Template
Gene Fowler
Senior Architect
DSK Solutions, Inc.
Advantages:
– Allows us to specify exactly what is displayed in
the email with an SQL query written in a Child
Form.
– Since it is based on a query, we are not limited to
the data elements exposed in the Object used by
the Template
– See wiki.avectra.com for more.
Tips and Tricks: Using a Child Form in an
Email template
Using a “SuperView” to make more sophisticated
and personalized email communications
Stephen Spruce
Solutions Coach
Agilutions Consulting
The Challenge – Data not the way you want it
• E-Marketing Templates are based off of a single netFORUM Object (List Type)
• Only have access to fields available in an object
• Do not have ability to “extend” Static Objects– Individual, Organization, Membership, Event Registrant
• Cannot access information from custom tables
• Cannot easily merge information from two different objects (Membership and Event Registrant)
• Formatting of fields is limited
• Some fields are not available once object has been initially added
The Goal – Get the data into a form that you can
include in a template
• Fancy display of Current Date: “Feb 28, 2011”
• Allow for date arithmetic: Current Date + 3 Months
• Conditional content based on Membership Expiration
Date
• Include a Primary and Secondary Phone Number
• Display event specific demographics
• Include Payment Information after object has been
added
The Solution – Template based on SuperView
• Create a SQL View that combines all of the relevant data
and conditional/complex business logic
• Use md_view_populate to generate table metadata
• Create an Object using the view table and joining to any
other tables of interest
• Create Tables List and Find Screens for querying
• Design E-Marketing Templates based on new SuperView
Object
Event Customizations
Jon Shoberg
Ohio State University Alumni Association
Event Customizations
• Our event registration process is pretty complex.
• Can eWeb event registration be more user friendly?
• Our service team felt users needed more prompting.
• We added the functionality of custom labels via iWeb
Created extender fields for labels
• We added six fields for custom labels
Made an editing interface
• We modified the event setup for to edit these new fields
Modified our eWeb forms
• Using toolkit we added the new extender fields
Customer View
• Our customers now see our custom fields
One more thing …
• Our complex rules need complex business processing
• Via system options you can name custom procedures
• We’ve replaced the one that calculates packages
Using Invisible SQL on Profile Forms to Control
Information
Grant Denten
Database Manager
Emergency Nurses Association
Invisible Control using SQL
• What is it?
– Makes the control “invisible” on the form when the condition is met.
• How it works
– If the test returns zero rows, then the Invisible feature is not invoked and the
element being evaluated will be drawn on the screen and displayed to the user.
– If the test returns one or more rows, then the content will be made invisible to the
user – it will not be drawn on the screen
Invisible Control using SQL
Simple Example
Invisible Control using SQL
• Our Requirements
– Use the notes table in multiple modules and show which module(s) have notes
associated with them
• Solution
– Added extender column to co_notes_ext to identify the module the note(s)
pertain to (ie. CRM, MBR, INV).
– Placed an HTML Label control on the form and used Invisible SQL to either hide
or display the label/text
Invisible Control using SQL
• How to do it
– Use an InvisibleSQL statement.
– InvisibleSQL works in the following way. In the URL/ImagePage/Data text area,
you will write a SQL command that will return a recordset with zero, one or more
rows.
– If there is one or more rows returned by the SQL command, then the control will
become invisible.
Invisible Control using SQL
Invisible Control using SQL
Invisible Control using SQL
We only want to have this
control visible when there
is a “CRM” notes record.
To do this we search for a
customer key that is NOT
in the sub-query. By doing
this, the SQL returns no
records so the control is
visible.
Thing 11
• One bonus thing: eWeb CMS Design Bookmarklet
• Allows you to easily toggle between Design and No Design on
eWeb pages.
The End
• Thank you to our ten presenters!
• Q&A