long value list problem

[niessen]

I have a value list for medications in a surgery database that is huge. Is there a way for the user to type in a couple letters and have the value list show only medications beginning with these letters or to actively reorder the valuelist based on these entered letters?

Thanks for any help.

[PoweredbyFm7]

No, not possible. But you can use a new medications window. in this way, the user select what he want from the list. But for this you must use a script. This script, selects a medication from the list. You can find this method at "Business Tracker."

[Søren Dyhr]

Even with ver 5.0 do you have both the Clairvoiance technique and dynamic valuelist, but with 7 - is it much more conveninent for the developer to set up:

http://www.newcenturydata.com/downloads/filter.zip

Previously was it done this way:

http://www.filemakerpros.com/Clairvoyance.zip

But since it's a relationship keying matter, will dynamic valuelist behave accordingly - which means that some data entered in a field acts as a key to control the popups attached valuelist in the field next to or even layered underneath ...The greates problem would be- does such a userinterface gadget violate Apples Guidelines for a graphical layout ...and on a more superficial level should Jacob Nielsens guidelines for scanablilty userinterface be observed so no more than 10 items to pick from should ever be shown.

--sd

[niessen]

Thanks for the solution examples. I think I will just have the users enter the medications because any solution is more time consuming, given there are hundreds of medications to choose from.

[Søren Dyhr]

Wait wait - Take a look at the attached template it kind of works (with the userinterface reservations I had) the list is "endless" until a letter or more is entered in the field.

--sd

CentryTweak.zip

[niessen]

Thanks that works perfectly!!

[niessen]

I have discovered a little glitch. The medications field with the value list is in a portal and the user has to enter a letter then click in another field, then go back to the medications field before the list appears. The list works well if its not in a portal. Any ideas why this is happening? Thanks.

[Søren Dyhr]

Ah yes i think it's the new commiting issues...

http://www.fmforums.com/threads/showflat.php?Cat=0&Number=96708&page=&fpart=1&vc=1

....you might consider a scratchpad'ish approach - that is construct and modify a record outside a portal - say in an extra window floating above the window with the portal.

--sd

[niessen]

Thanks. I'll try that.

[bdam]

Soren,

I like what you did in your Tweak example here and I'm looking to use it for a long list of Author names. In my case I will just use a single calculation field rather than separate first and last name fields. Also, I want to show the full name but actually store the Author's ID so my value list is setup appropriately. However, I'm coming up against the issue of having to enter the filter text and then exiting and re-entering the field. Can you expand a bit on what you said here?

....you might consider a scratchpad'ish approach...

thanks,

Bryan

[bdam]

Spent some time and created an example.

Books.fp7 - database where information on a particular book will be stored. In order to select an Author we have a value list of Author IDs while only showing Author's full name. However, given the large number of authors (5494) the value list acts slowly in a server environment. Is there a way in the Book database to start typing the author name and have the value list filter itself? This would avoid having to load the entire list if the user can enter in the first few charachters of the Author's name.

Authors.fp7 - the database where author names are stored.

Relationships - The Books database relates to the Author database based on an Author ID field which is numeric.

I present these as separate files since in my case this is the setup that will be used. We will utltimately pull from the Author database in several other databases.

filtered_vl.zip

[Søren Dyhr]

Before I dig deeper in this issue, being somewhat slightly off what I would put on a layout, should you perhaps read about these two workarounds:

http://network.datatude.net/viewtopic.php?t=237&sid=3d8c94526a8f270082c3054d7e58028a

But what I meant was that since commiting the record, have changed behaviour should such entries be performed in an extra window opened via a GTRR command in each line.

But today would I more likely be solving interface issue by a mixture of a tabbed layout with letter ranges as well as using stuff from this:

http://www.fmforums.com/forum/attachment.php?attid/7439/

--sd

[Razumovsky]

Keep in mind that if you are even remotely considering upgrading, this is just a matter of selecting Auto-type based on a value list for the field in FM8.

-Raz

[Søren Dyhr]

Yes but would you use it??

--sd

[Razumovsky]

I don't quite understand. I think I would definitely use it if I wanted

to type in a couple letters and have the value list show only medications beginning with these letters

This seems to be exactly the task it was designed for.

[bdam]

Attached is a new file that I've worked on this afternoon. It shows the three different techniques discussed here: Value Lists, Tabs & Portals, and Scripts & Portals. Since I plan on testing this with my users I've added explanations in the layouts. If anyone can suggest a better way to implement any of these techniques I would love to hear it. I'm not concerned with how it looks, that's easy; just how it functions.

this is just a matter of selecting Auto-type based on a value list for the field in FM8

I'm confused by that you are refering to here Raz. Where does auto-typing come into play here?

Bryan

filtered_lists.zip

