The easiest way to create modern business applications for the Cloud and Office 365
Note: This article applies to LightSwitch in Visual Studio 2012 (LightSwitch V2)
One of the more popular topics about LightSwitch has been around formatting numeric data. This is a very common requirement in business applications, which is why there have been so many requests to add this feature to LightSwitch. For those playing with LightSwitch in Visual Studio 2012, you may have seen that indeed, there is a new feature that allows you to do just this: you will be able to specify a formatting pattern for certain fields using the entity designer. Specifying this in one place will make every screen that displays that field use the same formatting pattern. This post will show what this feature can do, and how you can use it in your own applications.
Please note that this formatting feature works for all of the numeric data types, including Decimals, Doubles, Integers and Long Integers, and also Date/Time values and Guids. Formatting is not available for data types of String and Boolean. Of course, you can always format any type, including strings, using custom business logic in the Validate methods. For more information on how to do this, refer to the “Simple String Validation & Formatting” section of the Common Validation Rules in LightSwitch Business Applications article.
Let's say that I want to have a field that tracks the change in how something was ranked week-to-week. This could be how a stock is ranked, or a song on the Billboard charts, or in my case, the ranking of a football player. In other words, what I want to show is that something moved up 4 slots in the rankings. But instead of displaying “4”, I want all of my screens to show “+4”. Likewise, if it moves down in the rankings by 6 slots, then I want it to show “-6”. And finally, if it stayed the same for that week, then I want it to display “No change”. With this new feature, this is pretty easy.
For simplicity sake, we’ll create an entity called Player that represents an athlete who plays football professionally. It will have the typical fields, plus one called RankingDelta, which is an Integer. It should look something like this:
One that is done, select the RankingDelta field, and examine the Properties window. You will see a new section there called Formatting. Under there, you will see a property called Format Pattern. By default it is empty:
Now, to make it do what we want, let’s enter the following value +#;-#;”No Change” into the Format Pattern property field like so:
The format pattern above actually consists of three patterns separated by semicolons, “;”. Breaking it apart, here’s what it means:
Okay, so let’s see this in action. Add a screen, specifying the Editable Grid screen template, and choose the Players entity for the screen’s data. Press F5, and enter some data in the first row of the grid. I’ve used Tim Tebow for my example below (yes, there is a choice list for the Position field):
With the focus in the “Ranking Delta” column, enter a value of 4 and tab off; you will see “+4” displayed.
Place the focus back on this field and enter a value of -8 and tab off; you will see “-8” displayed:
And finally, returning the focus to this field, enter a 0 and tab off; you will see “No change” displayed:
So, what exactly is happening? Well, if the value is positive, the first formatting section, “+#”, says to begin formatting by outputting the plus sign “+”, followed by a number of digits depending on the value itself. If the value itself is a 4, then this will format that value as “+4”. Likewise, if the value is 22, then it will display “+22”. The pound sign, “#”, tells the formatting engine to place a digit value there. The same concept applies to the second section that handles negative values. The third section has no formatting characters; it says, output the text “No change” whenever the value is zero.
Note that the text “No change” works with or without quotes because there are no other special formatting characters for Integers in that section. To avoid any confusion, it’s a good idea to enclose static text with quotes. These quotes can either be with a single quote mark or the common double quotes. When displayed to the user, the quotes are not displayed.
Let’s return to the the formatting pattern we used above. Instead of using the “#” formatting character, I could have used a zero, “0”. In fact, if we had used a formatting pattern of “+000” –that’s a plus sign followed by three zeroes –and a value of 4 was entered, then “+004” would have been displayed. In other words, the “0” formatting character is used to define leading zeroes if the value contains less digits then what is supplied. There are a number of formatting options available, and the variations that can be employed are almost endless. If anyone has ever done some .NET development, you may find these formatting options to be quite familiar. The following table lists just a small sampling of them:
Pre-defined Percentage format that specifies 2 decimal places (see NOTE below this table)
Pre-defined Currency format
that specifies 2 decimal places
Pre-defined Hexadecimal format
‘Current Time: ’ h:m tt
8/2/2011 12:25 pm
Current Time: 12:25 PM
‘Value: ’ #,##0.00
NOTE: There is a difference in behavior between formatting a Decimal field using the pre-defined percentage format pattern “P2”, and specifying a field as the Percent business type. The business type assumes that any number you are entering is actually a percentage value. For example, if you enter a value of 10 for a field that is defined as the Percent business type, you will see 10.00% displayed. The real value under the covers for this business type is 0.1, not 10. On the other hand, if you have a field specified as a Decimal with a formatting pattern of “P2”, when you enter a value of 10, you will see 1,000.00% displayed. This happens because it doesn’t interpret the underlying value as a percentage, but as the real value of 10, and then formats it as a percentage. For more information about the Percent business type, check out the blog post, New Business Types: Percent & Web Address, that covers this topic.
With respect to formatting a Decimal field using the “C2” pre-defined currency format, and the Money business type, there is no visual difference to the user. However, the Decimal data type does not provide all of the inherent benefits of a business type, including validation. If you want these features, then I recommend you use the Money business type.
And for more specific information on all of the formatting options available for this feature, check out the documentation How to: Format Numbers and Dates.
As the table above demonstrates, dates and time values can be formatted in addition to numeric data types. There are a number of pre-defined formatting patterns that can be accessed using a single character. Here are some of the more common options, but by no means is this an exhaustive list (refer to the link documentation above for the full list):
Friday, June 1, 2012
Friday, June 1, 2012 1:45:30 PM
The Properties window for a date/time field will look just like the ones for numeric data types, but the formatting options expected are different. For example:
IMPORTANT: In order to have fields of Date or Date Time properly render their values using their assigned formatting patterns, the controls must be set to either a Label or a Text Box in the screen designer. Otherwise, the date-specific controls will ignore the formatting pattern, and user their own formats to display the date/time values.
In addition to numeric and date/time values, fields of data type Guid can be formatted as well. If you aren't familiar with a Guid (usually pronounced one of two ways: “Goo-id” or the one that rhymes with squid), it stands for Globally Unique Identifier. They are used because they consist of 32 alpha-numeric values, providing over 3x1038 possible Guids values, making them extremely difficult to duplicate. Here is the list of pre-defined formatting characters for Guids:
Guid Format Pattern
32 digits separated by hyphens:
32 digits separated by hyphens, enclosed in braces:
32 digits separated by hyphens, enclosed in parentheses:
Four hexadecimal values enclosed in braces, where the fourth value is a subset of eight hexadecimal values that is also enclosed in braces:
When assigning a format pattern for a field in the entity designer, an error message will be displayed if the pattern is invalid for that type of data type. In other words, specifying a Guid formatting pattern or a Date/Time formatting pattern for a decimal will result in an error message. For example, if you specify the pre-defined date pattern of “M” (without the quotes of course) for a field whose data type is Integer, you will see the error message:
I’ve introduced you to the new Formatting feature that is part of the next LightSwitch release. This feature is highly flexible, leveraging a wide array of formatting options which let you craft any number of formatting patterns. When you specify this in the entity designer, all the screens that display this field pick up the formatting. This is a really powerful feature, and I hope everyone enjoys discovering what they can do with it.
-Andrew Lader, Visual Studio LightSwitch Team
This is a step in the right direction!!!
Consider a different situaiton.
Consider a table that holds prices for commodities, stocks, stock indicies, bonds.
These prices are multicurrency so setting the format to money would not be correct. Some are not money because they are simply index values. To further complicate the matter. The number of significant digits to the right of the decimal is different for diffent sets of data within the table.
As you can see setting a global enitity style format would not work in this situation.
How about setting the column formatting of a grid prior to loading. This way in code we can change the formating based on the data that is being displayed.
I am at VS Pro 2012 Update 3 and I can't see to make this work.
I am storing DateTime in SQL and am using a standard format string of "g" for DateTime (short code)
In fact is doesn't matter what I put in the format string field, it is not respected. I have ensured my DateTime control is set to a text label as well.
Any ideas? Sorry to dig up an old article like this? Perhaps its a bug?
Looking for a way to pad the integer value with 3 zeros accidentally ended up discovering that it was enough to put 000 in formating pattern.