Here I have another workbook.

This one does have, the label row at the top, which I need. That's good. It's also got an ID column. That's good, for my key column.

It's also got two worksheets. Here's my person worksheet, which has all my name and address information like in the, other worksheet. But it's also got a company worksheet. It's only got three companies in it at the moment.

Each of those has an ID, so that's good. I've got a key column there. I've got my label row up at the top. This is all looking good.

But notice one very special thing. In my person worksheet, I've got this column. This is not a key column. A key column has unique values for every row.

This is my key column.

This column is a column that forms a relationship between the two worksheets.

It says Allen Ames is the number of company number one. So is Betty Barnes. So is Carl Carl Carlson. Diane Dunn is a member of company number two. If I look at my company list, I can see, oh, that means Diane Dunn is a member of gadget company.

So I've got here three people related to company number one, three people related to company number two, and four people related to company number three.

I've also identified over here who the officers are in each of those companies, who the shareholders are in each one of those companies, and which of those people are directors or managers of their various companies.

So there's a relationship between these two worksheets, and relationships supercharge your data.

We're gonna do some fancy stuff now that is made possible by the fact that this column here, company ID, in the person table is related to this column, the ID column in the company table.

That's called a relationship.

So let's set up a data source that will use that workbook.

Voxerat sources data, and it's a new data source. I'm clicking plus to add it. I'll call it my corp data source, and it is pointed at in my desktop this workbook right here.

Here's the two tables that are contained in that workbook.

The company table, I'm gonna tell it the key column for the company table is this ID column.

The key column for the person table is this ID column.

Do I have any pronoun columns? Well, my companies don't have genders, so there's no pronoun column there. My person table has people in it, and, yes, there's a gender column right there. So I'm gonna treat that as a pronoun column. That'll give me access to pronouns for all those people.

And the last box here, relationships. This workbook does have a relationship in it. In my company table, there's an ID column, and it's related to the company ID column in my person table. The way I tell Docs or Ah about that is I click the plus button to add a relationship and I say, okay. In my company table, the ID column is related to, in the persons table, my company ID column.

That's the relationship we were looking at over there in the Excel workbook.

That's okay. Now I've got a relationship here. Some workbooks might have many relationships among their tables. Here if I click info, here's a demonstration of a workbook that has four tables, four worksheets, and there are three relationships among them. There's the this blue relationship, a red relationship, and a green relationship. So worksheets can be variously related to each other. And if you get all of those relationships, encoded, if you inform DocsRADB of those relationships, then it can do much, much more for you when you're building forms that use this data source.

So I've told Boxer Eye everything it needs to know now. I've given each column, each worksheet an ID column. I picked out the pronoun column that exists in one of them, and it knows about the relationship between those two works sheets.

I'll click okay, and I've got a brand new data source to work with.

And, of course, these data sources, you don't have to create a separate data source for every form.

Now that I've created those two data sources, I can create an unlimited number of forms that use them. It's just like a master list or a folio. You create the data source, and then you can use that data source in as many forms as you like.

In this form, we're gonna do a letter. It's gonna be a notice of an upcoming shareholders meeting.

I'm gonna add a questionnaire, and the question is gonna be, recipient's name. Who is this letter going to? Who is the recipient?

Similar to what we did in our last form, I'm going to create a smart answer.

It's going to be a drop down choice. The source is going to be a data source.

The name of the data source this time is Corp. That's the data source I just added.

And my recipient's name is not gonna come from the company table. That gives me my company name. It's gonna come from the person table. I wanna choose a recipient from the person table.

And the appearance of items, I'm not gonna change this yet. I'm gonna show you what it looks like first and then we'll come back and change it. Click okay and click fetch just to see what that looks like. Oh, it's pulling in some random field, some sort of a date field.

I don't want that. Instead, I'm gonna return to that smart answer screen and say, I really want to pull in a person's name, and I don't really need anything else. Well, no. Actually, I changed my mind. I'd actually like to include in parentheses the name of the company to with which that person is involved.

It's in parentheses there so that when a user clicks fetch, they'll be able to see not only the person's name they're selecting, but also the company that's related to that person as well. Give them a little extra information there.

I'm also gonna ask for a date of meeting. This letter is gonna inform this person about the date of an upcoming meeting, so we'll put in a meeting date field here, date of meeting, and that'll just be a plain text answer.

Now let's build the form.

This is gonna be a letter, so I'll need the recipient's name and address up at the top. I click field, my recipient's name.

