rockman Posted March 15, 2013 Posted March 15, 2013 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: On Appointment Layout, have a drop-down that uses a Value List of the Client Names OnLeaveField run a script that performs an ExecuteSQL of “SELECT Client ID FROM Clients WHERE Client Name IS LIKE “ & [Drop-Down Value] If Result = “”, then no match. Display error dialog. If Result = 1 line, then unique match, put result into Client ID field of Appointments. 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
Rick Whitelaw Posted March 16, 2013 Posted March 16, 2013 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.
bcooney Posted March 16, 2013 Posted March 16, 2013 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
Lee Smith Posted March 16, 2013 Posted March 16, 2013 Automatic message This topic has been moved from "FileMaker Pro 12" to "Value Lists".
bcooney Posted March 17, 2013 Posted March 17, 2013 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.
Rick Whitelaw Posted March 17, 2013 Posted March 17, 2013 Hmmm . . . I must say I haven't noticed this. The selection of which field is visible is made in the Value List definition, no?
rockman Posted April 1, 2013 Author Posted April 1, 2013 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
Recommended Posts
This topic is 4501 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now