[Søren Dyhr]

It is, but I wonder if you ever use it as feature? In 5 and 6 did i indeed use keyfields in text format, but never ever since, not even pre- or postfixed, since theta-joins are defined by mutlicriteria these days.

If you take a look at HUI how comboxes should look like, and compare it with filemakers dito, does it leave a lot to be desired ....except when serving via IWP does it resembles the ideal.

http://developer.apple.com/documentation/Cocoa/Conceptual/ComboBox/ComboBox.pdf

...the point is that all measures should be as recognizable as posible, not candidates for guesswork seen from the users point of view.

--sd

[Razumovsky]

Well, I did just recently upgrade to FM8, so I have not actually used this feature for any clients databases. However, it does seem to be precisely what is called for here, and much simpler to implement.

Bryan,

I added the method to your file. Remember that it is only good for FM8 and above.

filtered_lists2_Folder.zip

[bdam]

Ahh, you mean auto-complete. When you say auto-type I think object/field type such as text, number, date.

Auto-complete looks pretty cool but Soren is right, when will you actually use it given its restrictions? There's a major problem with the auto-complete feature: it doesn't work with value lists showing values from a second field. FileMaker states this right in their description [filemaker.custhelp.com] of the new feature. Clearly both auto-complete and show a second field are two important value list features but they don't work together. That makes one of them more or less useless: close, but no cigar.

Bryan

[Razumovsky]

There's a major problem with the auto-complete feature: it doesn't work with value lists showing values from a second field... Clearly both auto-complete and show a second field are two important value list features but they don't work together. That makes one of them more or less useless

Now hold on a second here. Yes, it would be great if they worked together, but your evaluation of the scenario appears quite dire. Why is this a 'major problem'? Why does this make one of them useless? No functionality was removed when they added the ability to auto-complete on a value list, so, if value lists that display a different value than they return were useful, they should still be useful. If having the ability to auto-complete based on a value list is useful - it should not matter that it can not display a second (or third or fourth) additional value. Displaying or working with a proxy value has always been an easy thing to do with autocalcs or relationships or lists. Auto-complete has not. Getting around the "display values from a second field" is really no big deal at all (remember, they just introduced that option in FM7. Did that make all past value lists useless?)

As far as the "no cigar", I dont get it. Did you look at your file? Does the method not accomplish what you were requiring more elegantly and with less effort than the other ones you were trying? Could you elaborate on why you think it is not appropriate?

Again, I haven't put this feature to work yet, and would like to be aware of all the limitations and possible conflicts. But really, this is a pretty minor issue I think.

-Raz

[bdam]

Admittedly, it was a bit harsh to call auto-complete entirely useless. However, the two features in combination are so much more useful. Let me explain. The whole reason for auto-complete is that you have a really long list of things that you don't want to manually sort through. Small lists are by definition small and don't warrant its use. In my databases almost all value lists of a significant size are being populated via relationships to other tables. Since my personal preference is to avoid relationships based on text fields I make full use of numeric ID fields and FM's 'show only second value' option to hide them. Of course these are my personal preferences and most of my databases are in or are soon going to FM8 so I make heavy use of that feature.

In regards to the file you posted I should apologize. When I first looked at the file I didn't notice the layout you added. I got used to using the buttons to change layouts. My bad.

What you've essentially done is made a circular relationship. First you use a relationship based on the name field to lookup the ID. Then you use a relationship based on that ID to get the name and other related info. The first problem is that this requires both author name fields (local and related) to be displayed. Of course I can visually get around that by using the method Soren points to above. However, the user would only be able to enter the local version of the author name in browse mode. That means that you can only copy and paste from the local name field stored in the Books database. What happens when we change the author's name? While you may think that's a non issue for author names they do in fact change. Regardless, this becomes an issue with other things that do change often (ie: book titles). When the author name changes in the author database the local copy in the Book database obviously doesn't get updated. Since that is the only name field the users can copy and paste from you have a problem. Refer to the attached file to see this in action. "BRYAN STITT New" is the correct name but you can't highlight it in the current solution. So in essence you are forced to have the names on the layout twice which is obviously its own concern for our less discerning users.

My "close but no cigar" comment isn't in reference to the file you posted. It is in reference to the fact that we have these two great features that are very powerful but can't be used together in a direct manner. The implementation is so close but not quite all there in my opinion. How hard would it have been to go that last 10% and make auto complete work for all value lists?

Bryan

filtered_vl3.zip

[Razumovsky]

OK, I understand that you would have to have the local customer name and the related on the same layout, but how is that much different than having the local Author ID on the layout in the other version? You could also acomplish the same thing without the additional relationship using lists. Plus, why do you insist that:

However, the user would only be able to enter the local version of the author name in browse mode

