Autocomplete with a conditional /fiiltered value list

[Angus McKinnon]

I’ve been working on a database for a rental company, on and off, for quite a few years. Some of the answers to my previous queries here have been really useful in moving things along.

The volume of orders has increased to the point where we need to track things more carefully. The crucial bit is spotting shortages/overbooking, but it would also be useful to generate proper reports on hire volumes for each item, rather than relying on anecdotal evidence.

However, the actual "line items” on each order are just text fields that users can freely enter. We try and impose some discipline with consistent names for popular items, but there’s always a fair bit of deviation. (The difference between, say, “8ft ladder”,  “Eight foot ladder”, and “Ladder - 8ft”)   This starts to get messy when we are trying to conduct searches to check availability. 

What I’m hoping to implement is a list of autocomplete options that appear as users type in their line items. The autocomplete recommendations would ideally be sourced from a different table, which is essentially an inventory list. 

I can get a “live” autocomplete by using a drop-down list, and selecting “autocomplete using value list”, but this doesn’t seem to be selectable when I’m using a list based on a field in a related table. I also need to filter the list, based on what the user is typing.

Unfortunately there are enough custom entries required that I can’t restrict user’s ability to enter any text. The autocomplete should hopefully be a nudge in the right direction, without being so restrictive that it frustrates them. 

I’ve had a good read through the many topics on Value Lists, and I’m still not quite sure if what I’m aiming for here is possible or not. I’m also wary of what sort of overhead it’ll introduce. Speed is critical, because users are often taking down orders with customers on the phone. If something’s convoluted enough to cause a noticeable slowdown, that’d be bad news. 

[Angus McKinnon]

In typical fashion, I've blundered in with an attempt at a solution to this. I think I'm headed along the right lines, but have hit a bit of a roadblock. Here's how it looks:

The user entering line items has a text field. (Called Item_description)  I've got a script trigger set on keystroke. This script sets a global variable (called $$itementry) to be the same as item_description is at that point in time. 

Then, over in the Products table, there is a calculation field, based on PatternCount function, to see if each product record matches the $$ItemEntry variable. This returns "1" for any matches. 

I then use this calculation field as a match field for a relationship back to to the line items table, and use that to add a value list to the item_description field. 

 

That all seemed like it would make sense, but it isn't working. The script executes fine and I can see the variable being set in dataviewer. I suspect that the problem lies with the relationship back again, possibly down to the storage/indexing of the key fields? 

 

 

[Angus McKinnon]

I've tried a few other approaches and workarounds with this, but am still stuck with the same problem. 

I can get the results I want using a filtered portal, but this lacks the convenience of autocomplete. A calculated field (based on the patterncount function) can't be used in a relationship. 

The one other workaround I came up with was triggering a looping script, which inserted a calculated result into a field in each record on the Products table. This field is then used as the match field for the relationship. This allows the value list for autocomplete to be set up, but it's a very convoluted method and has a number of drawbacks. Firstly, it's far too slow, taking about 3 secs to complete, which is a long time to wait between keystrokes. It'll only get worse as more records are added in future. 

Also, if multiple users are typing at once, they will both trigger the same looping script and alter the match field, which could cause some confusing results. 

 

So I'm more or less ready to conclude that what I'm trying to do is impossible, as it stands. Annoyingly, the actual functionality is fairly common in other places (both online and other programs etc.) so my users are half expecting that I should be able to make it happen by just ticking a box somewhere. 

Next step is to look for alternatives that provide the same "steering" towards the correct terms. Possibly some kind of pop-up or popover portal with suggestions? 

[Ocean West]

https://www.soliantconsulting.com/blog/filemaker-search-executesql

This is a nice way to auto complete / type ahead.

and another

https://www.teamdf.com/blogs/building-a-super-slick-search-ui/

[Angus McKinnon]

Thanks - I'll spend some time digesting both links. 

I'm only needing to search based on one field which hopefully simplifies things somewhat. 

[comment]

Why do you think that your original goal:

On 1/30/2019 at 7:59 PM, Angus McKinnon said:

What I’m hoping to implement is a list of autocomplete options that appear as users type in their line items. The autocomplete recommendations would ideally be sourced from a different table, which is essentially an inventory list. 

is impossible?

 

 

AutoComplete.fmp12

[Angus McKinnon]

Thanks, comment - it's useful to look through the file and see how it's been done. 

My problem is that I'm trying to do more than just a straight match relationship. The goal is to catch when users are entering an "incorrect" item name manually, and use the autocomplete feature to steer them towards the "correct" item name. This gives us the consistency that we need (to be able to search on items to work out availability etc.) but also doesn't upset the current workflow too much. If I can implement it as I'd hoped, it should actually speed up data entry for users. 

Autocomplete as standard only works with the start of a string, so, using your example, if the user starts typing "hand" it'll offer "handle" but not "door handle" as suggestions. This is where the calculated field using PatternCount came in, as it would match anywhere in a string. But this unstored calculation seems to be what's making the relationship unavailable. 

[comment]

There are ways to create a relationship based on a partial match (e.g. by breaking the entries into lists of individual words). But I don't think there's a way to use this for auto-complete.

[Angus McKinnon]

Thanks, that's very useful to know. 

Do you think it's better to base a relationship on a partial match, or stick with the PatternCount method I've been using? 

I also now need to decide what sort of alternative method I can come up with for nudging the users towards the correct names. At the moment the items are added in a portal from the main orders table, and I wonder if I'll have more options available if I make a new layout based on the items table? That would, for instance, allow me to use a portal showing matched terms since the autocomplete is no longer an option. 

[Ocean West]

Ideally if you had FM 16 or above you would have ability to use Card Windows anywhere you can open a new window in list view and then use quick find or a global and scripts to find records on the table. No need for portal at all.  

 

 

 

[Angus McKinnon]

Alas, we're not on FM16 yet, and to upgrade we'd also need to replace quite a few of the client machines, so it's probably out of contention for now. We will need to make the jump eventually though - I'll keep it in mind. 

 

[comment]

2 hours ago, Angus McKinnon said:

Do you think it's better to base a relationship on a partial match, or stick with the PatternCount method I've been using? 

I am not sure I understand the question. I thought you said the PatternCount method didn't work for you.

[Angus McKinnon]

I can get the PatternCount method working in a portal, but not as an autocomplete. 

I've given up on getting an autocomplete happening the way I'd like, it's just a case of what's going to be best for whatever alternative solution we end up using. Having a portal with the "correct" choices appearing alongside would probably be the simplest option at this point I think, unless there's some other possibility that I'm overlooking. 

[comment]

If you want a portal, then a filtered portal using an unstored calculation will be much easier to set up then a partial-match relationship. OTOH, it will also be slower, though you might not notice this until the number of related records grows from hundreds to thousands (YMMV). 

[Angus McKinnon]

Thanks, I'll give that a try. The number of products is unlikely to exceed a couple of hundred so hopefully won't cause too much of a slowdown. 

[Angus McKinnon]

Just to "close the loop" on this, I've given up on getting the autocomplete to work, as I'd originally hoped. However going through the process of attempting it has sparked off some ideas for a better item picker layout, which the users far prefer. 

Rather than trying to encourage them to correct the item names as they go, I've got a script set up so that the administrator can quickly skim through any items that aren't properly set and marry them up with products. 

The end result is altogether better than it would have been if I'd been able to wave a magic wand and fix my original challenge. So the process has definitely been worthwhile.