How to Apply Custom Data Formatting - (2024)

See more videos and subscribe to the Proposal Kit YouTube channel

In this video we're going to show how to use the data formatting options. Now data formatting, that is for when you want to change the raw data in your database and apply some kind of formatting to it for how it gets output into the documents. A couple quick examples would be say if you want your Boolean values instead of to be 0 and 1 to say True/False, Yes/No, On/Off or so on.

Or, if you want to change your date outputs from you know ##/##/## to the actual date name or month name you know January 2nd and leave off the the year. Or for changing currencies you know to not show decimal places or to change the currency code. That is all set up in the database definitions and your Preferences.

These formatting strings you can apply them globally or at the individual global and field line item level. The formatting is always database specific for global and line item fields or you can have a global format that applies across all database definitions all fields. That is in this formatting tab Global Data Formats.

Only set the formatting here when you want it to apply to absolutely everything everywhere. You'll see one entry for every database data type so you can just page through. You got Boolean, Currency, Percentage, Numerics and so on.

I'm not going to set anything in the global settings but you can just get some ideas of options you can select from instead of typing them in. Say if we always want Booleans to say True/False across everything everywhere we just set that. I'm just going to leave that off for now.

Same thing for everything else. Currency, you can just get some general ideas of a few currency strings and we'll show this later too but for currencies and numerics you can have three different types of formatting all kind of combined together in one string. So you have what do you want the output to look like for a positive number, what do you want the output to look like for a negative number and what do you want the output to look like for a zero value.

We'll show examples of this in the other screens but this is just the global screen. Okay, so that's the global screen. Now in the line item databases I'm going to clone a database Just take the Basic Product Sales database, clone a copy, make a custom database.

We usually just clone the stock databases if we're going to make modifications to them just to keep our stock databases pristine. Now you'll also want to look at the Wizard's line item database manual. It should be on your desktop with a little icon that says Wizard Line Item Database Manual or you can just open it from this Line Item Manual Button.

You'll want to scroll down, we've already got it set here you'll want to scroll down to the chapter How to Format Data for Output. This will basically give you a description of what we're covering in the video but there's a link down here for a page on Microsoft's website. Now all these formatting fields are actual Microsoft Office formats.

Anything you you type into our formatting fields you can apply to what you see here on Microsoft's description of their format functions because our Wizard actually calls Microsoft's formatting functions using their formatting strings. So, for example dates and your date time data types can make use of these date symbols. So say if you want your output to say January 20th well we would do mmmm d because that will change our date times to be the full month name followed by the number of the day.

The same thing with time symbols. We can set formatting strings for times and this page will have some descriptions of what I mentioned earlier how to set a formatting string for a positive value, a negative value and a zero value for numerics and currencies. You can even use these words Short Date, Long Date as your formatting strings.

You can use for your Boolean data types in the Wizard you can use these strings Yes/No, True/False and we have these already predefined to make it easier for you in the Wizard. Special case characters that you can use within formatting strings. Anything you want to Google online about how to use Microsoft's formatting strings, anything you find will be applicable to what we're showing you in the Wizard.

All righty, so let's go and edit our database definition go into the global fields and let's pick a total amount. You'll see the format now if nothing's in the format the Wizard will just use its default formatting strings. So for a currency data type it'll be dollar sign followed by the number in a two decimal places.

Or if you've selected an international currency in your project it will use the international currency code say like a Euro symbol or something now if you override that. Now I can click this drop down to get some suggestions. And say if I click this that will output any positive numbers in this format a dollar sign a comma two decimal places.

If I have a negative value it will instead of a negative sign it is going to display the value with parentheses. And if you have a zero value it's going to actually output the word Zero into our into our document. We can use international currency codes say the the Euro symbol.

I can use the word Currency and that's one of Microsoft's formatting values just to do a default currency. I can select a lot of international currency codes so you have a whole range of international options. And again these are just examples you know these are just examples you can use.

You can use any of these and just retype you know the the formatting string. Now you'll see slashes now slashes are used as escape character characters because there are quite a few individual characters that are special case modifiers. So if you put a slash in front of all those that just ensures that Microsoft isn't going to interpret a single character as a code and then your output doesn't look correct.

So, you know when in doubt just put escape characters. I think there's maybe five, six, ten actual characters that are special use that need to be escaped so you don't have to escape everything but it doesn't hurt and it takes any doubt out. I can use the Get Suggestions button.

