Drop Down List Normalization

[patrickglass]

Hello, i am very new to FileMaker and this will be my first database. I am creating a maintenance Database for our company to organise maintenance reports. I already have a complete solution in MS Access. i found filemaker and loved the idea of being to be able to do data entry over the intranet.

Now onto my situation. it seems my background in access had led me in the wrong direction. i currently have two tables,

:Equipment

-EquipmentID

-EquipmentTypeID

-EquipmentName

::EquipmentType

-TypeID

-TypeName

They are linked on Type Fields ie CM11 is a Continuous Miner

In access i had a combobox that would drop down and autocomplete the TypeName but on complete would fill the source field to be the index or TypeID field. in FileMaker i can do everything the same except i would have to have the drop down display the ID value rather than the text which is much less reader friendly. i have found a work around where you have a text box over top of the list and it would display the related value.

The problem arises when we want to autocomplete the drop down. with this curent method it will only autocomple for the ID number and not the type of equipment. we have tons of datafields for the rest of the database of the simular kind so a robust solution would be very welcome.

Well my real question is how are we in filemaker supposed to normalise databases if we cannot use ID's in an easy manner, should i convert all my ID fields to be text and just store everything that way, destroying all usefullness of a database. i am confused as how to get around this large situatiuon. Thanks for the time. Patrick Glass

[Fenton]

You cannot combine drop-down list AND autocomplete options. Autocomplete is from data already entered in that field. Which is not so good for such a choice anyway, as it would be useless for a new entry.

It can't do the ID switch trick. But a drop-down list can. If you use values from the ID field, and say "Also display values from a 2nd field", the name, and [x] Show only values from 2nd field. It drops down showing the names, it scrolls to the letters you type, then you click or hit Enter to enter the value (which will be the ID).

And yes, you have to put a non-enterable name field on top of the ID field. So the difference is that a list drops down; but it does that with auto-complete also.

[patrickglass]

Thanks for the reply, i do have a drop down list with a name field over top of it. but the autocomplete feature does not work how i would like it, i want to be able to see what ive typed and have it do a filtering. Access has a much better solution for the drop down it seems. it will still only have the numbers to write in. i also have show only second field however if i were not to have the text file over top of the listview it would still only show a number of the ID value. Thanks

Edited August 11, 2006 by Guest

[patrickglass]

Hi again, i now would like to just ask some developer professionals ( Anyone on this form ) how they use drop down lists with normalized tables. is the only main method to hide the dropdown index with a name field on top.

Also i was wondering if someone could point me to some code that when someone clicks on the drop down but not neccessarily on the triangle it would pop down the list. this just makes it so the autocomplete works for me. Thanks for the time. Patrick Glass

[davidtraveller]

I have the same issue - does anyone have a good solution for this, other than laying a text field over the dropdown list field like Patrick has done?

Edited June 14, 2007 by Guest

[Oldfogey]

Not a different solution but a modification. I don't like the little arrow there but ....

I've left it there in case users mess things up. My ID-field is just 1 pixel wide - effectively invisible. My Description-Field is large and on top as you know. However, it is defined as a button which "Goes to field [iD_Field] [select/Perform]". When a user clicks in the field instead of hitting the arrow, the drop down list appears and behaves in almost exactly the same way as auto-complete.

Elegant? Not really but it works and looks OK to users. Elegance takes 2nd or 3rd place to 'works'

(And please do not get me started on Access vs FileMaker.)

[davidtraveller]

Thanks, that works well for me - shrinking to 1 pixel/changing to a button gets rid of that big black area that otherwise appeared when I tabbed into the field.

[davidtraveller]

Unfortunately I've found another issue with this general method - when I'm in Find mode, I can select from the dropdown list but the name field remains blank even though the ID has been chosen. Any ideas on how to fix this?

Thanks

David

[Juniper]

Hi , I have had to change the Dropdown list to a popup in order to get the second field to stay.

ie

1 - carrots

2 - bananas

Dropdown stores 1 shows 1 in the field (normalising) but

Popup stores 1 but shows carrots

works with find

Still not satisfactory