Jump to content
Angus McKinnon

Autocomplete with a conditional /fiiltered value list

Recommended Posts

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. 

Share this post


Link to post
Share on other sites

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? 

 

 

Share this post


Link to post
Share on other sites

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? 

Share this post


Link to post
Share on other sites

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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.  

 

 

 

Share this post


Link to post
Share on other sites

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. 

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

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). 

Share this post


Link to post
Share on other sites

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. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.