Value List option changing text box entry

[Adam AIS]

Hello,

I have a value list question. I have a FileMaker file which lists about 250 different locations. Each location is a separate record and has about 10 fields. I then have a separate FileMaker file, which connects everything together, and has a drop down menu listing all the locations as a value list.

Now my question is: some locations have a location ID number. I would like to put a text entry field on the second FileMaker file for the location ID. Then if someone enters a location ID that matches any of the location records it will automatically change the value list to the correct location and vice versa (if a location is selected with a location ID the ID number field is filled out automatically). Is that understandable?

Any help would be great! Thanks!

[ESpringer]

I take it not all locations have an ID, from the way you described it. It seems as though sometimes the user has access to the location name, and other times there is some practical preference for the ID...

It sounds like the issue has to do with the convenience of data entry, right? As long as it doesn't happen that someone could know the ID without knowing the location name at all, I'd suggest making a concatenated field in the locations file: c_Concat = name&" #"&ID, and letting your drop-down value list in the central file bring up all these concatenated values. That way, the data entry person gets a quick confirmation of the whole package -- ("yes, that location with that id#). Typing directly into the pop-up list will auto-scroll the list to a close entry, and then return key selects the highlighted entry and moves on to the next field...

Then, you could make just one relation between the files based on the concatenated value, and both the name and location ID fields, if they need to be listed separately somewhere in your main file, could be generated through calculation by way of that relation, or you could just make the related fields show up on a layout...

If you really need both fields each to trigger a lookup on the other, then I'd have to think again --FMP won't let two field definitions depend on each other that way, as far as I can tell... at least one of them has to be entry based on value list at best... anyone else?

[Vaughan]

Mt feeling is that either *all* locations have ID, or *none* do. Inconsistencies like this are impossible to work with.

So ,give all locations an ID -- just a simple, meaningless serial number is best. Then make a value lst that displays the ID number and the location name, and sort by the location name.

At the data entry screen, the person enters the ID number but the value list displays the name as well so it's easy. You can then put a calculated field next to the ID field that displays the full location name.

Anything else becomes very messy.

[Adam AIS]

Thanks. I think I understand the limitations with only having a couple location ID's. After thinking about it for awhile I've come up with another idea. Maybe you guys can tell me how workable it is:

Is there a way I can create a list of locations from the values file and then have a button which will trigger a small pop-up that lists the location, location ID, and maybe a couple other fields from the value file in a column view. The user can then sort by any of the fields and select the proper location, which would in turn change the value on the main file?