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:
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:
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:
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:
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:
Conditionally format the expression box to hide if IsEmailing is FALSE:
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:
Select Perform custom action using Rules:
and click the Rules button. The Rules for Submitting Forms dialog opens:
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:
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):
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:
Test the Form
Save your changes and publish the template to DAT. Open the form, fill it out and submit it. Check your email....