Hi All,

 

I'm brand new to Filemaker so I apoligize if this post is confusing in some way.

 

In coming over from MS Access, I'm a little dismayed at how Filemaker handles it's down-down control. Specifically, that with a two field drop-down, the first field (usually an internal ID number) is displayed and not the second field that the user actually chooses from. I’ve seen a number of slick work-arounds like stacking fields on top of one another or suggestions to use the pop-up menu instead. It seems with the advent of the ExecuteSQL function in FM12 that another (perhaps better) work-around is an option. Unfortunately, my knowledge of FM scripting is not yet able to put it together. If you could help me, it may help many others looking to improve the drop-down control.

 

Table Structure:

Clients

     Client ID

     Client Name

Appointments

     Appointment ID

     Client ID

     Appointment Time

     Appointment Duration

 

Drop-down concept:

1. On Appointment Layout, have a drop-down that uses a Value List of the Client Names
2. OnLeaveField run a script that performs an ExecuteSQL of “SELECT Client ID FROM Clients WHERE Client Name IS LIKE  “ & [Drop-Down Value]
3. If Result = “”, then no match. Display error dialog.
4. If Result = 1 line, then unique match, put result into Client ID field of Appointments.
5. If Result = multiple lines, then non-unique match. Perhaps display a modal window to select Client from a list of the matches.

Is this doable?

Thanks for any help.

 

Jeff

You can have a drop down list, let's say based on ID, choose a second related field to display, and choose to show only values from second field. I do it all the time and it's native to FM.

Rick,

A drop down list will display the first value (the ID), after selection.

 

Popup menus truly hide the stored ID and display the second value.

 

I suggest popup selector windows or yes, layered fields.

 

hth,

Barbara

Automatic message

This topic has been moved from "FileMaker Pro 12" to "Value Lists".

Oops! Right, Popp list!

Rick.

Actually, Rick, it's either drop-down list or popup menu.

 

To the OP. Why not create a new appointment from the Client Form? If you do this, you do not need to ask the user for the Client ID - you have it. So, you'd capture $clientID, create new appointment and set the foreign client key to the $clientID.

Hmmm . . . I must say I haven't noticed this. The selection of which field is visible is made in the Value List definition, no?

Thanks go to Barbara and Rick for their thoughts.

 

While Barbara's suggestion of starting with a known Client Name/ID to then create the new Appointment record is a valid point, this is not always possible. For example, imagine a table that has related information from more than one related table.

 

I've worked on a solution using the ExecuteSQL command and have put together a demonstration file.

 

You can download a copy here: https://www.dropbox.com/s/ryfkpbciufiqxub/Drop-down%20Workaround.fmp12

 

This demo uses ExecuteSQL from FMP 12 so earlier versions of FMP can't utilize this workaround.

 

I would appreciate any feedback that you might have.

 

Jeff