I want first the person's name, and I'll point out now, this is gonna come up over and over again. Notice how long our list of choices is here. Not only do I have access to the person's address, city, state, zip, etcetera, salutation, and so forth, all of this information from the person table, But because this person table is related to the company table via that relationship that we created, I also have access to all this company information from the company table. I have just this full assortment of information that's all tied to this one answer.

In this case, right up here at the top, the first thing I want is from the person table. I want the person's, name, and I'm not gonna put in the full address. I'll just abbreviate it with the city and state here.

Field person's city and field person's state.

Person's state.

So there's my personal address. I'll put in, dear here I need the person's salutation.

Field person salutation.

And the annual meeting for And here I need a field from a whole different table, from the company table. I still have recipients' names selected over here, but because my list is so long, I can go straight to the company name field here. That'll give me the company name associated with that person. The annual meeting for such and such company is on a particular date. Field, meeting date, treat it as a date. Notice we're combining information that's typed directly into the questionnaire with information that's extracted from an external data source. You can mix and match and pull in data from all over the place.

Period.

Now when I choose a recipient, fetch, let's go with, I'm gonna go with there's a particular one I should choose. Gary Grant.

I'm choosing this because I have preset the data in a particular way to show off different features and problems.

So Gary Grant is the recipient. The date of the meeting, I'll tell you, is gonna be, next month on the fifteenth.

Which and when I click fill, it pulls in all the information we need, not only from the person table, but also from the company table and from the questionnaire itself.

Now let's add some more information here. Reset.

And I'm gonna add a list of officers.

At the meeting, we will confirm our officers, colon. And here, I'm gonna put in, oops, wrong button, name, and, office for some various officers.

Underline those.

And here, I'll click the list button.

There is no there is no series type answer down here. Right? Ordinarily, but, if I weren't running Doctor. IDB, I would not be able to create a list at this point.

It would tell me, no. You need a series type answer. But because my forms have access to all of my external data sources, I can create a list even without a series type answer. Watch what I do.

I click list here.

See it says data source up at the top? It is not giving me any choices from the questionnaire because there are no serious type answers here, but it's still giving me access to all of my data sources. So I'm gonna choose my corporate data source, the persons table, because what I'm creating here is a list of people.

The column, I'll go with the person's name column.

How do I wanna sort this list? I could sort it by any of these columns.

In this case, let's look and see how I wanna sort it. I've got in my corporate database here's my list of people.

My officers, I would really like to be sorted in order of hierarchy. I would like pres the president listed first and then the treasurer and then the secretary. Fortunately, whoever typed in this data did that. Here, for company number one, it's listed the president first and then the secretary.

For company number two, we've got president, then the treasurer, then secretary, and for company number four, president, treasurer, secretary. So this is the order I would like to preserve. The way I can preserve that order is if I use the ID column as my sorting column. Rather than sorting by name, first name, last name, anything else, I'm gonna use the ID column, person ID, to sort. That way I'm gonna get the president at the top of my list.

And appearance, I'm gonna go with repeating paragraphs.

I'm leaving something out here. We'll come back and add it, if you notice what I'm leaving out here.

This is gonna give me let's see. I don't want it to say sample paragraph about. I just want it to give the name followed by a tab character followed by the office of that person. So I click field.

Again, this is information coming from my data source.

So I choose the data source tab rather than the questionnaire tab. And the field I want here is from the corporate data source in the person table and it's the person office field.

I've got the current item selected. Since I'm creating a field inside a list I'm using the current choice. That'll give me the current office of the current person in the list. Click okay. There's my office field. I'm gonna tighten up the spacing here a little bit, and that'll give me my list.

I do have one problem here.

It will become visible in a moment.

I've got Gary Grant selected from Widget World. I'm clicking the fill button and way too many office holders.

I've ended up with a list of every single person in my spreadsheet. I need to filter this list down. I don't want a list. I want a sub list. I only want the people who are members of the company, the same company as Gary Grant here. Let me hit reset.

I'm gonna modify this list. I'm gonna turn it into a sublist. Click the list button to go back into that screen. Here here's my problem.

I say include all items in the list. No. I don't wanna include all of the people in my persons table. Which ones do I wanna include?

Let's look at that Excel spreadsheet again.

Here is my list of people.

I've chosen Gary Grant as the person for this example. The people I wanna include are these four people, all the people who share the same company ID as Gary Grant. Gary Grant's got a three for his company ID. I wanna include all the people who have a three in this column in my list. I don't wanna include any of these people who are from different companies.

