It has been awhile since I posted anything new. Ok, it has been nearly a half year. Perhaps as my new year’s resolution, I will try to get back on a regular basis here. It may not be twice a week like before, but I’ll try to do something weekly.
I’ll open the new year with a short tip. Suppose I wanted to create a list which had Month as one of the fields and I wanted to default that field to the current month. Obviously I cannot simple enter the name of the month as the default value, but I notice that the Default property has the option of defining a calculated value. So I begin by creating a new field named Month2 and declare it to have the type: Choice. Why Choice? To eliminate the chance of misspelling the month name should the user need to enter a month name other than the current default month.
After choosing the data type for the field, the property settings for the column appear on the second half of the screen as shown in the next figure. Note that I entered all of the month names in the list box containing possible values. I also chose to use a drop-down box with no fill-in values. After all, the month name must be one of the values in the system. However, the important property is the default property which appears close to the bottom of the list. Notice that I chose not to allow fill-in values.
The calculation of the default value looks a lot like an Excel expression. It uses the special variable: TODAY which returns the current day’s date. Next it uses the TEXT() function which has two parameters. The first parameter is the date for which I want the month returned. In this case, I can use the special variable TODAY. The second parameter identifies how to format the date. In this case, the string “MMMM” will return the full name of the month. (Note: “MMM” returns the 3 character month abbreviation, “MM” returns the month number with a leading zero such as ’01’, ’02’, etc.)
After completing all the field properties and clicking the OK button, I can open the list and add a new record. As you can see in the figure below, the form opens with a default value in Month2 (screen images were captured back in July 2014). Note that the default value, although calculated, cannot depend on the values from another column since at the time the form is instantiated, none of the other fields for the current record have been entered.
The value on the form is a true default as you can see when you open the dropdown for Month2 which shows the month of July as being selected.
Can you add a dropdown for the day of the week as well? Sure, just change the formula to:
= TEXT(Today, “DDDD”)
You might even want to a field to default to the current year, last year, or perhaps next year. Hope this little tip on how to work with calculated defaults for date fields helps.
C’ya next time.