Include a DBXL Link to a Completed Form in Email - Hilary Stoupa

InfoPath Dev

Hilary Stoupa

Include a DBXL Link to a Completed Form in Email

It is always nice to alert people via email that there is a form they should look at -- maybe you are using some built in InfoPath capabilities for your work flow rather than having to build one in SharePoint, maybe you just want to make sure that the right people have the right information at the right times -- let's face it... modern humans use their email in-boxes as their task scheduler and to-do list, and if you want people to go look at data, you have to make it as painless as possible.

Adding a link to a filled out form to an email can be a great way to facilitate people actually opening and reading filled out forms. With DBXL, we have the option of shredding the link to the form to the same data table as the form data... which means we could use QueryDB to get that link back!

Here's the sky-high view of what we are going to do -- using submit rules, we'll submit our form, query a data source to get the link of our form back, display that link in the form, and email the form to the person we think should care about it, with the link to the form in the email, so that if there is action they need to take, they can open the form with a single click.

Set up Database and Mapping

For the purpose of this blog post, I'm going to assume you've already got a DocType set up in DBXL that has a data connection that uses the SubmitDocument method of the DBXLDocumentService, and you have database mapping set up. The reason I'm going to make that assumption is we already have lots of documentation on how to do that and I don't feel like writing more, just to cover this one technique. I'm sorry if that is disappointing, but we can't start from scratch on every blog post (i.e., I've also assumed you can design a simple form and create a simple SQL table...) 

When mapping to a database in DBXL, we can map form data to the table, or we can map DBXL Mapping Tokens, like DocId, DocType or the Link to the document:
Data source

For the document you'd like to email a link for, add a column to your SQL table for the link, and add mapping:

Save your changes.

Set up Data Connections

Open your form in design mode. Under Tools, select Data Connections. The Data Connections dialog opens. You'll see any existing data connections for the form. Add a new Submit data connection, and submit as an email:
Data Connections with Email submit

While you are testing, you may want to set the 'To' field to your own email address.

Add another data connection -- this time it will be a recieve data connection. Using the QueryDB web service's  GetColumnsXMLStrQuery method, query the same table your form shreds to. Don't set this data connection to get data on load -- we'll want to tweak the query xml in our submit rules later so we get only the data we need (after all, we have this power to get filtered data, let's harness it!).

My form now has three data connections -- one for my DBXL submit, one for my Email submit, and one to get my link back from the table my form data shreds to:
Data connections

Add Conditional Formatting

Since we are form designers, we are probably picky about aesthetics. We don't want to clutter up our canvas with stuff the user doesn't need. And, if the user has the form open, they don't need the link. However, it is nicest to put the link at the top of the form, so that it shows up at the top of the email (no scrolling for the lazy recipient - just click and open).

A simple way to do this is with a boolean field. I'm calling mine IsEmailing. Set the default to false. Now, at the tippy top of your form, add an expression box:
Expression box

Set the value to either just the column in your secondary data source connection you set up earlier to get your link back from your database table....Or do something fancy, like use concat to add some additional verbiage:
Expression box formula

Conditionally format the expression box to hide if IsEmailing is FALSE:
Conditional Formatting

Set up Form Submit Rules

If you want to get fancy and also be able to close the form after all these rules, you probably want to create your own button, label it submit, and use ordinary button rules, so you have the option to select 'Close the Form' as an action. Otherwise, you can use submit rules.  

Under Tools, select Submit Options:
Submit Options

Select Perform custom action using Rules:
Submit with rules

and click the Rules button. The Rules for Submitting Forms dialog opens:
Rules for submitting

Select Add:
Add Rule

The Rule dialog opens. Give your rule a meaningful name (if not for yourself for future you, who will thank current you in 10 months when you open this thing to make a change and all your rules are called Rule 1 and future you calls current [actually, at that point, it will be current you and past you] you a bad name) and select Add Action. Make the first rule action to submit the form to DBXL, using the data connection in your form that currently does that:
Submit to DBXL

Add Action and set the queryXml field for the GetLink dataconnection to get back just your form link.... now, this may take a little trickery. You have to know what form you just submitted. In general, if you are shredding a DateTime from the form you have a pretty good chance of using that for a match. Keep in mind though, you can filter on as many fields as you are shredding... so you could match on Name, Date and Description (or whatever your data looks like):
Rule for dynamic query

AddAction and query with your data source that uses QueryDB to return data from your table.

AddAction and set IsEmailing to true(), using the formula editor to select the true() function.

AddAction and submit with the email data connection.

AddAction and set IsEmailing to false(), using the formula editor to select the false() function. In the end, you'll have rule actions a lot like these:
Full Submit Rules

Test the Form

Save your changes and publish the template to DAT. Open the form, fill it out and submit it. Check your email....
Email with link




Mel Clemente said:

Easily add a template part that  validates email addresses using Active Directory:

February 4, 2010 2:48 AM

Catharine said:

Hi Hilary -

I am missing something.  

I have the docid and have calculated the link.  

If I include the field, (which is formatted as a hyperlink) containing the calculated link, on the form, and send an email, it is shown in the email an active link (but I don't have any opportunity for conditional formatting).

If I use an expression box and reference the field which contains the link,  the email just shows text (no clickable link).  I don't have the ability to format the expression box as a hyperlink, but I do have the option for conditional formatting.

July 8, 2010 3:17 AM

Hilary Stoupa said:

Hi, Catharine!

Put your hyperlink in a section.... then you can conditionally format the section (like we always have to do with certain other pesky controls)....

July 9, 2010 3:35 PM

About Hilary Stoupa

I wandered into development after working as a business process analyst for a global manufacturing company. I create InfoPath solutions for our clients as well as work as a developer on company tools that extend InfoPath. I've also been instrumental in creating the InfoPath Master Class training provided by Qdabra.

Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.