How to Customize an Existing 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 customize an existing line item database. This will come in handy if you want to add some fields, change calculations, change the output settings from a currently existing database. It doesn't matter if it's a database we pre–created or if it's one you've already created in the past, if it's one of our sample databases, and so on.

You can edit any database. So we're going to go into the Preferences and find a database we want edit. Okay, so we're in the Preferences Line Item Quote screen.

We're actually going to start by cloning an existing database. Now you can edit any database as is, but just to keep our stock databases pristine and unedited it might be better to just clone one, make a copy, and then edit your copy. That way you can always revert back to the original.

So we're going to select this Basic Product Sales database and we can look at the definition info. We see it has a category, it has price, description, quantity, amount fields. It has globals for the tax rate, shipping, discount, sales tax and total amount.

We want to edit this to add in weights into the line items and then sum up the weights and use that to help inform our shipping costs. So we'll first go ahead and clone this Basic Product Sales database. Click the Clone button and let it clone to a copy.

It's added the copy down to the end of the list. Whatever database we have picked in the dropdown list, that's what's going to be operated on during all of our editing. Another reason to clone this first before we start doing anything is let's say you've made use of a database.

You've added line items to a project and then you decide you want to go and edit that database, but instead of editing you wanted to clone it. You will lose your line item database entries for that project unless you go through some extra hoops of exporting it to an Excel spreadsheet, save that updated spreadsheet with a new fields you add, then reimport your data back in. It's easier just to clone a database before you start using one in all your projects.

All right, so now that we've cloned it let's go and make some edits. Just click the Edit Definition button and you can see all of the existing line item fields, the globals, the categories, the line item fields and so on. We're going to go and edit our category and we're going to keep the category information the same.

Just go into fields. All right, so this is where we want to start adding some weights. So we already have an item price and an amount calculation so we're going to add a weight for the item weights and then a weight calculation then later we'll add a global total weight that's going to sum up all of the individual line item weights.

We're going to add a new field, call it Item Weight make this a numeric. We'll default it to zero. We're going to make this an invisible field and we also want to turn off the required setting because we're going to use these weights behind the scenes just to help inform our shipping calculations.

But we don't want the weights to be added to the Cost Summary. And now we'll add a calculation which will be the quantity times the item weight and instead of adding a new field let's clone an existing one. We already have a calculation based on the quantity we're going to add another calculation based on the quantity so let's clone the amount calculation and that'll be the line item weight.

We're making up these identifiers so just make up a unique identifier that means something to you. All right, so we've cloned the line item weight. Let's edit that and now instead of item price it's going to be the line item weight times the quantity.

Just in case you forgot what did I call that field let's just View Calc Fields. What did I call that, I called that item weight. All right, so now we have our item weight and this is not a currency.

Whenever I change the data type it actually will wipe out the value field. So I'm going to copy this, we're going to change the item weight calculation to a numeric then paste back in our calculation. We're also going to make this invisible and whenever we do invisible we also uncheck the required box.

That is going to be our calculation for the weight. If we scroll over we can see these are both tagged as hidden fields in the output. Now note that these visible versus hidden, that only come comes into play when the Wizard is creating the output into the documents, you know, when it creates the tables from scratch.

Now if you are using custom tables already set up in a template in the MyTemplates folder this doesn't apply because those kinds of tables are set up with these tags, and it's whatever tags you put into the table get output. So that gives you more flexibility where we might want to have the Cost Summary not output the weights but in the Invoice template maybe we want the weights output. So that will come into play when we are doing the design of the table layouts and there'll be other videos that go into how to set up your tables.

All right, so we have our new fields added, we've edited the line item category now let's go and add total for a global. All right, so we're going to add a global, call that total weight. This will be our label and this is going to be a numeric.

All right, so our calculations always start with CALC: and this is going to be a sum of all the line item weights. If we don't remember what our functions are, okay we're using the SUM function we'll insert that in and then our code field. Code is going to be the item weight then we'll have to add back in parenthesis.

So now we have a total weight global variable that is the sum of all line item weights and we are also marking this one is not visible. All right, so now we've edited a database by adding some category line item fields and a global field. Let's go and test the database definition edits.

All right, so that tested without any problems. If there were problems, if we mistyped the calculation, misspelled something, if it couldn't find a field this comprehensive report is going to tell us what we need to go fix. Now let's go put this customized database to use.

We haven't selected any chapters for this project yet and we have don't have a database selected. Now we're going to go add our customized product database and we will add some line items right, we've added some line items. You can see it's calculated out the weights.

We've got three line items in there let's add some global data, our tax rate. Let's just say that's 6%. Give maybe a little bit of a discount, give a $100 discount.

We don't know our shipping yet. Now if we look at the globals we can see it did calculate out the total weight of the entire order in pounds which is 680. Now we'll go and do whatever we need to do our normal process of figuring out what the shipping cost on that large of an order is going to be.

Probably going to be some heavy freight. All right, let's just say this is going to be $1,000 to ship. Now I can go back look at the globals, got the tax rate, shipping, discount, sales tax and the total amount on the order.

Let's just go ahead and build a full document. Add a Cover Letter, Front and Back Cover Page, Introduction, got the Cost Summary. All right, we've created our document, we'll open it up take a look at it.

You can see we've generated an invoice and a complete document with the front cover, cover letter, cover design, intro, a cost summary and so on. That really is all there is to editing databases. It should be a best practice to clone an existing database unless it's one you've already been working on just to keep the stock databases pristine in case you needed to use them in the future for other things.

You can basically go in and make changes to all the databases this way. That is all there is to editing databases.