How to Create a Custom Line-Item Database - (2024)

See more videos and subscribe to the Proposal Kit YouTube channel

In this video we're going to show how to create a custom line item database from scratch. Now there may be cases where you might be better off cloning an existing database making modifications to it. Other videos will cover how to do that and other videos will cover the advanced topics.

We're going to get into just the basics of how to create your own custom database started doing that from scratch. Click the Preferences button, Data and Display and click the Line Item Quote Database button. This is the Preferences section where all the line item database modifications and creation, everything, is done.

Okay, so you'll see a drop down list. These are all the currently loaded databases. We could modify one of these if we wanted to but we're going to create our own custom database and that is done by just adding a new definition.

Click this button Add New Definition. We'll give it a title. Title just has to be a unique title that doesn't match any other titles.

You'll see a bunch of tabs across here. Some of these are more advanced features other videos will get into like drop down lists, user functions, the image database and so on. Using table settings for for defining the table layouts and so on.

We're going to get into the basics which are global fields, categories and line item fields. Think of your database definition similarly to an Excel spreadsheet where an Excel spreadsheet you can have multiple worksheets embedded within it, each is its own independent set of lines and columns. A category is like a sheet, every database definition has to have at least one category.

We have zero categories so far and we will add a new category. You'll always use codes for for all fields, categories. Everything you can create in the database definition will have a unique identifier.

We're going to do a product category and the Output To you'll see there's 60 items here. These are 60 of the thousands of templates in the Proposal Packs that are predefined to be database enabled. All of these will actually have their own database definitions and you could customize these as well.

These are called the component template databases. Now creating our own database and then assigning it to these doesn't change or alter anything about the component template databases. So we're just going to assign this to the Product Cost Summary.

When I check off any cost related page it's going to ask us if we also want to output to the Invoice. We'll say yes in this case and let's just go uncheck this original. We've set up our product category and the output is going to go to the Product Cost Summary page and the Invoice page.

Now if I wanted to have it output to a completely different template in The Proposal Pack system that's not in this list of 60. Say it's a completely custom page I created myself then we would just have to go up a level and get into the tab where we can add additional chapters into this output list. That's all customizable.

These other fields down here, these are for display purposes so we're not going to get into that here. The basics of adding a category are give it a unique identifier, give it a display name and select what chapter or chapters the tables will get output to. Now I can go into the category fields from right here or if I go out I can get into the category fields from here, multiple places.

Okay, so we can have multiple categories so say if we needed to have a services category we could add another category for that. If we have an installation schedule, timeline database we want to create where we can add a category for that. What if we want to split up our products into multiple categories so each will have its own subtotals and so on.

Well, we can just keep adding categories. Another quicker way to create multiple categories that are going to be pretty similar say if we wanted multiple product categories or add a services category and it's going to use the same line items we would create the category first, add the line items fields which we show you next. Then we could clone that category make a copy of it, saves us the time of starting from scratch with all the fields.

Okay, so now we have a category created. Let's go into category fields you see there's no fields here. We're going to add a new field.

I'm going to create description, we'll make this a string field. We're going to leave everything else alone. These fields down here are for display purposes so we're not going to show that in this video.

Requiredk, if that's checked then it's just required that the user always fills in something in the data entry. Visible, that means the column is intended to be output into the documents. We can actually set up columns that are for calculation purposes that aren't going to display in the tables.

Unique Values, that will just ensure that the user always puts in a unique value. So this is a pretty simple field, just a description and set it to a string. Now let's add our unit price, that'll be a currency and we can also set default values so it'll always start at zero.

At least that ensures if the user doesn't enter something it'll at least be set to a zero value. We're going to add a field that's not in the other stock product databases since that might be the reason we're creating custom one. We're going to add a weight field and we're going to type just pounds into the label because when we're creating say a weight field there's nothing to tell the user whether it's pounds, ounces, kilograms and so on.

You have to decide what your unit of measurement is going to be and just ensure that the data entry is always done appropriately. This will be a numeric data type and for this example we're going to add the weights because we're going to use that to help us determine what our shipping cost is going to be. Okay, now we're going to add a quantity.

For field identifiers in categories these can actually be the same as the identifier for another field in another category. So that's the only time we can actually use the same identifier codes is when they are field level codes and categories. The quantity, that's going to be a numeric.

We have our basic data entry fields. Now we need to do some calculations. All right, now we're going to add some calculations.

