An office debate spurred the creation of AppWorks’ Value Picker Smackdown. Which method of creating linked data is easiest?

Watch both videos below, then vote here

David’s Method


Hi this is David Weiner with AppWorks and I’m here to present the Value Picker Smackdown. My presentation is how to create the simplest filtered value list for picking related values, but a subset of related values from another table.

For example, in this scenario, you might have a person table and you want to attach a company to that person, so you have a company table as well. The companies can be active, or inactive, depending on the contents of the status field. So, what we want to do, is, from the person side, be able to select from a dropdown, from only the active companies, not the inactive companies. In situations where you have a lot of related records, you don’t want to see hundreds and hundreds of records in your dropdown. So, the first thing we want to do is make a value list that will show us the companies. And, when we go into layout mode from here and select the ID-Company field from the person table, and we want to put an ID of a specific company in here, which will then relate that one company to this person record, and we’ll be able to see the company name, address and city from this person.

So, when you go to this database palette, you can choose from the control-style popup menu from this field. You want to choose a value list – there’s already a value list in here called Company Status, which just shows active or inactive. What we want to do in this case, is to make a new value list. We’re going to call this Active Companies. And, what we want to do is use value, from a specific table. The specific field in that table – in this case, what we want to do, is be able to choose from the companies.

There are actually two pieces of info we want to see in that value list. The first is the PrimaryKey. We’ll use values from the first field in the company table, which is going to be the PrimaryKey field. We also want to display values from a second field, which is going to be the Company Name. If we just leave it like this, what we’ll see in our dropdown is a long string of numbers, which is the PrimaryKey, and the company name. That’s not all that useful, because the PrimaryKey is not a great way of identifying the company name. We really just want to see the CompanyName. We’re going to show values only from the second field. What this does is it just displays this CompanyName. The PrimaryKey is actually the piece of data that’s going to be deposited in the field when you choose from the dropdown, but you don’t actually see it because it’s not useful. I’ll show you what that looks like.

At the moment, we’re going to hit ok, we’re going to back out of all of these. Now we have the active companies values list showing on this field. When we go back into browse mode, we select this. You can now see all the companies in the company table. Now if that was a lot of companies, this list would be far too long. What we want to do is have this only show us the active companies. There’s one other step we have to do here. We have to go to Manage Database, and we have to make a new relationship. Right now, we have a relationship between the person table and the company table, by the ID of the company, or the PrimaryKey. Now this value list actually creates a relationship. It deposits the PrimaryKey of the company into the ID_Company field in the person table, and now we’ll have a relationship between the two. But if we want to show only the active companies, what we need to do is actually make a new table relationship. This one we’re going to call Company~active.

So, I added a table, choose the company, name the table occurrence Company~active, and now we place this over here. What we want to do is make the person record a field in the person record, match the word active in the status field. That will make it so that from the person record, you can see all the companies that have the words active in the status field. In order to do that, we actually have to have a field in the Person table that can hold the word active. We’ll go over to tables, double click on Person, add a new field. We’ll call it Active. This field’s going to be a calculation field. The calculation field can be pretty much anything that you want. We’re going to have that calculation field calculate the word “active” as a string. We put it in quotes because its a string. If we leave the quotes off, FileMaker won’t know what to do with it. It’ll think its a field name or something. We also want to make sure the calculation results in text.

We’ve basically added a table in which every record will hold the word active in this one field. We’re not going to use it for anything except this relationship. Now we make a relationship between the word Active and the Status of the company. So from any given person now, we can view either through a portal, or in this case, it’s going to be a value list, all the active companies because the word active matches the word active in some of these companies but not all of them. Hit oK.

