Combo-box Table Lookup Value

[Hellmut]

I am new to FileMaker, but am familiar with MS Access.

In Access, what I would want to do is use a combo-box so I could type a person's name (e.g. Smith, John) and what would really be entered is the Person ID (i.e. the key on the person table).

How do I do this in FileMaker?

[Genx]

Relate the field in the table where you are "selecting" your person from a pop-up list to your original persons table by the person name i.e.

Table::PersonName = People::PersonName

Then just make a new lookup field in your original table and set it to look up the id over this relationship

~Genx

[Reed]

Another way to do this is to use a drop-down menu with a value list in your Person ID field. Value lists can be formatted to show values other than the ID itself (like a related name).

In FM7 and below, you still see the ID value in the list with the name, but you can sort the list by the name, and if you start typing the name, you will move automatically down the list to the correct position. When you choose a value, the ID populates the field, not the name.

In FM8, you can format the list not to show the ID at all, but it still shows in the field after you choose it. To Hide the ID from view after moving out of the field, you can place the related name field on top of the ID field, but set it not to allow entry. Then after you exit the ID field, only the related value will show.

It's not real easy to hide the ID value when a user is actually in the ID field (unless you use a popup menu in FM8, in which case the ID doesn't show at all, but you don't get type-ahead in popup menus like you do in drop-down lists) The best way to hide the ID value is to change the text color to match the background color so you don't see the text.

I know that's a lot of information, so let me know if you want me to post an example file, or if you can figure it out on your own from my ramblings...

[Genx]

I dont really like doing it this way because if your reviewing your data, i.e. browsing through your records it may be difficult to understand the numbers vs. peoples names, but then again this is just my point of view, it might be exactly what Hellmut is after.

~Genx

[Hellmut]

Thanks, I was thinking about a drop down menu as well but could not figure out how to do that.

[Hellmut]

I would be grateful for an example, Reed.

[Reed]

Here is an example file showing the various ways to dislplay the id values in lists with the related names.

Like I said, in v8, you can remove the id value from the list or completely from view if you choose, but in v7, you'll see both values.

Dana

dropdown.fp7.zip

[Hellmut]

Thanks, Reed. That helps big time.

The one thing that I could not figure out is how to format a field as a drop-down menu.

I know it's embarrassingly simple but how do you do that, please?

[Reed]

Field Format (Command-Option-F in Layout Mode) can be chosen when the field(s) are selected.

[Hellmut]

Thanks, I got it to work.

Can I define fields in portals the same way?

[Reed]

Not sure what you mean... to place a field in a portal you simply position a field on your layout within the first row of the portal. Then in browse mode, that field will be displayed for all related records.

If you mean can you have a drop-down list in a field that's also in a portal, then the answer is yes.

[DanS]

Hi all,

I too am new to Filemaker, but have plenty of experience with other relational databases - including MS Access. Access has quite a nice setup for creating combo boxes populated from ID/value pairs in other tables, and it's pretty trivial to set up a combo box that will show you names from a table, while actually populating a numeric ID field.

Genx, you say:

I dont really like doing it this way because if your reviewing your data, i.e. browsing through your records it may be difficult to understand the numbers vs. peoples names, but then again this is just my point of view, it might be exactly what Hellmut is after.

Am I misunderstanding what a "portal" is in Filemaker? It seems to do the same sort of things you'd do with a Query in other databases? In other databases, when I wanted to look at my table (let's call it "invoices") in a human readable form, with people's names rather than IDs I'd build a query to join the two tables and show names from the people table in place of the "PersonID" in the invoices table. It's common to have raw tables full of IDs that don't make much sense to a human being - which must be joined with queries to produce readable output.

If you go ahead and work in your preferred way (storing the person's name in the invoices table in a PersonName field while having a second PersonID field), what happens if you change a person's name in the "people" table? Do you then have to update every invoice for that person and update the PersonName field in the invoices table? - or is there some Filemaker magic that performs that update automatically?

I ask because I've been brought in to work on a Filemaker database that has been set up in this way and - as someone who has worked with a lot of other databases - this setup seems to run contrary to every other relational database I've seen (and I'm wondering what I'm missing).

 

 

[comment]

You do this in Filemaker the same way as you do in any relational database: you store the parent ID in the child table, and display the parent's name (or any other attribute) directly from the parent's record, by placing that field on the layout of the child table. Note that a portal is not required here: you use a portal to show data from many related records; here, there's only one.

Note that Filemaker also offers the option to lookup the parent's name into a local field in the child table. This is meant exactly for those cases where you want to preserve the historical value and not have it updated when the parent record changes.

[DanS]

28 minutes ago, comment said:

You do this in Filemaker the same way as you do in any relational database: you store the parent ID in the child table, and display the parent's name (or any other attribute) directly from the parent's record, by placing that field on the layout of the child table. Note that a portal is not required here: you use a portal to show data from many related records; here, there's only one.

Note that Filemaker also offers the option to lookup the parent's name into a local field in the child table. This is meant exactly for those cases where you want to preserve the historical value and not have it updated when the parent record changes.

Hi Comment,

Thanks for that - I've actually been reading a bit tonight, and - for any other Filemaker newbies who are interested, yes - it seems that a "layout" is basically a query (allowing you to bring in fields from multiple tables, using the relationships you define between the tables).

On the subject of combo boxes (dropdowns?) and showing one value while actually storing another behind the combo, I saw a nice trick today for doing that (bit of a kludge, but it seems to work): 

 

So it seems that the only reason the database I've been given to work with has a combo box bound to the "name" from a parent table (rather than the ID) is either that this trick was not possible in versions of Filemaker prior to v12, or that the developer simply wasn't aware that this sort of thing could be done.

Indeed it also seems I was misunderstanding what a Portal is - it's basically analogous to a "subform" in MS Access.

Edited June 1, 2016 by DanS

[comment]

1 hour ago, DanS said:

either that this trick was not possible in versions of Filemaker prior to v12

No, this technique of covering the drop-down field with a non-enterable related field is pretty ancient. Another possibility is to use a pop-up menu instead. This will continue to show the value "only from second field" even after being exited. However, in older versions the formatting of a pop-up menu field was weird (and still is, to some extent)  - which is why the above technique was often preferred.

Consider also the possibility of popping up a portal or a list view of the other table to enable a more informed selection ( e.g. in case of two clients with the same name).