Best Practices/Layout Technique -- How to get a list of people associated to an item?

[elo]

Context, I'm working on a system that has lots of People (table tPeople) and items (table tDocket), I relate the two using a third table (table mDocketsPeople).

Question/best practice request: What's the best way to help users select people to associate with a matter (or vice-versa). Assume both lists will over time have on the order of several hundred items.

What I've tried (talking from the perspective of a layout in tDockets having a portal into mDocketsPeople):

Pop-up menu: Works, but doesn't allow typing and easily gets out of hand past say ~50 items

Drop-down list: Displays the person_id instead of the nice human understandable value name. This is apparently a known issue from searching these forums. Display situation is intolerable for basic usage.

So what do people do?

I've looked at Nightwing solutions for lists where you do have two scrolling portals and a "move" button. I guess that would be viable but it feels like overkill.

Is there some trick to using the layout editor to make the drop-down lists work?

Other?

[David Jondreau]

In these situations here's what I do:

Set the field to the key and have a pop up menu display the name. Set the field to a button. Have a script go to a "Picker" layout that will pause and wait for user selection.

My picker layouts are based on the the table I'm searching and display the choices in list view. There's a global I use to set the find with another button that runs a script finding off the global.

The item choices are also buttons that, when selected captures the item ID to a global variable and resumes the paused Picker script, setting the field to the global variable.

Edited July 22, 2008 by Guest

[elo]

Thanks, sounds like there is no good built-in way so it is going to involve layering some extra layouts/layout work to do nicely.

[elo]

Thanks again for the reply, I ended up using the technique outlined in Ray Cologon's "FileMaker Pro 9 Bible" chapter 9 (pp 267-272)

Great book!

[David Jondreau]

And what technique is that? I don't (and others who may stumble upon this thread may not) have a copy of the book.

[elo]

Ok, I will do my best to explain it. I think it is a variation of what you proposed.

Basic idea: use a utility table with a relationship to a table occurrence (TO) of your preferred valuelist source use a script and some fields turned into buttons to help “launch” you back and forth to a new window showing a layout of the utility table.

More details:

(1) Let’s assume you have a tMatters table and a tPeople table that are related by mMattersPeople. I’ll use “id” as the primary key for each (mMattersPeople has matter_id and person_id keys).

I had been trying to create a value list of tPeople values as a drop down, but ran into the too-long to show/no-typing.

(2) New solution (simplified, I’m leaving out the filtering capabilities that “Bible” offers on the utility layout; but I also supplemented it to work on Many-to-Many relationships), add a table gUtility and relate it to the records in tPeople. So now we have a layout “People Picker (gUtility)” which has a portal showing tPeople items.

(3) On the relevant tMatters layout, say “Matters (tMatters)” add a button in your portal showing your mMattersPeople items call it say “Pick” and have it invoke a script “PeoplePicker” and pass any parameter in.

(4) Make sure you also name the Portal (object viewer) on the Matters layout say “PeoplePortal”

(5) Back on the “People Picker (gUtility)” layout, apply a button action to the field inside the tPeople portal so that clicking on the tPeople::Name field invokes “PeoplePicker” without any parameters. Set it to also “Exit” any running scripts. (You also need to change the field behavior to not allow entry into the tPeople::Name field)

(6) Wire it all together by writing the script, if the script gets a parameter then it displays the picker/no parameter it goes back and adds the chosen person:

If [ not IsEmpty(Get(ScriptParameter))]

  # called to pick ojects

  Set Variable [$$portalrow; Value:Get(PortalRowNumber)]

  New Window [Name: “Select Person”; Height: 400; Width: 400; Top: 10; Left: 10]

  Show/Hide Status Area [Lock; Hide]

  Go to Layout[“People Picker” (gUtility)]

  Enter Browse Mode []

Else 

   # called because used clicked on a person’s name

  Set Variable [$select; Value: TO_for_tPeople_on_gUtility::id] 

  # e.g. whatever the portal’s TO name is on the “People Picker” layout

  Close Window[Current Window] 

  # you are now back at your original window showing the Matters layout

  Free Window

  Go to Object [Object Name:”PeoplePortal”]

  Go to Portal Row [No dialog; $$portalrow]

  Set Field[TO_for_mMattersPeople_on_Matters::inventor_id; $select]

  # this assumes the TO of mMattersPeople is set to auto create records and all you need is to

  # plug in the inventor_id

  Set Variable [$$portalrow; Value: “”]

End If

Hopefully that’s sufficiently clear. The full solution in the “Bible” uses more global fields on gUtility to offer filtering using more complex relationships with tPeople that might help you narrow down the list (type first few letters of last name; or limit to only certain type of people (employees, vendors, etc.))