This is the first time we're gonna actually go through the nuts and bolts of creating forms that use DB.

And the first step there is gonna be to look at an Excel spreadsheet.

This is just the most basic sort of a spreadsheet you're gonna run into. You can see it's got the default three tabs at the bottom here, sheet one, sheet two, sheet three.

Sheet two and three sheet three are unused at the moment. In sheet one, I've just typed in some names and addresses. That's all I've got here. Names and addresses plus a salutation column and a, gender column here.

There's a few ways that we're gonna be modifying this so that it is more useful to us in DocsDB.

But first, I'm gonna show you what this looks like, from DB's point of view, just as is in its most basic form like this.

So in Docsoradb, you'll see we have no new buttons. That's because pretty much everything works just as it did before, except under sources, we have a new source for external information, the data source.

What you do is you set up a data source, and you link it to an Excel workbook. A workbook could have as many spreadsheets as many worksheets in it as you like, but each data source is gonna be linked to an Excel workbook. Here, I'm gonna create a new data source, and I'm gonna link it to that Excel, workbook that's sitting on my desktop.

I'm gonna call it, clients, and I'll just browse out to my desktop and select it. There it is, client list, and click okay. That has created a data source.

It recognizes that there's three worksheets in there, sheet one, two, and three. Sheet two and three have just sort of a placeholder column in them. They're not really doing me any good. One thing I'd like to do is get rid of those because they're just kind of, adding a little bit of extra confusion to my screen here. So that's one thing I'm gonna do to clean up my workbook is get rid of those extra worksheets.

Another thing is if I click on sheet one here where the information is, there's a couple of problems here.

First, there's no key column.

And what is a key column? If you click this info button, here's a lengthy description of what a key column is. Basically, a key column is an identifier column, which uniquely identifies each row in a worksheet. The most common sort of a key column you'll see would be a column on the left. It doesn't have to be on the left, but most commonly, you'll see a key column on the left side of the worksheet, and it might just be a sequential series of numbers so that there's a unique number assigned to each row.

Worksheets that have a key column are way more useful to us than worksheets do not for all sorts of reasons that we'll be, getting into here. So it would be great if we would add a key column to this, worksheet.

That's another thing we're gonna do. And then over here, it says we have some bad column labels. And if I look up on my list of columns here, it's actually showing me data for the first person in the list. It's showing me Alan, Ames' data.

These are not labels at all. The problem is and I'm gonna click the info button up here to see that, what we need is some column labels. The first row of our worksheet needs to be column labels. And there are certain characters that are not allowed in column labels, so I'm gonna avoid those as well.

So that's another thing we're gonna change in our worksheet. So I think that's three things that we're gonna change. So I've created this data source. I'm gonna be modify coming back and modifying it a bit.

But for right now, I'm just gonna click okay to preserve what I've done so far. And I'm gonna go back to my worksheet, and I'm gonna make those changes in Excel. First, I need to have labels, So I'm gonna insert a row here at the top and put in some labels. First name, last name, gender, salutation, valuation, address one, address two, city, date.

Yep.

There's my labels.

Next, I want to have a key, key column, and I'm gonna put it here at the front of the workbook. I'll click insert to add a column. I'll call this my ID column, And I'm just gonna sequentially number these. It doesn't matter what goes in the key column as long as it's unique for each row. Sometimes a Social Security number will serve as a key column, sometimes a phone number.

If you have a parts list, perhaps a part ID number, anything that's going to be unique for every row in the list. Excel has a nice shortcut here. If I just type one and two like that, select them both, and see that little black square in the bottom right corner of what I've selected? If I drag that little black square down, Excel fills in all the other sequential numbers for me, so that's nice. If I have a table here that has hundreds or thousands of rows, I can quickly add a key column with unique ID numbers there on the left.

Last thing I'm gonna do is I'm gonna delete my extra sheets that I don't need here. I'm gonna right click on that and delete it. Right click on sheet three and delete it.

And I don't like the name sheet one. I don't find that very descriptive, so I'm gonna rename that and call this, people. This is a table of people, so I have labeled it such.

And that's all the changes I'm going to make to my worksheet. I will close and save my changes.

Return to my data source that I was working on.

I've already created it, so it shows up here in my list of available data sources. Now I called it clients.

And now I only have one table showing up here, my people table, since I only have one worksheet in this workbook now. And when I click on it to select it, I have a chance down here to assign a key column. Here's that ID column that I created.

It now has a key column. I don't have any red flags over here because now I have good column labels for all of my columns. Here's my list of columns that appear in the worksheet. And I'm gonna do just one last thing here. If you remember in our worksheet, we've got this gender column.

If I let DocsRODD know that this worksheet has a gender column, then it's gonna be able to, leverage that for me. It'll give me access to pronoun fields whenever I'm referring to this worksheet, and that's gonna be very useful. So whenever you do have a gender column in or or doesn't have to be labeled with the word gender, but any column that serves as a gender indicator in a worksheet, it's definitely worth telling DocsDB about it. Here, I'm gonna select that gender column, and I'm gonna turn on the pronoun checkbox. That means I'm gonna be able to create pronoun fields based on the information in that column.