Now when I click the Get Suggestions button it's actually going to search every database in the system. It's going to search all of our examples, all of our sample proposals, any custom databases you've created and it's going to pull out every formatting string it finds for the currency data type anywhere. So you can see a list of actual formatting strings that have been used.

So what's this one here No Charge. So what this would do is you will use a standard currency for a positive value, no change for a negative so we have two semicolons with nothing between them. That means don't do anything special for a negative number.

It's just going to have a negative sign in front of it but for a zero value it's going to put the words No Charge. So you know maybe you have a type of line item database for your product sales or service sales where if a value is zero you don't want the number 0 in the output you actually want the word No Charge. This is is how you can do something like that.

So actually let's just let's just leave that default in. Well, okay let's cancel out of that. Now let's go into our category fields and for line item weight this is the calculated weight on an order.

So there's no formatting and it's just a straight up numeric. So what if we want to do something like all of our weights are always going to be in pounds and we want the actual letters lbs for pounds. We're going to use the escape so this would cause pounds to be lbs to be output and we'll show this in use.

And up to two decimal places, let's just go one decimal place on the calculated line item amount. Now as we scroll up and down all of our fields you can actually see custom formatting. So you can have a quick glance at all your custom formatting and for the amount let's do NC for no charge.

Let's go do a test database output and you can see this in use. All right, we got our custom database selected go and add some line items and remember our calculated weight is going to have the custom formatting. The individual line item weights are actually hidden so our formatting doesn't really matter and we're not going to change anything on this one.

Okay, so and you can see the actual formatting gets applied on the screen here as well so you can see our lbs and for our zero amount column here because we charging zero dollar on this line item it comes out as NC for no charge. Let's do a quick Test and Preview. All right, so you can see NC output here and the weights actually aren't being output because those are hidden fields.

So, actually we can go back and add those. So we can see those in the output. All right, so our line item weight, yeah we can see that's a hidden field.

So let's just edit that and make that a visible field. Real quick we'll go over some other data types so I'm going to add a global field. Now for every data type you can have different kinds of formatting.

So a string, normally a string that's not as common to have custom formatting. Integers, numerics, currencies all kind of work the same way. Anything that's numeric can use that three–part positive, negative, zero type formatting.

Percentages, usually you're formatting on a percentage is going to kind of change how many decimal places you want to see. Alpha numerics, same thing it's treated like a string. Booleans, your common formatting is going to be either True\False, Yes\No, On\Off, or 1\0.

An Increment data type, now that's just a numeric so if you're using it for rolling date times or rolling cost rollups. You might set a custom format for a date or a currency. Date\Time gets a little more complicated.

You can have lots of different types of formatting on date times. You can actually put in these words. So remember these are the same words I showed you in that Microsoft web page or custom formatting rules.

Say if we want our date to only show the year well what if we want it to say January 20th that's where we would use mmmmm d, four m's and a d. So this is how you apply formatting rules to a Date\Time field. DropDown lists, those really aren't going to have formatting.

You're URLs, that's not going to use formatting. An Image data type we don't have any images in the database yet but you have an image data type. The only formatting you can put in here it's not Microsoft related like that web page we showed you but you could put in say a number.

Now if you have an Image data type and you use a number for the format this is going to be a scaling factor where you say I want the image to be 70% of the size of the width of the cell dropping that image into. So that's the only time you'd use the formatting on images. So that's basic rundown of all the formatting types and I'm just going to add a Date\Time here to show that since that's a little more complicated of a formatting string you can apply.

We'll do the mmmm d. All righty, so let's go redo our project. Our line items are the same.

When we made this final weight visible that only has an effect when we create the documents. But now you'll see we have a Date\Time in the globals, our test date. Now note I'm typing it in this format but you'll see the formatted output that mmmm d is going to output as November 16th.

So I'll do a Test and Preview. All right, now in our preview we've made the weight visible and you see lbs added. That's our formatting NC (no charge) for zero values and our date output it in that mmmm d format.

So that's a basic run down of how to use custom formatting strings across all the data types and how it's going to change your raw data into a formatted value that gets output into the documents. You'll see it in the data entry screens. So make use of that link in the manual to go see how you can use Microsoft formatting strings if you have further questions.