How can I ???

[rmssoftware]

How can I set a field to look up data from another table without the drop down look up showing. I want the user to be abler to type the value in the field and have it fill in other fields wothout having the ability to use the look up feature. I have used closest match before but the problem is just that. If the value in the list does not match it picks the next closest value and fills in that information. I need an exact match and fill i n or no match.

[Cheryl Lemire]

Re: How can I ???

Hi Fred,

Will force pop up on mismatch work?

[MikeC]

Re: How can I ???

Fred,
Don't think it can be done within field rules....but

You can use one of the lookup() functions with a variable as the field. If needed I will dig up the example Tim Kiebert gave me to show how it is done.

[Tim Kiebert]

Re: How can I ???

Fred,

As Mike mentioned you could use the lookup() functions based on the value the users enter into the input field.

At the form level you would code the OnChange or OnDepart events of the input field. The code would either set the value of the field objects on the form or set the field values at the table level and then refresh the form.

At the table field rule level you could use the OnWrote field event to populate the other fields , again using one of the the lookup functions to gather the data.

I would first test for the existence of the key value in the table you are looking up to and if not there then end the script. You can use A5_GET_RECORDS_IN_QUERY() or Exist() functions for this.

[MikeC]

Re: How can I ???

Fred,

In addition, instead of just having the script end if it did not exist is to have the script open a form to look for the value or to create it....like the field rules would do for you with the "Popup on Mismatch". Works great and you can customize any error messages this way which you cannot using field rule.

[rmssoftware]

Re: How can I ???

WOW! Grate ideas! I have never learned to us much X basic yet but am going to start. For years I've been able to create some great apps using just the standard Alpha wizards and field rules. Nut times are a changing. I'm going to try to use each of these ideas and see which one works best. This is what maakes alpha so great and the most powerful feature of Alpha is the user group.

I'll let you know whice one works best.

[Tim Kiebert]

Re: How can I ???

Fred if you are used to using action scripting then start there using some of the object properties actions to set the values. Use the lookup functions within the actions as the expression for the value.

Then check out the xbasic behind it. It really isn't all that difficult. And since you have been using Alpha for a while you will probably have a good idea whats going on when you see it.

Good Luck

[rmssoftware]

Re: How can I ???

Thanks Tim,

I will try that.

Cheryl, I did try the "Pop up on mismatch but it still showed the drop down. I just wish there was a way to disable the drop down from showing but yet use the look up function.

My goal is to let the user enter a number in a field that should match the same number of a product in another list. If the numbers match then the app would use the look up function and fill in the other fields as well as tye part number field. If no match then they would enter a number and have to fill in the information manually. The field should only be populated when the numbers in both tables match.

[MoGrace]

Re: How can I ???

Thanks Tim,

I will try that.

Cheryl, I did try the "Pop up on mismatch but it still showed the drop down. I just wish there was a way to disable the drop down from showing but yet use the look up function.

My goal is to let the user enter a number in a field that should match the same number of a product in another list. If the numbers match then the app would use the look up function and fill in the other fields as well as tye part number field. If no match then they would enter a number and have to fill in the information manually. The field should only be populated when the numbers in both tables match.

Quickbooks works this way when you enter in the Cust ID in an Invoice. You can pop up the list if you want, but it shows the closest matching Cust ID as the user types it. The problem with a product id is that it would not be as familiar to the user as a cust id might, and the user could accept the wrong item.

So it would depend on whether the user already knows the product id (working from a hard copy) or if they must first find it. And if they type in the wrong number, will the user be able to know it from the filled in data so they can correct it? The solution would seem to be where ever the least possible errors could occur. That is more important than the speed with which the user can enter the data.

Another idea:
Is there a field the user can fill in before the product id that could be used to limit the possible matches? I am thinking of a TYPE field perhaps? In the field rule OnWrite event for that field you could set a global variable that could be used to filter the lookup for the product id.

[rmssoftware]

Re: How can I ???

Thanks Robin, yes that is exactly the problem I'm facing. The user simoply types in the products code number and when it matches the next closest code he just hits enter and accepts it without understanding that it is wrong. So What I was hoping for is to have a field rule (lookup) that would fill if there was a match and not if no match and did not show the drop down icon that lets the user open the table. The goal is to stop the user fromopening the look up table and yet still allow the field rule to fill on a match. Still a lot of great ideas. here.

[seaken64]

Re: How can I use lookup without a pop-up???

Fred,

sorry to be a PITA but I wanted to remind you and all onlookers that your title for this thread is no help at all in suggesting the topic. I skipped it several times but eventually clicked on the topic out of curiosity. I learned from the thread and am glad I took a look.

This is a general reminder for all of us who use these forums. Please remember to title your topic in a way that indicates the subject. In this case, perhaps something like "How can I use lookup without a pop-up?"

End of Moderator-speak.

Sean

[DaveM]

Re: How can I ???

Fred,

try this in field rules. If you type in the field a valid entity, it will fill the other fields. If what you typed in does not exist, it will popup. It uses a premade browse.

[MoGrace]

Re: How can I ???

Fred,

try this in field rules. If you type in the field a valid entity, it will fill the other fields. If what you typed in does not exist, it will popup. It uses a premade browse.

Dave, I don't think that will help Fred cause it will not let him enter a new value if there is no match found. What he needs is a script that checks the data entered against another table and then prompts the user to enter a new product id if none is found. Then a lookup can be offered to him or he can continue with a new product id.

It would help to see the tables...

Edit: Actually I see that you have permitted records to be added to the lookup table, but I think he wants to do that on his form.

[DaveM]

Re: How can I ???

we do enter new zip codes all the time this way. It allows enter and edit from the form associated form on pop up.

If a zip code(in my case) happens to reference the wrong county(in FL my case), sales tax can be wrong. It is sometimes necessary to change the county and sometimes city or add a new zip code if it does not exist.

a shortcut sometimes taken is if the zip exists wrong, you can simply overwrite the filled in fields(not the lookup field) and go on about your work. If you go back into the lookup field it will revert back to the old data unless you change the info associated with that zip.