So add a new field and we're going to do the total weight. We're just going to call this line item weight and this is going to be numeric. We're going to uncheck the visible flag.

This is going to be for internal use and it's not going to be output to the actual Cost Summary or Invoice. Our value is going to be a calculation. All calculations start with the word CALC with a colon in uppercase, always has to be uppercase and a parentheses.

Everything's going to be bracketed. Now we're going to calculate the weight times the quantity. Just in case we've kind of maybe forgot what we made our labels we don't want to mistype anything we can just click the View Calc Fields button.

Okay I want the unit weight inserted into our CALC and I'm going to type in the keyboard a times. Go back here pick our quantity and end the calculation. Now this is our calculation for the line item weight.

Also, whenever you make a field invisible it cannot be set to be required. All right, now we're going to add one more calculation for the total amount. The cost which is going to be the unit price multiplied by the quantity.

This will be a currency and we'll start a CALC again with CALC you'll notice this button Get Calc Ideas. You can click this and do a search across all the databases that are in the system and it will find all the CALC strings for every database and give you a list of everything. We can narrow the search down too.

We kind of know we use the tag AMOUNT or even quantity QTY pretty commonly in our examples. If I just search for AMOUNT I'm going to search the all the databases for the use of the AMOUNT field. It gives me all of these so I can see how it's been used in every other database.

This just gives you idea of how calculation strings, valid calculation strings, are constructed. I'm not actually going to use any of these because I'm using my own custom fields here. We just go back to data entry so I'm going to do a CALC.

Look at the fields we've got, I'm going to pick the unit price multiplied by the quantity and there's our last calculation. Now we have all our fields that we want and we can see looking at the list the ones that are visible, the hidden one, the calculations, default values. If we were to be creating our own custom Word tables it'll tell us the codes that need to go into the tables and so on.

All right, so all of our line level fields are done for this category. Now we're going to add some global fields. Global fields are going to be fields that are used within calculations, inside categories or there're going to be calculations or static values that are used to calculate things from the categories.

For example, we're going to add a new global. This is going to be our rate and say if you need two or even three tax rates you just create multiple globals, say PST, GST, VAT, whatever you need. Now tax rate, we're going to make that a percentage and for good practices every global we're going to assign it to a specific category.

If you only have one category, it doesn't matter if all of your tables are on the same page, doesn't really matter. This is more important when you have multiple categories and different categories are going to be output to different pages within the documents and you want to make sure all the right globals get output with the right categories. Also, if you're going to have multiple categories on a page but sometimes some categories are going to be optional you're not going to fill in data for them don't assign globals to categories that might not get used just assign them to one of your categories that you know will always have line items.

I'm going to just default our tax rate global to always be 6 pt 5%, that way it's set set for every project but then on a per project basis it can always changed. Okay, so we got the tax rate, let's add a shipping amount. Okay, we got a tax rate we got a shipping amount so we need to get the subtotal of all of the amounts and weights on the line items.

How do we do that, so let's add a calculation for those. All right, subtotal is going to be a currency. We don't have to assign this because we're going to use a SUM and when we use a SUM on a single line level field it automatically associates it with the category so we don't need to.

Also, set it here. We're going to do another CALC and let's look at the fields we have available. We want to do a sum of all the amount fields and we have some built–in functions here we can use.

Let's use the SUM function. You see SUM here says CATCODE:FIELDCODE well this is the CATCODE:FIELDCODEs up here. We're just going to do a SUM of the product category amount.

Let's just get the SUM in there, just delete this out go back and add the amount and there we go. I'll finish it off with a couple parentheses and now we have the sum of all product category amount line items as a currency. So that is that global.

Now we want another global which is going to be the sum of all the weights so let's add one for that. All right, we're going to do the sum and you can always type these by hand too, you don't have to do the View Fields every time if you know everything you need to type in. We want the line item weight.

All right, and we're going to make this one not visible. All right, now we need to do a sales tax. So our sales tax is going to be the tax rate times the subtotal plus the tax rate times the shipping.

We're going to assume that they're charging tax on the shipping as well so let's add one more global for that. Now this one we will assign because it's not a SUM. Now let's do the subtotal multiplied by the tax rate and the shipping multiplied by the tax rate.

All right, so there's this calculation that is the sales tax. All right, now we need one final global field and that's going to be our grand total which is going to be shipping plus sales tax plus the subtotal. All right, so it's going to be subtotal plus sales tax plus shipping and that is all of our calculations.

