This video illustrates how to create and use dropdown lists in the line item database. Dropdown lists are used to constrain the users input to a list of specific values. This video also shows various ways to speed up list creation using Excel spreadsheets and premade lists of common things such as states, countries, etc.
Read this related article: 9 Steps to Accurate Estimates and Proposals
Watch this related video: How to use Data Types in Line Item Databases
In this video we're going to show how the DropDown lists work in the line item database. We'll go into the Preferences and we'll set up a couple DropDown list fields. I'll just take the Basic Product Sales database.
I'm going to clone a copy and now we're going to edit the definition. For lists we want to go to this Lists tab and set up a list first. Then once the list is set up then we can add global or line item fields using the DropDown data type.
All right, so we'll go into the Lists tab and we're going to add a new list and give it a unique identifier. We're going to create a list of salespeople and our default value we'll just make it zero. We'll start adding items.
The values have to be numbers and they have to be unique. One advantage of this is that you can use your Drop Down list fields in calculations and the calculations will be performed on these values, not the display values. All right, so we'll just add an item.
Every time you add an item it shows up in this item here. All right, so we've added three items. We can view the list we'll see the values and their displays.
We can do a test like when you select one of these items it tells you what the value return will be and this is the value that will be used in the calculations if you use your fields and calculations. There's another video on how to do that. There's an example where we use the salesperson drop–down list and then use these values in a custom function to calculate say your salesperson's commission rate.
Okay, so we can move items up and down in this list and change the order. We can delete items, we can go and update items and so on. There's another button up here Import List Items we're going to show that in a second for a different list.
This is showing the the basic setup of just hand entering all your list items which that works fine for a small list. Okay, so we have one list here now let's add a second list and we'll just call this a State list. Now what if we going to add a list of common things that's a pretty long list that's going to take a little while to hand enter a whole list of like say all 50 states or 100 or more countries or so on.
Click the Import List Items button here. Now we can import from an external spreadsheet. If you have taken your existing database definition exported it to a spreadsheet there's a feature there where you can import a list from that.
But we also have this pre–made list which is an actual Excel spreadsheet so if you highlight pre–made and open the spreadsheet you can see we've already pre–made an Excel spreadsheet that has all the states, all the states plus military and territories, states by name. Same thing, states territories military locations by name, countries, countries by code and value. You can import ethnicities and so on, given titles, genders, months.
You can actually add your own tabs in here since this is an Excel spreadsheet. You just click the plus button, add a new sheet and then just following the same convention here where this will be the name of the list in the drop down, this will be a description, this will be the default display value and then number rows four and higher will be your items. Now if you don't specify another column here for the values, the numerical values, it'll just start with zero for this default and then one and higher for these.
You can see over here on the countries we actually have a first column which is numerical and these are all unique numbers which are actual country codes and then the names. If you import this country list your values and display values will be here. You could customize a spreadsheet.
What if you wanted to to use these two and three character ISO codes. You just copy these columns over here and so on. If you want to make your own list like say you saw how we hand entered salespeople.
You could actually hit the plus here, add a new sheet, make your own sheet call it salespeople and put in a whole list of salespeople and instead of hand entering them you'll just import them from the list. All right, so just make a note here this is the title that you'll see in the dropdown that I'm going to show you next and then the notes and then the items. So the Wizard is actually when I click Import from Premade Lists you can see it actually just opened in the background and interrogated the whole spreadsheet and pulled out all of the sheets and their names.
If I click any one of these it shows that note that was in row two. Since we're doing states I'm just going to do the first US state codes. Click the import button, it just loaded all 50 items.
Close this and now I can see I have a list loaded with all 50 state codes and it loaded in zero and the two dashes as the default display. Another trick here if say if you want the default or any item to show as an empty string because we have to actually do data entry on all these fields you can use the word BLANK like this. During the output of your data into your documents when the Wizard sees the uppercase word BLANK as a data item it will just output an empty string into the document.
All right, so that is how we can hand enter a list or we can import a list from a custom spreadsheet. Now we have two lists created. The lists are created we can go and add them as fields.
We can enter lists as line item fields into categories or global fields. Both of these are going to be more suited for global fields. Let's go in and add a couple global fields.
We'll call this one SALESPERSON. And the data type we're going to click DropDown. As soon as we change this to DropDown it gives us which list.
So we're going to pick the Salesperson list and if we could select a default value so it's always going to default every project to a specific salesperson or just leave it empty. All right, so that's all we need to do to create a list data type. We'll add another one here for the state, make this a drop down list.
This will be the state. Again we'll just leave the the value blank and okay so now we have two drop down global fields in our database. Now I'll just do a quick test on our database.
Every time we make changes to the database definition we want to run a test and make sure it doesn't find any errors. That'll look good, so now we can put these drop down lists into use in a project. We're going to use our List Demo database.
Let's go set up the global data first. Say we have a 6% tax rate. This order might have a $10 shipping.
You would probably figure out the shipping later after you actually add the line items and say get the weight of the order and so on but I'm just filling these all in ahead of time for now just so we can see how the lists work. All right, so you can see with the salesperson list here we can just pick whoever the salesperson is for the order for the proposal and we can pick the state. Now normally we're going to be using this in conjunction probably with the the rest of the shipping address and so on.
This is just to illustrate the use of the list itself, not in the full context of the rest the whole address you'd be entering in. All right, so now let's go add a line item. We've got couple line items added.
This might be where we go and figure out your shipping after you've added your line items you've got the weight figured out, you know, how heavy the order is going to be. Maybe have a different process for going and figuring out your shipping cost based on say the the weight. There's other videos that'll go into how this all can be set up more customized.
You can see our two list items that we had selected and we'll just do a Test and Preview. The way we're using these lists here in practice we would actually go and probably modify the template to put these particular kinds of lists in say up in the body text of the documents, not in the footer or not in the summaries here. We see the cost summary, our line items, the salesperson and the state.
If we're going to do something like this we might actually be putting this information into say the invoice instead of the cost summary and we'd probably be putting addresses and who the salesperson is up here in the body. There are other videos on on how to customize the templates to do that but we can show that really quick. But this was the basics of setting up lists, importing data into lists or hand editing lists and how they will work in the data entry.
We'll just go back into the Preferences and we're going to go into the Quote Templates and let's update the invoice and we'll click the Add Tables of Quote Tags. All right, so we've just made a custom copy of the invoice for our database. That's going to now include tags for these lists we've just added.
You can see here now we want to put let's say salesperson match this to the other styles and this is going to be Proposal Kit Subheader 2. Okay, now we've put the salesperson actually up here. Let's just remove that entire row from there.
The state, now the bill to these will actually be filled in from the fields entered in the project, not the line item database but we can also mix and match with both systems. All right, so you can see we've got the state code which is our drop down. Sales person up here.
We'll remove this from here. So we're basically creating a custom invoice template that will put our new dropdowns up in the body of this invoice just to show you how you can customize all this stuff. These items first name, last name, these addresses, city, work phone these are coming from the project data fields, not the line item database fields.
But we could go and recreate fields for all these if we wanted to in the line item database but it's kind of duplicating something that's already set up in the system. Now that we have that custom invoice set up let's just go recreate that project. You'll see the invoice, all right so this company data and client data.
This client data here this is where the other fields the client's first name, last name and so on will get pulled from. Just to show you so you'll see we don't have a postal code here. That's the state code that's going to come from our dropdown.
Now I'll just do another Test and Preview and you can go iteratively back and forth doing a test on a new project go back and edit settings or templates until you get your system set up the way you want all your documents to start looking. This is kind of a normal iterative process for how you would customize everything. All right, so now you can see that our dropdowns the salesperson and the state have been moved out of the summary lines here and they've been moved up where we added those tags.
Now they're still here in the cost summary so that means we could either create a custom template for the cost summary where we would just not add lines down here for these or we can just go flag these new fields to be invisible when the Wizard's creating the table like it's doing for the cost summary. That way they will drop out of the cost summary but they would display in the invoice here. There's another video that really goes into depth on how to do those kinds of customizations.
For now now this will cover how to use the drop–down lists in your line item databases. Another option we have is we can export our line item database. Now you see we've exported our current line item database.
These two items we could actually hand enter even more and these columns here, calculations, the ones in orange. Those are used as calculations so we don't actually have to do data entry for importing this back in the summary. Same thing we can change these.
We wouldn't change these because these are calculations. Our salesperson list can add a couple more there. State list, yeah, maybe we can just change the default.
Maybe we just want to add in, let's just add one in for maybe Puerto Rico or something. Okay so now that we've saved our exported spreadsheet and this spreadsheet is only in our project folder for this specific project. But now we can import the data from that spreadsheet.
So it's basically going to import and replace everything in our current database with what we just hand entered in that spreadsheet. Now we can go into manage the data we see there's three lines. So you can see the yellow widget that we added in the spreadsheet.
Manage the global data and now you can see it's actually imported the new salesperson items and the new state list item. Because lists are at the database definition level we've actually updated the database definition doing this. So let's go show you that real quick.
Go Edit Definition, go to our Lists. Now we edit our Salesperson list. Now you can see these new entries are down here that we just imported from that project spreadsheet and in the State list it updated the default display and pulled in that new value at the end.
So that's a third way you can get data into your lists for use across all your projects is to just import the data from one of your projects using an Excel spreadsheet. All right, so that is pretty much everything you need to know about using drop–down lists in your line item database.
Using our professional quality proposal and contract packages, wizards, and support documents to develop your proposals, business plans, and other business documents will give you a comprehensive final document that will present you and your organization as a highly professional alternative that instantly inspires trust.
It will provide you with the inside track. You can order and instantly download the Proposal Kit that best suits your needs.