Thursday, August 28, 2008

Outlook Forms 11 - Data Verification

Issue:
Insure that all required fields have been filled out.

Quick:
Set validation formulas for fields on Validation tab of Properties page.

Visual/Learning:
The quick way to do this is to open the properties for a field, go to the Validation tab and select the check box "A value is required for this field"



The down side of doing it this way is that the error message you get is pretty vague. All I get for an error is that "A field on this form requires a value". So the poor person filling out the form has to guess which field on the form is missing required data.


A better way to do this is to use formulas to check my fields and assign error messages that are more friendly to work with. The formulas will change a bit depending on what kind of data I am checking.

To do this I need to know the name of the field I want to work with and what it is. I am going to work with the text field Payee so I right click on the field go to properties select the Value tab and I see that "Payee" is the name of the field value. Makes sense, so now I go to the Validation tab.


Here I check the box for "Validate this field before closing the form" and I put in the formula box "Asc(Payee)>0". All text is represented to the computer as a number code. The code comes from the American Standard Code for Information Interchange or ASCII for short. In the formula Asc is even a shorter version of ASCII. I look for the ASCII code for the text in the payee text box. As long as that box is not empty it should have an ASCII code of more then zero. That is why my formula tests to see if the ASCII code for the contents of the Payee field is greater then zero. Now the great thing is if it is not greater then zero I get to write my own error message. In this case I put "You must enter a name for the Payee". Now when someone tries to send this form with out filling in the Payee field they get a message that tells them exactly what needs to be fixed. This trick should work with pretty much any text field.



For radial buttons the same trick works because when I click on the button it assigns a text value to the field. First get the name of the field.


Check if the ASCII code for that field is greater then zero, and specify an error message.

The one thing of note here is that when you set validation on one radial button all the radial buttons in that group get the same setting, so you do not have to do it on each individual button.

The check amount is a number field and the default value gets set to zero. Because it does not make sense to request a check for zero dollars I will check that the value of CheckAmount is not equal to zero. <> in the formula means not equal.

That is pretty much all we need for this form because we are only checking for text and number fields in this form. But if you need to check a date field this is how I have done it in the past.

This formula is from a New Hire form I created. The default date field shows "none" when you run an Outlook form, but the computer really sees that as some date in the distant past. Now() is a function that gives me todays date. A formula like Now()<[StartDate] would check that the date entered was after today. So tomorrow, next week, or next year would be fine. A start date of today's date, yesterday or any older date would generate an error because today is not an earlier or smaller date then the start date.

In the case of the example below I am checking for Now() minus 10 days. In theory New Hire forms should be filled out before people start work, but in reality that is not always the case. This formula gave people a few days to get in the New Hire form after the employee starting working with no error, but it would still give an error if they forgot to put anything for a date.


Go To Outlook Forms 12

Go to Outlook Forms Index

18 comments:

Anonymous said...

Jimmy, I have created forms following closely to your tutorial. The only difference is that I need the requester to be able to attach files along with the request form. So I included the message control box so that attachments can be inserted. Questions: 1) How do I get the attachments to be forwarded together with the request when the manager approve the request? 2) Is there any way of hiding the msg control box since I actually don't need it except for attachment purposes? 3) I notice that after the manager click on not-approved, and enter his comments, the original request form is still open, is there any way to automatically close the original request form? It happen when the manager click approved too.

Jimmy said...

Using the forward action for the reply from should carry the attachment along. If you want to hide the message box you could just hide the page with the message box on it and move or copy all the data you want to display to another page.

Anonymous said...

Hi Jimmy, 1) I have tried the forward action for the reply form but the attachment still did not get carried along. Pls advise. 2) If I hide the page with the message box on it, the form wouldn't allow attachments anymore. Any way to work around this? 3) How abt qn 3?

Jimmy said...

1. I do not have a machine to test on today but I know that there are options for how to treat the original message when you do a forward. Play with the options there and you may solve this problem.
2. Yes if the message box is hidden you can not attach files. You my need to use a check box or button that unhides the message box using a bit of code.
3. Yes this is normal behavior for the original message to stay open when you open a reply form. Again you could probably force it closed with a bit of code. Maybe look at
http://support.microsoft.com/?kbid=193460
I am thinking to add some code examples for these kinds of things soon.
Jimmy

Anonymous said...

Jimmy,
I've followed your instruction not as closely as I can since the need of my form is a little different. I needed to creat one so people can send it to me with an attachement and just click off certain options. I do not need to reply back to them. So I think I did it all right, but when I send it to myself for testing, I see only my signature, which is on the Message tab. (I did check off not to display the message tab).
What am I doing wroing? Would you be able to help?

Jimmy said...

Sounds like you don't have the form installed in the library and it is defaulting to a standard form.

Anonymous said...

Hi Jimmy,
So installing the form to the library is different from publishing the form?

Jimmy said...

Sorry no publishing is the better word. Where have you published the form?

Anonymous said...

The form was published to Personal Library since I do not have the ability to publish it to the Organization levle

Jimmy said...

Not sure if you are not doing it this way but try going to Forms and select the form that you have published in the Personal forms library and send it to yourself and see if that works. Be sure that the tab with your fields are unhidden in the form?

Anonymous said...

I tried that.. when I chose the form to send to myself, I can see what I created and check off and uncheck any checkbox I created. Then I hit send. But what came back is just a blank message with my signature on it.
And I checked the messages in Sent folder and it's also a blank one with my signature on it.

Jimmy said...

Do you have another machine that you can run the form on? If so try that. Also try to create a new form with just one field on it and see if that works. That should help to narrow down the problem area.

Anonymous said...

Jimmy,
Apparently, if I actually open up the message, I will be able to view the form the way it should be. It just won't display the form when I have the reading pane out... but that's fine...
Now my problme is printing the form as is, not with a list and values...
Can you help?

Jimmy said...

Yea forms will not display correctly in the preview pane. Printing is more of a problem. If your form is only 1 page you can open the form and use Alt-PrintScreen to print just the message window. Antoher option is to create a reply from that has the fields arranged in a larger text box. There are some other ideas here http://www.outlookcode.com/article.aspx?ID=24

Anonymous said...

There are other parts to this form...but what I'm having trouble with is - There are three options in one section, with radial buttons. If a particular one of the radial buttons is selected, I would like to require that another date field be completed. How might I do that? (Options are Dispose, Hold, and See Instructions. If the person selects "Hold," they need to enter a date to hold until.) Any help would be amazing!! thanks :o)

Jimmy said...

You will need to use a little code. You can add an event handler for CustomPropertyChange. This will get checked each time a field is updated. You can then use this to trigger some code to unhide a date box. You would then need to set the initial date to something that checked out as "not valid" so that they would be required to update that box.

Michelle said...

Can someone help with attachments? I need them to attach receipts to the form email.

Thanks!

Chen Alan said...

Useful info...
Thanks for the posting.