When we go over to here, we go back into layout mode, when we select this ID_Company field, we’re going to actually edit the value list. Click on the pencil, hit Edit, and then we specify the fields that we want to use, and now we’re going to use values from the first field in Company~active table occurrence. We’re going to also display values from the second field in Company~active. We want to choose PrimaryKey again. We want to choose PrimaryKey from the active company we want to select, but we want to show it by the company name because we can’t really tell which company it is by looking at the PrimaryKey. Now we’ll choose by CompanyName, showing values only from the second field, this time we’re going to change this checkbox to include only related values starting from person, which means that we’re only going to see values from Company~active that are related to the Person record, which of course will be, now that we’ve set up a relationship, only active companies.

Hit ok, we back out of all the dialogs, we come back into Browse Mode, now when we select this, it only shows us active companies. If we go back to the company table and we choose Essential Stuff, we make it Active, when we go back into Person, now when we choose this, we see Essential Stuff is now part of the active companies dropdown. Now we can make a relationship between the Person and the Company. And that’s how you do it, it’s the easiest way to do it, and I hope you vote for my video. Thank you.

Matt’s Method


This is Matt at AppWorks, and today’s video is going to be a value picker shootout in which I show my favorite method to link table data from one to another. In our little sample data here, we’ve got two tables – we have person and company. What I really want to do is be able to link a company to a person. For example, Spirited Ways has this ID here – if I copy that to the clipboard, go to my person layout, and I past that ugly ID into the foreign key company ID field for this person, it will then link that company to this person record.

How I do this with the picker, though, involves just three little things. One is a layout that’s a list view. On this layout I’m going to do a search to limit it to only certain companies. An important thing is that I only want to find companies that are active, not just any company. The second parts are two very simple scripts.

Let’s get working on it. I start by going into layout mode in person. I create a new button where I want it to be. I’ll call this script and the button Select Company. It’s going to run a script. The script doesn’t exist yet so I can create it. I’ll call it Select Company. It’s just going to have a couple of steps. First, it’s going to do a new window. The new window has quite a few options in it, one of which is make it a card window. Second I can choose a specific layout. I choose the new card layout that I made, which is just that list view. Third, I’m going to choose a height – I’m going to make this around 500 pixels high, so it will be tall enough.

The next step I want to do is limit my list of companies to only the ones that are active. That’s really simple – I say Enter Find Mode, I say Set Field, and the field that I’m searching against is in the company table, and it’s called Active. Active is the value in the status field. What’s cool about this method is that you can do any type of really complex search, far more than you could ever achieve with a relationship you could build in here and it’s really easy to maintain. The very last step is perform find. That’s it for this script – here’s how that would look.

My button bar has 3 segments, I get rid of the other 2, combine it with a nice look here. What this will do is bring me a list of all of my companies which are active. Pretty sweet, except what I don’t yet have, and what we’re going to do next, is what happens when I click one of these, because what I really want to do is grab the ID and link it to my person.

Let’s build that – I go back into scripts, I make a new script called Link Company. This is going to have just three lines of code – first I’m going to grab the value of the company that I’m choosing – I say grab the ID of that company. That’s kind of like my clipboard thing. Second, I’m going to close my card window. Last, I’m going to set the field of my foreign key, which is the Company ID in the person table, to the value that I just made a second ago called $id. That’s it – that’s the whole script.

The last and final step is, on my card, I need to have a button that actually does this, so I can just click on my two fields, right click it and say Button Setup. Choose Perform Script, choose Link Company, and that will work. I love looking at things in debug mode, so we’re going to turn on script debugger and watch things happen. First of all, I click Select Company and it runs my script, it opens up my card window, goes to find mode, searches for active companies and there’s my subset. Now I have no script running, so I can close my window. The next thing I want to do is choose my company, so choose AppWorks, which fires off my second script, the 3 lines of code, grabs the ID from the company, closes the card window, and sets the company ID right there, and it’s done. So there’s a lot of different things you can do to improve this and make it more flexible and more powerful, but those are the only requirements to make this work and it’s very easy to just choose another company, or if you don’t want to make a change, just to close the window, so it’s got a lot of really nice things to recommend it. Thanks very much for your time and I hope you vote for my video.