If you ever need to get more information on that, there's another info button here, and it gives me the list of words that are valid gender designating words here. And m and f are among them, so I know that that gender column in my worksheet is gonna work great as a pronoun column.

And the bottom corner here, we're gonna deal with, in our next worksheet, not in this one, so we'll ignore that for the moment. I've told Docs and I everything it needs to know about this worksheet to get full value out of it now. Now I can start creating forms.

And at this point, you're gonna be surprised, I imagine, at how much you already know.

We're gonna use all the existing tools from Docsara to, to pull in information from that external data source that we just defined.

So I'll add a questionnaire to this form. We're gonna create a simple form here that's just gonna ask me to choose a client, and then the form's gonna tell me some information about that client. So client name, who's the client?

And this will be a smart answer.

It's gonna be a drop down answer.

And the source for the drop down choices, I've got a new choice here on my list, data source. And you can think of data sources as really quite similar to master lists. They do a a whole lot more stuff, we'll see in our second example here. But the one that we just created is really just a single worksheet.

It's gonna function very much like a master list. So I'll choose data source. Here's my list of available data sources. Here's that client's data source that I just created, and here's a list of tables or worksheets within that data source.

I've only got a single worksheet in there called people.

I want to show my form user a list of of people from that table in my answer. I'm gonna click okay here. And I did one thing wrong. I'll go back and fix it in a moment, but first I wanna see I want you to see the result I get right now.

Now, this has become a Fetch type answer.

Data source questions are Fetch answers in the same way that Folio questions are Fetch answers.

So data sources have some things in common with folios and some things in common with master lists.

Here I'll, pretend I'm now the form user and I click fetch to answer this question. And what I've got is a list of addresses here.

This is, my address column is showing up as my list of choices. That's not what I want in my smart answer, so I'm gonna cancel out of this, return to my smart answer screen, and find out why that is. And it's because of this. Here I get to choose the appearance of the choices in the Smart Answer.

I've already told the program the source for those, choices, But down here, I get to choose the appearance of those choices.

And here's the various columns I can choose from. In this case, here's a first name column. I would like the first name to appear, and then a space, and then the last name.

First name space last name. I'll click okay. And now when I click fetch, that's much better. I've got a list of names.

If I wanted to, I could rearrange those fields in my appearance selection. I could say, well, I'd really like to have the last name followed by a comma. I'll just type a oops. Type a comma in there. And then the first name, that'll give me a little different looking list.

There it is with last name first. I can even include some extra information. Maybe I want to, include for my form users reference, an indication of where each one of these people lives. I could put in parentheses lives in space, and here put in the state column and end up with a closing parenthesis there.

Click okay.

And now I get a little more detail in my list. I get to see where each one of those people lives.

So you can get quite elaborate in your appearance selections here, pulling from all available columns, combining them with surrounding text before and after each of those. Up to three different columns can be used, and, you can customize the appearance of those choices in the Smart Answer.

So I've got a Smart Answer here, which is gonna allow my form user to choose a client. Now let's build the form itself.

I'm gonna have it say, my client is somebody's name.

He or she lives at this address, and I'll put in an address there.

So we need some fields here.

First field is gonna be the name of the client. I'll click field and choose my client name answer and click okay. I've done something wrong there. I'll come back and fix it in a minute.

He or she, that's gonna be a pronoun field. I click field, choose my client name answer. My pronoun choice is available because I was smart enough to designate one of those columns in my Excel worksheet as a pronoun column.

So I have access to all my nifty pronoun fields here. I'm gonna say he or she lives at this address, and I'll just do a couple fields here. I won't put in the full address.

Lives at, and we'll put in address one field.

This is you'll see it's working very much like a master list field, and I'll just put in a city and a state. I'll abbreviate the address a bit for our convenience.

City and state. Period.

So the form user comes along, they need to answer this question, so they click fetch.

They choose a person from our list of people. I'll go with Carl car Carl Carl Carson here.

And when they click the fill button, the form reaches out into that Excel worksheet to gather all the needed information.

Now look. I did make one error here. My client is car Carson, comma, Carl lives in Washington. You see what it's done? Instead of giving me Carl's name in the format I would really like, it's giving me Carl in the format that I chose for the answer. I'll show you why that is. I'm gonna return to the field screen here.

And here, we have answer selected.

Answer means whatever appears in the answer box, that's what I want you to put up here. Sometimes that is indeed what you want. In this case, though, what I really want is Carl's first name to appear here, and then I want it be to be followed with another field that it that'll give me Carl's last name.

So even though the answer says one thing, I have access to all sorts of different fields related to that person in the finished document. I'm gonna reset the form and fill it.

That looks better. Karl Carson.

That is a basic form using a very basic Excel worksheet.