Value List, New Option ?

[raymanj]

I am sure many of you have seen the new option in the value list dialog which allows you to display one field's contents and return another field's information. It's the check box named "Show values only from second field" in bottom right-hand corner of the Specify Fields for Value List dialog.

I am trying to use this option in the following scenario:

Tables:

Items (contains records of items)

Seasons (contains a list of different season in a year)

Seasonal Item (a table that contains keys of the item and season records. A join file)

Relationships:

Item::ItemID_pk >> Season Item::ItemID_fk

Season Item::SeasonID_fk >> Seasons::SeasonID_pk

Goal:

I want to make a value list that will display the season's name but return its ID, so the user will not have to see any IDs while adding a season to items.

I am able to create the value list that does the above.

Problem:

So how do I get the season ID that is returned into the correct ID field of the Seasonal Items table? If I have a portal that list all the related records in Seasonal Items table on a layout for an Item and have one of the fields in the portal pointing to the season name field in the Seasons table, what field do I put the value list on?

If I put it on the seasons name field it will enter the ID not the name. I cannot display the ID field and link the value list to that as that will show IDs. So I am a little stumped on what to do.

I am not sure how this new option with value list is be used.

I hope I have explained myself clearly.

[aaa]

I think that you must create value list for ID field, where you will show only name. Then join this value list for ID field. When you entry to ID field you will look only names, which you can chose.

But i think that you must see in value list ID too, because two ID may have the same name.

[SlimJim]

aaa is correct. If you create a value list in this way to show names but not IDs then what you get is a value list of the names. The pop-up will choose the first matching ID for the given name. So for this to be useful you need the ID to be uniquely identified by the name. If that is the case you may as well use the name in setting up the item (an extra relationship using name rather than ID) and then look up the ID in the background which will avoid exposing the ID in the items portal.

[raymanj]

aaa is correct. If you create a value list in this way to show names but not IDs then what you get is a value list of the names. The pop-up will choose the first matching ID for the given name. So for this to be useful you need the ID to be uniquely identified by the name. If that is the case you may as well use the name in setting up the item (an extra relationship using name rather than ID) and then look up the ID in the background which will avoid exposing the ID in the items portal.

Storing the season name and then looking up the ID for the season kind of defeats the purpose for assigning IDs to the season name. The reasons for the IDs are to store smaller amounts of information (numbers not names) and have a single point of changing data. If a user ever needs to modify a season name they would only need to change one season record and all related items would automatically display the update without searching for each item record and changing the season stored in it.

I guess Filemaker still has not made it possible to easily show one set of information and return another to be stored in another field.

So here is the big question now, what is the point to add such an option to value lists if it is going to return the wrong information to the field that the value list is linked to?

Any comments?

[comment]

Perhaps you are expecting too much. There is no dramatic change here from earlier versions.

You are still expected to have an ID field, and enter an ID into it.

While selecting the correct ID, you can see another value associated with the ID.

If you chose to sort the list by the associated value, duplicate values are omitted.

The field itself stores - and displays - only the selected ID.

None of this has changed. The only change is the option to make the IDs invisible in the value list. If you choose this option, the "Sort by second field" option is automatically selected for you.

You can display the selected value - season name in your case - by putting a related field on the layout. If you disable entry into the field, and put it over the ID field, then clicking on season name will drop down a list of season names. But during the selection process, the ID field will be in front, and the selected ID will be visible.

If that is not acceptable, you need to go back to one of the workarounds of earlier versions.

[SlimJim]

Storing the season name and then looking up the ID for the season kind of defeats the purpose for assigning IDs to the season name. The reasons for the IDs are to store smaller amounts of information (numbers not names) and have a single point of changing data. If a user ever needs to modify a season name they would only need to change one season record and all related items would automatically display the update without searching for each item record and changing the season stored in it.

Any comments?

I was not suggesting that you replace your main relationship ID to ID with a name to name relationship. I was suggesting a method whereby you can set up your ID to ID relationship without the user seeing, or even knowing about the existence of, an ID. Also I do not agree that the purpose of the ID field is to save space. The ID is there to identify the record. If you change the name of a season the ID doesn't change, the relationship ID to ID doesn't change; the name to name relationship may change so you have to be aware of that and make sure that the ID's you have already chosen by this method are protected from being changed by changes of name.

[sledg]

I agree with you raymanj. I don't see the point of hiding the ID when it shows the user anyway (after selection is made). I would prefer it act like a lookup and show the selected value but sadly it doesn't. I don't think you were expecting to much cause I was expecting the same.

[comment]

If you hide the ID, it will NOT show AFTER selection is made.

Only if you have made a prior selection, you will see the previously selected ID in the field WHILE you are changing your selection underneath.

[sledg]

Hey comment.

I'm not talking about your solution. I'm talking about the way FileMaker did their so called new feature.

I understand how to do the work around that you suggested. I still don't like the fact that the user will see the ID when the list is displayed.

It drives me crazy that FileMaker can't make working with ID's a little easier when using value lists.

[comment]

I agree with that. That's what I meant by expecting too much. I would also like drop-downs, checkboxes etc. to display the native OS graphics. That new "arrow to show and hide list" is particularly lame.

[SlimJim]

To get back to the original poster's question. I think we have established that some sort of work-round is required. Since my previous effort didn't appeal I'll have another shot.

This requires the addition of one global text field which I will call Helper, one extra relationship and a couple of Auto-enter options on the Season Items fields.

I attach a mini-sample in which I have tried to match what was described in the original post of this thread. As I have no idea of what kind of data was being described I have gone for seasonal clothing. :

Just for the record you will need FM8 to make much sense of this sample.

Seasons.zip

[sledg]

raymanj,

Did you figure out a solution yet. I just noticed something in FM8 that kind of surprised me.

If you are using a value list and hiding the ID, you must use the "Pop-up Menu" option to display the associateed name. If you use the "Drop-down list" option you will see the ID when the selection is made.

Hope that makes sense. Not sure if that was what you were looking for though.

[CyborgSam]

An explanation for this behavior is that drop down lists allow the user to manually enter data, and since the user would have to add the data for the first field, the first field always shows.

IMHO I'd like to see drop-down lists without the ability to manually enter data. I like popup menus more, but for really long lists a drop-down can be navigated and view easier.