Why not just enter, copy, and paste through the related field in the modes? Why not use the related field for display? The problems you are describing are entirely workflow considerations, and have nothing to do with the ability to auto-complete based on a value list - that has been natively provided.

While I agree that it would be nice if they kept the 'display second value' with the auto-complete, a little imagination could probably accomplish everything you need.

Edited May 31, 2006 by Guest

[bdam]

how is that much different than having the local Author ID on the layout in the other version

While it may be a non-issue to many the difference is that I already have the author_id field in the books database which is why I used it like that. Here you had to create another name field in the database thus duplicating data. Not that the extra space is a big issue but it makes it more difficult to remember in a year or two why there are two name fields. The entire reason for the author database is to have a central location for things like author's names, addresses, and the like. Again, no big deal, but I always strive to limit the number of fields, scripts, and calculations I have kicking around to try and make life simple.

Why not just enter, copy, and paste through the related field in the modes?

Bah, some day I'll learn to write less confusedly. By 'enter' I didn't mean create a new record or enter a new author as that will always be done in the author database itself. By enter I meant the ability to enter the field in the layout with your cursor and thus being able to highlight and copy the field contents. In order to make the layered name field and list work I had to disable cursor entry into the related field so that the value list pops up. This works because the value list is the only one you are allowed to enter you cursor into while in browse mode.

If I understand the workflow you suggest I would have to use GTRR or something like that in order to copy and paste from the author field. While certainly workable from a designer perspective I would never get that into the user's heads. I have people who struggled recognizing hyperlinks in Word documents so I can't require multiple layouts just to copy something that is right in front of them. If they see it then they must be able to copy it; at least that's what they will intuitively assume.

Raz, thanks for walking through this with me. I'm not trying to be argumentative; this issue is just very important to me because I'm going to be using these techniques about a hundred plus times in the next few months as I redesign our databases recently converted to FM8. The things we're working on here I plan on using on a whole host of other lists that don't involve authors.

Bryan

[Razumovsky]

I did not mean to suggest any particular workflow, as I didn't have many details on what you are trying to do, or understand the reasoning behind it. I gather that your situation is roughly tracking books and author names. In this case, wouldn't it be good to have the book author name be a lookup? If an author changes their name or writes books under a penname later on, you would not want the earlier books to reflect that change, but still want them linked to that author record.

Does the Author ID really need to be seen on the book layout? Do users ever search by author ID , or would the name be sufficient? Do you want a drop down menu to appear everytime a user clicks in the author field to copy it (also risking accidently switching the author)? How about adding a little arrow button to the right of the field that would pull up the authors drop down list? If you did not want it as a lookup (if you wanted the authors name to change on each book with a single modification in the Author table), you could simply place a related field from Author table with the name in it (no GTRR, no layout change, just a simple author::name field).

I am not defending FM for not joining these two functions. What I am saying is that because FM does not come out of the box tailor made to your specific workflow is actually kind of a good thing (for me and everyone else with different requirements). I suspect if you carefully consider your needs, you could adapt the auto-complete feature with much less hassle than you seem to believe.

I have learned a great deal on this forum by being 'argumentative' when I do not quite understand someones point, no need to worry.

Cheers,

-Raz

[bdam]

Bah, had a response all typed up the other day and closed the browser accidentally.

Anyhow, the Book/Author example is just that: an example of where I need to select something from a large list. Depending on the case I may or may not want it to use lookups to record historical data vs. showing most current. I may or may not want to use the arrow button. In some cases I will want to show the ID field and in others I will want it hidden.

At the heart of the matter is being able to select an object (author or what-have-you) from a large value list.

So here are the criteria as I can best describe:

-Can handle thousands of objects reasonably quickly

-Auto completes or filters

-Can be based on lookups or related fields using a secondary ID field for the relationship

-Must be able to enter, highlight, and copy field contents being shown (no mystery meat)

-Field should only be shown once on a layout to avoid confusion

From everything I've seen all of these cannot be accomplished in tandem: you have to pick and choose. If you can achieve that I would love to see an example.

Bryan

[Razumovsky]

-Can handle thousands of objects reasonably quickly

-Auto completes or filters

I assume that the auto-complete function does this acceptably for you?

-Can be based on lookups or related fields using a secondary ID field for the relationship

It is easy to relate one field to another outside of the valuelist dialog box. You could create a secondary relationship, or parse the correct data from a list using autoenter do not replace.

-Must be able to enter, highlight, and copy field contents being shown (no mystery meat)

-Field should only be shown once on a layout to avoid confusion

These are basic layout features. Have only one instance of your field visible and enterable.

I still do not get the issue, unless by "in Tandem" you mean exclusively within the define value lists dialog boxes, in which case you are correct. If this is not the case, could you let me know which step is bogging you down?

-Raz