So to build that sublist, I say sort, here we go, sublist, only include the items where the person's company ID is the same as my recipient's company ID.

See that?

So give me all the people who have a company ID that's the same as my recipient name's company ID.

And click okay.

Now when I click fill, we're almost there. We still have one more problem. Ah, much closer. Our only remaining problem is that Helen Hopper is showing up in our list even though she is not an officer of the corporation.

There she is. Here, yes. She is a member of company number three, but she doesn't have anything in the office column.

So let's clean that up. I'll go back to my list screen.

In addition to filtering by company ID, I also needed to add another criteria in here. I'm gonna click and or.

Also, the person's office is not empty.

I only want to see office holders in this list. They have to be from the same company as my recipient, and they have to be an office holder.

Click okay.

Click fill again.

That's what we want. Our office holders listed in a hierarchical order.

I'll click reset here, and let's put in some more information about this company.

I'm gonna say, the let's see.

Here we go.

The director or manager or managers of the, corporation or LLC or partnership, whatever it may be, is or are these people.

Lots of information in this sentence that we're gonna need to, automate.

First, let's do this. It's either a corporation, an LLC, or a partnership. In our database, in our in our workbook, we've got a column here on our company table called type, which tells us what type of corporation this is.

So I'm gonna use that to fill in this field here. Click field.

And based on whatever recipient I chose, not only do I have access to information about that recipient, like the person's name and address, but I also have access because of the relationship to all of the information about the company to which that person is related. So I can put in just a plain old field for the company type here, and that's gonna fill in the company type or whatever person I selected down here.

Then here, if that company type is a corporation, I wanna use the word director, so I'll add a condition.

If the recipient name's company type is this text, corporation, then I want to use the word director.

On the other hand, if the recipient name's company type is not corporation, then I want to use the word manager.

Now let's put in our list of directors or managers.

Looking at the Excel spreadsheet, here we have a column called dir manager, and it's either got an x in it or it doesn't. So my dir managers are the ones who have an x in that column.

So I'm gonna put in a list here.

Give me a list from my corporate database, the persons table, show me the persons name, sort them by I'll sort them by company or I'm sorry. Sort them by person name here for lack of anything better.

And don't give me all the people in that worksheet. Give me a sublist of people. I only want to see the ones whose company ID is the same as my recipient's company ID.

Only the people in the same company as my recipient. And I click the and or button here. And, by the way, they also have to in their person in their director or manager column, they have to have an x.

Okay? All the people in that particular company who have been tagged as directors or managers.

I'll go with the default appearance over there. Gives me my list. I'm almost done with this sentence. The last thing I wanna do is this is gonna be a singular plural, field.

We'll click field, and what's gonna drive it is how many manager directors or managers there are for this particular company.

That information, I can't get directly from this drop down. There is no field for a particular person which tells how many directors or managers there are for a corporation. So I'm gonna have to instead of pulling that information from my questionnaire, I'm gonna have to pull it straight from the data source. I'll click the data source tab over here. What I wanna do is count up the number of managers or directors for this, company. So the source, data source is corp.

The table I'm gonna look at is the persons table because I am counting up some number of people.

And I don't wanna count all of the people in that worksheet. I just want a sublist of people. Which ones do I want? I wanna count up all the ones who have a company ID that's the same as my recipient's company ID, and they also have to be directors or managers.

So I say their, director manager column has a big old x in it. Okay? How many people are both members of my recipient's company and are directors or managers?

That's the sub list I've created here. Having identified which people I wanna count up, I can now use a singular plural field, and say, in this case, I want an s if that's more than one person or, no s if that's one person or less. I click okay, and I've got my field. I've got another field over here, and, oh, jeez, I don't wanna go through all that again. That was a lot of work to create that one field. So I'm just gonna copy this field.

My is our singular plural field is basically just the same thing. It's It's got all the same parameters to the sublist. All I need to do is modify it slightly, so I've copied and pasted here. I'm gonna modify it slightly. Instead of giving me an s when it's plural, I'm gonna choose is are.

Everything else here is the same. This whole complicated, filter I've used over here to create my sublist, exactly the same. That'll give me my is r word.

Let's try filling this in. I clicked the fill button. We've selected Gary Grant from Widget World over here. Let's see what we should get. We should get for Gary Grant of Widget World, there are four people related to that company, three of whom are directors or managers.

So, it should come up with Gary, Helen, and Inez in our results.

Click fill, and we get Gary, Helen, and Inez. Notice that our plural is correct. It's using the word manager instead of director because this is a partnership instead of a corporation.

It all looks great.