Lookup Field

[snowboy76]

I'm a very beginner, so warning if this super simple.

I have a table (Items) with 2 fields, "Item Name" and "Item Code". In another, related, table (Orders) I have the same fields.

On said Order table, I have a pick list that references the "Item Name" from the Items table. I would like the "Item Code" field to read the "Item Names" field and choose the "Item Code" that matches.

As an example, in the Items table, if I enter an item as [Widget] (Item Name) and as [rt657] (Item Code), when I chose [Widget] as the Item in the drop-down list in Orders, it should fill in the item code.

[The Big Bear]

Create a relationship between the two tables using the field name "Item Name" in both tables. Base your lookup on this relationship for the second field.

Hope this helps

[snowboy76]

Yeah have a relationship between them already. Was looking for more of the steps to get the lookup to work. The code, in other words.

See attached image.

[Fenton]

It is not so much code you need, it is a better understanding of what ID (or "code") fields are used for, coupled with learning to use the features of a FileMaker Value List.

First, the reason for "code" or ID fields is so that you have a short reliable method to positively identify an object (entity, thing, whatever). This idea existed way before computers were invented.

So, you would not use the "name" to Look Up the "code," you would use the "code" to Look Up the "name."

The relationship would be based on code = code, only. The drop-down (pop-up) value list would be based on the field Item Code. It would [x] Also display values from 2nd field, which would be the Item Name field.

In later versions of FileMaker there is a further option, lower down, to "Show values only from 2nd field," if you want to see only the names, not the code. There is however a hidden requirement using this option that every name be unique. Because indexes contain only unique values. If two names are the same you will only see it once, and will only be able to select the code from that first one.

[snowboy76]

I think I'm not being clear here and that is my fault. Hope none of you are wondering how this idiot got on the forum. -)

I have a lookup list that references the "Item Name" field. And I see that you can also reference "Item Code" when you reference the "Item Name". That is nice, but not want I want to do.

I want a separate field to see I've chosen "Widget" from the list and lookup the corresponding "Item Code" for a Widget in said Items table. I tried making the field for "Item Code" use the LookUp feature to get that to work. I thought I had it right, but it doesn't work.

If I was in Excel, I know how I could use the HLOOKUP to do this. I'm just wanting to do something similar with "Item Code" field. If the Item Name is Widget in the "Item Name" list, then the "Item Code" would be the corresponding number in the "Item Code" field for the Widget.

See attached images for were I'm at now.

[larrytheo]

Rather than offering a solution, I will expand on the problem, as I have a similar situation with an added dimension.

I have two tables: songs and setlists. Songs contains fields like title, duration, key. Setlists contains similar fields, but up to 10 of them per record, so: title 1, duration 1, key 1, title 2, duration 2, etc. Currently, these are separate fields, not repeating fields.

The title fields in a record in setlists use a picklist of titles from songs. What I want is simple: to pick a title for a title field in a setlists record, and have it look up duration and key for that song from songs.

Seems like such a simple thing: Lookup "duration" from songs for title in songs = currently active title in songlists.

I've tried making a global field in setlists based on Get(ActiveFieldContents) and using that as a match field to title in songs. That didn't work.

This seems like it should be an easy thing, so I have the feeling I'm missing the obvious.

Whatever the solution is to my problem will probably be a good pointer for the original problem in this thread.

Thanks!

[comment]

See here (and beyond):

http://www.fmforums.com/forum/showtopic.php?tid/193586/post/283003/#283003

[larrytheo]

I'm not getting it. I understand the idea of join tables, but I don't see how it applies here. Join tables map many-to-many relationships in a relatively fixed way. I don't understand what things I would be mapping to each other.

The problem is see is that filling out a setlist record (i.e. making a setlist) is a dynamic, iterative process, not one in which definition is clear and static. And I don't need to map two things together so much as do a lookup. I've worked in other database programs where this was quite easy to do, but, as is often the case with FMP, the mechanism for accomplishing a sophisticated task is not obvious.

Am I missing the point with join tables?

[comment]

A setlist has many songs. A song can belong to many sets. Therefore, the relationship between setlists and songs is a many-to-many - and is most conveniently handled by a join table.

You can forego the join table and assign songs to a setlist by, say a checkbox field in the setlists table. But you will lose some functionality that way.

I am afraid I didn't understand the rest of your post. Making a setlist, I think, is a matter of creating a new record in the setlists table, then assigning some songs to it. I don't see how the terms "dynamic", "iterative" or "static" apply here.

[larrytheo]

I guess I don't understand how I would construct the join table.

As for the dynamic and iterative aspects, the process is that I choose songs, then go back and change the choices repeatedly, based on the information looked up for each song, until I get the right combination. That's why I'm not clear on the join strategy: if I have a join record that contains a setlist name (or ID) and a song name (or ID) and then change a setlist entry so that song is no longer in the set, what happens in the join table?

It seems like I'd have to select a song in the setlists table, then go to the join table and add a record for that mapping of song to setlist.

I'm sure I'm missing something simple here! Thanks for your persistence in helping.

[comment]

Did you play with the demo file? You can assign a song to any list from Songs, or a add any song to a list from Lists.

[larrytheo]

I went back and dug deeper into the demo file, but it still isn't working for me. I made a new dbase that essentially duplicates the demo file where Songs = Contacts, Setlists = Organizations, and Join Map = Affiliations), only there's something that isn't right.

When I go to a portal, in the demo file (say, in the Organizations table) I get a drop down of Contact IDs. Although I set up the fields and the portal the same way, as far as I can tell, I can't get tbe drop down list of Song IDs (= Contact IDs) to appear in the Setlists table.

In the demo file, as soon as select a Contacts ID in the Organizations table, it creates a new record in Affiliations, and I don't understand how that is happening.

So I'm getting closer and have a greater understanding of what I need to do, thank you very much, but I still can't *quite* get it to work.

I set it to

[comment]

In the demo file, as soon as select a Contacts ID in the Organizations table, it creates a new record in Affiliations, and I don't understand how that is happening.

Check the definition of the relationship - it has "Allow creation of records..." enabled on the Affiliations side.

I'm afraid I didn't get the first part.

[larrytheo]

That was it!!! As soon as I enabled records to be created from the relationship, everything worked.

Thank you SO much. I had the feeling this was a very basic kind of problem, but one I had not had occasion to master yet. This one little piece of deeper understanding of how to do join tables in FMP is going to open all kinds of new doors for me.

Mission accomplished. Thank you very, very much. Made my day!