Value Lists: Hiding IDs and IDs vs Text

[devices99]

This is a throw back from converting a system from Access, it's quite a large database and although I'm keen to stick with relations using numeric IDs between most tables, but I feel it will confuse some users if some of these IDs are displayed on screen for many smaller / linked tables. Hiding in Access is trivial using a combo box, as is dynamically changing the combobox rowsource at any given time, but not in FileMaker, not that I can see.

In fairness, you can hide IDs value list pop-up menus but not pop-up lists, as some of the lists will contain many items, lists, not menus are required.

Currently I've added what I think is very overly complex and clumsy system to remove the on-screen IDs, but retain these actual relational links.

Firstly the standard ID relations for example (not a v good example as we show client and booking IDs, but it's the principle for illustration):

ClientID, relates Client and Booking table together.

Booking ID relates Booking and BookingLines etc.

all as normal.

But then I've added separate relations to tables for text "title" fields used in lookups, e.g. ClientName relating Client and Booking. A value list of ClientNames shows just textual names (no ID), then the ClientID field on the Booking is a lookup of the ID (from the name relation) populated once the text field is entered.

Surely there is a simpler solution without this or overlaying hidden fields, changing font colours etc in FileMaker Pro 11?

[Reid]

I'm keen to stick with relations using numeric IDs between most tables, but I feel it will confuse some users if some of these IDs are displayed on screen for many smaller / linked tables. Hiding in Access is trivial using a combo box, as is dynamically changing the combobox rowsource at any given time, but not in FileMaker, not that I can see.

Just for some clarification...Where are you trying to hide the IDs? Is it in a layout or a form you made? Is it on the relationship manager? Not sure exactly where you are seeing them and where you want them to be hidden from.

[devices99]

Just for some clarification...Where are you trying to hide the IDs? Is it in a layout or a form you made? Is it on the relationship manager? Not sure exactly where you are seeing them and where you want them to be hidden from.

Always in layouts and reports, not in the relationship manager! (Essentially I've been working with RDBMS for all my life, mainly with larger scale systems, SQL Server, Access for front ends, and now rewriting some "smaller" systems (systems with 20 users or less) into FileMaker Pro.)

Want to hide the IDs in dropdown value lists/fields (whether they have focus/being used or not). So for instance, in an Access/SQL Server front end based system you'd have field label Client followed by a combo box with Client ID (hidden), Client Name visible. User clicks the list to select a client, client ID is the actual data stored, but they only ever see the Client name whether in the field/list or anywhere else. (Again client's not the best example, but switch for some secondary table data where the ID is normally not relevant to the user.)

Currently I can only do this with a value list pop-up menus but not pop-up lists, without a lot of trickery.

[bcooney]

First, the approach of relating by a text value to lookup an ID is a mistake. Text values are not unique and you could easily return an incorrect ID.

Three approaches:

1. Popup menu. As you know you can format it to have a value list where only the second field is shown and the first is stored (the ID).

2. Layer two fields. Bottom field is enterable in Browse mode and is a drop-down list, ID and Text. Top field is related text value, not enterable. Validate (field option) to only allow values from value list (since users can type what they want in a drop-down list).

3. Popup window selector. With large lists, giving the user a popup window might be the best approach. This window could be a filtered portal or a list view with a script-triggered find. My link

4. Add a selector that helps narrow the choices (create a conditional value list). First choice is Country, second field's choices narrowed to selected Country's cities.

[devices99]

First, the approach of relating by a text value to lookup an ID is a mistake. Text values are not unique and you could easily return an incorrect ID.

There isn't an issue with this lookup in my solution, as the text values in question are unique, and the text fields are set to only allow unique values - still I don't like the idea of a double relation.

Three approaches:

1. Popup menu. As you know you can format it to have a value list where only the second field is shown and the first is stored (the ID).

2. Layer two fields. Bottom field is enterable in Browse mode and is a drop-down list, ID and Text. Top field is related text value, not enterable. Validate (field option) to only allow values from value list (since users can type what they want in a drop-down list).

3. Popup window selector. With large lists, giving the user a popup window might be the best approach. This window could be a filtered portal or a list view with a script-triggered find. My link

4. Add a selector that helps narrow the choices (create a conditional value list). First choice is Country, second field's choices narrowed to selected Country's cities.

Thanks bcooney, will give your techniques a test, especially 2 and 3. I'm using a pop-up window in a couple of other places for assisted entry, so that may be a solution/compromise.

Popup menus aren't really very useful in large solutions as with anything more than a handful of values they are impractical, the lists I'm working on with have between 20 and 2000 data items.

I'll give the field layer technique a test, I was keen to avoid these sorts of fudges, having employed similar ideas in older Filemaker systems, but it appears there is little option. I'm stunning that a product at v11 has such limited value list options.

Still what is easy in Access is tricky in FileMaker and vice-versa...

[bcooney]

Thought of another approach. Rather than creating a new record, Invoice for example, and then asking which Client, start the process at Client.

That is, on the Client form, place a New Invoice button (pseudocode):

Set $clientID; Client::_kP_ClientID

Go to Layout Invoice form

New Record

Set Field Invoice::_kF_ClientID to $clientID

[devices99]

Thought of another approach. Rather than creating a new record, Invoice for example, and then asking which Client, start the process at Client.

That is, on the Client form, place a New Invoice button (pseudocode):

Set $clientID; Client::_kP_ClientID

Go to Layout Invoice form

New Record

Set Field Invoice::_kF_ClientID to $clientID

I do have some "shortcuts" for this in several areas already, but thanks for the info!