Help with Value list

[rkass068]

Hello, I am reading on the forums here and I cannot seem to wrap my head around dynamic value lists in the way my search is set up on my layout for my db. 

 

My database consists of multiple tables, only one is significant in my question. My table designated as "main" contains the following relevant fields

 

parental key: pk_main

field 1: Last name (text)

field 2: first name (text)

field 3:Full name (calculation=last+first name)

global field: Patient search

 

On my layout I have every letter of the alphabet displayed as a button. When I click it it searches the first letter of the patients' last names and does a find. It works great.

 

I have a global field that searches the patients in a popup box. This also works

 

What I want to do is, if i search by a letter ex. "A", i want the popup box to only display the patients that have the last name that starts with an "A". right now the popup box displays every patient (over 700). 

 

Is there a way to do this? I tried looking into dynamic value lists and tried looking up having a value list based on a calculation. Any help would be great

 

Thank you!

[eos]

Try this one out. Hope it helps.

PopupByLetter_eos.fmp12.zip

[Josh Ormond]

So you want to filter the search results of your pop-up, using the letter selected on the other layout, correct?

 

As long as you are using field local to the table that are indexed, I would likely just run it as 2 finds. At least from the description, I'm not sure how the value list comes into play. What are you using the value list for?

 

Using Constrain Found Set, you can reduce the found set down to what matches the search.

[rkass068]

Try this one out. Hope it helps.

Eos that is exactly what I want. Thank you. I will fish through that file and see how I can do it in my DB. If I have any problems could you explain it to me? thanks so much for the help 

[rkass068]

Try this one out. Hope it helps.

Thanks Eos works perfectly! 

[ron G]

I need a popup that, when the user types the first letter of the last name, will jump to the first name with that letter.  I saw this a while back but don't remember where.  Anyone got an idea?

[LaRetta]

I should mention that you can completely eliminate that calculation in People which holds the single character.  Here is how using the file from Eos, example attached as well:

 

Set the gLetter global field to Auto-Enter (replace existing value) with

Let ( x = GetValue ( Self ; 1 ) ; List ( x ; x & "z" ) )

gLetter then becomes (for example) if you type a 'b'

 

b

bz

 

Then change the relationship to

 

gLetter <= People::LastName

and

gLetter >= People::LastName

 

Note that the single gLetter global field is being used to filter both sides and neither is there a need for a second field to hold the letter & z for the backend of the filter.  I know it is only one indexed calculation but that calc is only used to provide this functionality and calculations can slow your solution (all fields are downloaded from server) AND unnecessary calcs clutter your field definitions.  

 

Ooops forgot the attachment.  

PopupByLetterMOD.fmp12.zip

[LaRetta]

Sometimes it is irritating to the User when they select a letter and get no results.  Instead you can KEEP that calculation I told you to ditch and base your value list on it instead of a Custom Value List so all options in the pop-up produce results and eliminates the need to trap for no records found.

 

But really, with 12, you can use ExecuteSQL() and dynamic value list to generate the single-character list.

 

Options abound with FileMaker.