How to use Data Types in Line Item Databases - (2024)

See more videos and subscribe to the Proposal Kit YouTube channel

In this video we're going to go over the data types available in the line item quoting database system. This is going to be something you're going to set up when you are customizing or creating your own custom databases. The data types already are preset in all the pre–made databases.

Just go into the Data and Display then Line Item Quote Database and we'll just open one of our pre–made databases. Go to Edit Definition and the data types are the same with one exception between global fields and line item fields. Line item fields we have one extra data type which is called the Line data type.

There's one difference between how one data type works between globals and line items which is the Increment data type and we'll get into that when we get to the explanations of all of them. I'll just go into the line item fields. We'll just go through and we can see in this database we already have a String, a Currency, a Numeric, a Boolean data type set up in these fields.

We'll just add a new field here just to go over all the available data types and some of the caveats with them. When you create or edit a global or line item field you've got the data type and you'll see all the different data types you can assign. So String, that's fairly simple.

It's just your straight up numbers, symbols, letters, just a string. There is one limit here where your string size can only be 255 characters long. For an Integer data type, this is just whole numbers so a 10, 0, 5.

Just whole numbers with no decimals. A Numeric data type, that is a number with zero or more decimal points. A Currency data type, now this is actually stored as a numeric and it's just for dollar amounts and it's stored internally as just a straight up numeric.

A positive, negative, number or zero with two decimal places. It's the formatting, like if we select currency the formatting of how it looks in the database is going to be defined by the data type. A Currency data type will always by default display with the formatting of your default currency type that you have selected for your project.

That's going to default to US dollars so it'll be a dollar sign, your amount, a period and two decimal points. You can alter that with the formatting, so say if you want your currency to have parentheses around it for a negative number you would use a formatting string like these. Say if we wanted the word Zero output into the tables when you have a zero amount you can specify that here.

You can have a slightly different formatting for positive, negative and zero amounts. This is also how you set up foreign currency codes. There'll be other videos that get into formatting, so formatting is kind of a separate thing than the data type.

However, the data type will have a default formatting which is your system currency. For a Percentage, this is stored as a numeric. A positive, negative number or a zero with zero or more decimal points.

The display is going to usually default to a couple decimal points followed by a parentheses symbol. This is stored internally in the database. So say if we're going to have a 6% tax rate we're going to store it internally as 06 but it will be displayed based on the default formatting is say 6%.

And again, like currency, we use formatting strings to alter the display of the data type. In Alphanumeric, that's just numbers, letters, spaces. That's good for say product codes.

It just limits you to not using symbols. Now the Boolean data type, that's stored as either a 0 or a 1. You can use it in calculations.

For example, using a Boolean data type is one way to set up line item taxes. We would add a field to our category called Is It Taxable and set the data type to Boolean. Now we can use the display so instead of always being 0 or 1 we can use the display formatting to show as options for True/False, Yes/No, On/Off, or 1/0.

Then in this case if we were going to use a Boolean as a calculation for Is It Taxable we would just create a calculation, multiply the is taxable field which is 0 or 1 times the tax rate and the quantity and the amount and get our line item level taxes. Now the Increment data type, this is probably the most complicated data type and it's slightly different depending on whether you use the Increment on a global field or a line item field. If you set an Increment data type on a global field it will increment across each project.

Say if we created five different proposals you're going to increment that field automatically for every new project. This might be a good way to say create a invoice number or a work order number where you wanted to autoincrement for every project. When you use an Increment on a line item field you will have a starting value and that starting value can be a number, it can be a field.

It could be the current date, could just be the number zero. That will be set to the first line item you create. Then you'll have an increment value.

That increment value could be a number or it could be another field. So this is how we create running subtotals, project schedules where we increment the next date of a task based on the previous rows date and increment by the days till the next task. Since the Increment data type is fairly complicated there will be some example databases already set up where you can see how to do a decrementing field for a balance sheet, an incrementing running subtotal for an amount and an incrementing start date for projects.

I'll show that real quick when we're done going through the list. You have a Date/Time data type and again the formatting of whether you're going to display it as year/month/day as numbers or spelled out as month day year or just the day and month that is the formatting that you apply. So you have lots of different options, these are just some default ones and you just apply the formatting to the Date/Time and that's how it will actually be output into the into the documents.

Now you can do some calculations on Date/Times as well. I'll get into showing you some of the examples where you can increment a Date/Time by seconds, minutes, hours, days, weeks, months, quarters or years. That's done by creating a second field which would be a numeric field that's set to the increment type which would be seconds, months, days, years and so on.

I'll show you an example of that later and you can also do some other calculations on dates and you can do some other basic date calculations. Basically to subtract a date from another to get the difference. Now your Dropdown List data type, this allows you to create a drop down list of items with display values that'll be in a drop down list.

The underlying data that is operated on will be numeric so this is how you can create a list of say sales people. You can create lists of product codes, whatever you need. To use dropdown lists you'll have to go out and actually create the dropdown list first.

How you do that is you would go out to the Lists tab. There'll be another video on how to manage lists but we would basically go into this. We would add a list, we would set up our list of display values and underlying data values and then when we select our dropdown list we would have a another dropdown over here which would let us set up that list.

Then when you're creating your projects instead of of a free form text field to enter your data into you just have a dropdown menu of those items. Because a drop down list the internal data is stored as numerics you can use it as a calculation so your user can select a text value from a drop down list and then you could use it in a calculation since each item in the drop down list will have a different number. Now your URL data type, URLs have two parts, you have a display value and the underlying data value.

For example, we could say something like this where when this information is output to the documents you will see the display value with a hyperlink and then if you click the hyperlink it's going to go out to the underlying data value. Your values, these can be valid URLs or mailto addresses. For Image data types, images are kind of like dropdown lists and have to be set up externally first.

We would go out and set up the image database. I'll show you that in a second after this last data type. The Line Number, the Line Number is only used in the field level and that's just a simple incremental number from 1 to X which is the line number of the line item.

If you have five line items they're numbered 1, 2, 3, 4, 5. So this data type is something you might use if you want in your output to have like the number 1 through X as your first column just for display purposes. Okay, now for the image database.

If you're going to use the Image data type you have to set up an image database. You would go back out to the main list of tabs and go into the Images and you would set up your image database. An image is going to have the underlying file out on your hard drive and a display name because you're going to be selecting your images from a list and so you can have a nice visual display of the names of your files to pick from.

Then when that document gets created the actual picture will get pulled in from the file and get inserted into the document. You can set up your image database to be global across your entire database or you can have images specific to each category so you could have a list of product images you could have a list of employee images for resume pages and so on. So that is an overall description of all of the data types.

You can see examples up here, go to the main screen for the line item database, click the Premade Databases tab and go into the Example DBs. You can see lots of examples of using these various data types. Here's an image and a URL example.

You can just look at a results PDF. These are just some examples of how images would get inserted into a table. An example of the Increment data type, we're doing a running subtotal.

You see the value 10 and then the next one added to it, the next one's added to it and so on. You can see examples of how to concatenate strings. Here's a URL example where we can have the display type and then you see the underlying URL that's clickable.

So that's pretty much all you need to know about the data types. The data type will define how it's stored internally, couple of the caveats of how it's used and then how it's displayed by default. Then you'll use formatting strings to customize the formatting.

So make sure to watch that video on formatting options for various data types.