All right, now we have our category, we have our category fields, we have our globals. That's pretty much a basic line item database setup. Now we want to test it.

We can't actually use a database unless it tests without any errors so once we've gone out of the edit screen where we created it we are going to click Testing then test the definition. Test Quote, all right the database definition tested successfully. So that test what it does is it adds a dummy line of data with default values and runs all calculations and does some tests on the database to make sure that say all the fields it can find all the field codes and so on.

So as long as it tests okay it's probably going to be good to go. So I'll get out of here, now you see our custom database is now in the list of available databases and let's go put this to use. So I'll go and add a new project.

Now we haven't selected any templates yet so we'll just let the line item quote system do that. This is a new project that hasn't been saved yet, just Yes. You only really need to make sure you get your project name set before you go and save the project because that sets the folder that will be created for the project.

So just make sure you at least got your project name set before you go in here and save it. Okay, so there are no chapters selected, that's fine, we have no quote selected yet and we are going to go and select our new custom database down here. Now as soon as I selected that it will have added the Product Cost Summary and Invoice chapters to our project automatically since it saw we didn't have them picked yet.

Now you can go to Manage Data, there'll be other videos that'll go into say Import Data and Export Data so you could actually export your definition to an Excel spreadsheet, do your data entry into that, import it back in. We're going to use the data entry screens instead do it normal since we are going to add a couple line items. Go to Manage Data, so it says no lines.

Okay, we're going to go add some lines and you can see here's our description, currency, our weight in pounds and our quantity. All the fields we set up for data entry in the definition along with the default values that we assigned for them. All right, so we've added three line items.

You can see it calculated out the weights and the amounts. We'll go and do the globals next. We already have the tax rate set and for shipping, that was the whole point of our weight field.

We don't know what we want to charge for shipping yet and this is going to be a simpler database example. You can get more complex if you have calculations you want to create using custom user functions based on the actual weight you can actually calculate shipping based on weights and so on by building up a more complex database definition. We're going to do it a little simpler in this example.

Since the quote is calculated out every time we make a change we can just go to View Globals. We've already calculated out the weight so now we know this is a 126 pound order. Now I can just go off and do whatever I need for my normal process of figuring out what the shipping cost is going to be for a 126 pound order.

I can come back here, go back to Global Data and let's just plug in the shipping is going to $125. We can go back into View Globals. Okay, there's our shipping added and the sales tax and grand total we added in the shipping cost now.

We are ready to actually put this to use. We'll do a Test Preview first really quick. So, you can see in this example I hadn't intended to make the weight visible.

What we could do really quick is let's just go pop in and we'll show how to edit the database definition. All right, we're going to pop into the Preferences here. We'll go and edit our database custom database, edit definition let's go into the line item fields for our product category and the unit weight field.

We didn't make that invisible. All right, make a quick modification there. This is how you'll basically iteratively go and test your database creations is you'll just go create a project and create some data, output it and then just go back and forth between that and the Preferences until you have the whole setup exactly how you need it.

I'll pop back into our project here and let's go do a Test and Preview again. Now you can see we don't see the weight field anymore. Let's say what if you wanted the weight to show up in the Invoice but not the Cost Summary that goes into the proposal itself.

Now that's easy to do, we would just edit a custom version of the Invoice, create a custom table and output weight column there but leave it out of Cost Summary. Lot of customization options. Or, we could leave the weight column out of the line items but we could add the weight as a summary field.

So it can be customized pretty much however want. All right, so we like our preview. You know, we were able to output and it looks pretty good without having to make any customizations to the output settings.

So, we're good with this. Now we'll just go create the whole document. Now we go into Pick Documents and you can see it's selected the Product Cost Summary and the Invoice and we'll just finish it off with a front cover, back cover, introduction.

Add as many pages as you need for whatever you're creating. We'll use that, now we've got six selected now. We'll build the full document.

Okay the document has been created. Let's open it up take a look at it and you can see the Invoice and our full document with the Cover Letter, front Title Page, Introduction. Got the Product Cost Summary output and our Back Page.

So that is the basics of creating your own custom line item databases. They don't have to be just for cost related pages. You could create a custom line item database for anything that you need a list of information for possibly with calculations.

So make sure to watch other videos that will go into all the other details of the features in the line item database system.