Drop down, look up lists 2

[cobweb]

Hi there:

I am a big fan of Paradox...except for the look up lists!
I am setting up a paradox database where I want a drop down list on a field that reads one field from the lookup table but writes another...it is a 'reason for failure" so i want the drop down list to read to the user "broken in transit" but return to the field 'reason' in the master table the primary key of the reason table..which is a number.

I know Access does this thing sweet as pie; can paradox?The 'Ctrl & spacebar' routine is not what the users want!

Thanks!

 

[footpad]

cobweb,

Take a look at

http://www.techtricks.com/paradox/poplists.php,

which shows how to dynamically populate drop down combo boxes and selection lists in Paradox. It's pretty lengthy, but there are a number of ways to approach the problem and the article demonstrates several useful ideas.

Also, you might some interesting ideas in

http://www.thedbcommunity.com/code/tt/ttfilterlookup.htm

Also, I agree with you. Paradox's lookup handling has never been what one might call elegant. Fortunately, you can use ObjectPAL to create far more useful solutions that work for your users.

Hope this helps...

-- Lance

 

[LWB]

I have a related question about drop down edit lists. They are limited to 2500 items in a list. I have a drop down list that picks a name (although the user can enter a name that is not on the list and that is okay). This eliminates many of the typos from users typing in an entire name. However, as the size of the database has grown I have had to create multiple lists (there is an update button to update all the pick lists). They are divided into sections of the alphabet.

I have often thought there must be a better way. I'm not sure I fully understood the links, but it sounds like it might be possible to allow the user to enter one letter and based on that letter calculate on the fly a list that begins with that letter. I would want the list to automatically "drop down" after the first letter is typed.

Is this possible? How?

Lynn

 

[Kliot]

Lynn,

I have a few applications where a user needs to look up a customer name. I create a form that contains a table with existing customer names indexed on the name field, (be sure that the table is set to that index) I then have a entry field where they type in the name to search and I attach the following code to the keyPhysical property of that field:

method keyPhysical(var eventInfo KeyEvent
doDefault
customerTable.setRange(self, self + chr(255))
endMethod

As the user types the table filters down to match the criteria typed in. It's a really slick solution that is not restricted by size, I'm sure you can modify to do what you need, the key to making it work is the chr(255) in the setRange. If you have any trouble trying this let me know.

 

[LWB]

Kliot,

Thanks - your method seems like it should be slick, but I can't get it to work. Maybe it's because I don't really understand it. I get a general protection fault (in Paradox) when I try this. The field I want to look up is linked to another table in the Data Model - is this a problem? Should the field type still be a "combo" (drop-down edit box)? I've tried several variations on the look-up table, including creating an entirely new table with the name as the only field. All were indexed on the name. Should the table be in the Data Model?

Here is the code I used:

method keyPhysical(var eventInfo KeyEvent)
Var
MyTbl Table
endVar
doDefault
MyTbl.attach("lookup.db&quot
MyTbl.setRange(self, self + chr(255))
endMethod

So what am I doing wrong?

Lynn

 

[Kliot]

Lynn,

The way I use it is to give the user a lookup form where they search for an existing customer, if they find it they hit the enter key or press a button and the customer is selected, if it's not found they enter a new customer. It's not quite the method you are looking for but I think it can give you an idea for another approach. What you are trying to do sounds a lot like what they do in Quicken, when you start typing a name they fill in the rest based on past entries. I'll keep thinking about how you can do this, I think it's doable. You can contact me directly at perrin@lacis.com for more help if you need to.

 

[LWB]

Kliot,

What you are describing is pretty close to what I want. But either approach would probably work. I thought in the previous post that you were describing something like the Quicken fill in.

Actually the normal drop-end edit box operation is fine. The problem is that it is limited to 2500 names in the list, so I have to use a radio button control to change which list is used. The user needs to be able to enter a name not in the list.

Are you saying that you open up another window to display the list for the user to pick from? I guess I'm confused about how your code actually works.

Lynn

 

[Kliot]

Lynn,

I've got some code that should do what you need

On the entryField attach the code:

Var
tc tcursor
entryString string
endVar

method open(var eventInfo Event)
doDefault
tc.open("lookupTable&quot ; Attach tcursor to lookup table
tc.switchindex("cusnametindex&quot ; Switch to index on name field
entryString.blank()
endMethod

method keyPhysical(var eventInfo KeyEvent)
doDefault
if eventinfo.vchar() = "VK_BACK" and entryString.size() > 0 then
entryString = entryString.substr(1,entryString.size()-1)
tc.setRange(entryString, entryString + chr(255))
endif

if tc.nrecords() > 0 and entryString.size() > 0 then
self.value = tc.customer ; if a match is found fill it in
else
self.value = entryString ; otherwise accept entry
endif

endMethod

method keyChar(var eventInfo KeyEvent)
doDefault
if eventinfo.vchar() = "VK_SPACE" then
entryString = entryString + " "
else
entryString = entryString + eventinfo.vchar()
endif

tc.setRange(entryString, entryString + chr(255))
if tc.nrecords() > 0 then
self.value = tc.customer
else
self.value = entryString
endif

endMethod


What I'm doing is trapping for the characters the user is entering and filtering the tcursor table based on the entry and filling in the first match and continuing to filter as the user types. Once all matches have been exhausted I accept what has been typed as a new entry. The code is pretty crude and you will need to add error handling for things such as the "+" key etc. Let me know if this works for you or if you need more help I can e-mail working code to you.

Perrin

 

[LWB]

Perrin,

It works - sort of. The problem is that it translates everything to uppercase and my names are in mixed case. Is there a way to change this?

When I originally asked about this, I was thinking that if the code could get the first character the user typed, then run a query to generate the list of all names beginning with that letter, assign that list to the drop-down edit box, then open the drop-down edit box with the newly calculated list, it would be ideal. That way the user could either type or scroll down to where the right name was located.

Thanks for your help. I have never attached any code to keyPhysical or KeyChar. I need to read up on what they do.

Lynn

 

[Kliot]

Lynn,

Here is a solution that is closer to what you are talking about, I'm not crazy about using a query for speed and retry issues but it does work. This doesn't automatically open the dropdown list but that shouldn't be difficult to program in. Im not sure why you were coming up with capital letters in the previous method, strange....

method keyChar(var eventInfo KeyEvent)
var
q query
qvalue string
endvar

doDefault
if self.size() = 1 then ; only run on first letter

qvalue = self.value+".."

q = Query
ANSWER: RIV:ANSWER.DB

customer.DB | CUSTOMER |
| Check ~qvalue |

EndQuery

if not q.executeQbe() then
errorshow()
return
endif

#listbox.datasource = "[riv:answer]"
endif
endMethod


Have fun
Perrin

 

[LWB]

Perrin,

You are wonderful. It works just like I had wanted. But I have run into a few problems. Maybe you can figure them out.

One is I have not been about to figure out how to make the drop down edit box drop down under ObjectPal control.

More signficant is that I discovered that one of my lists still exceeding 2500. So I change your line:

If self.size() = 2 then ; only run on first two letters
qvalue = self.value+".."

This worked great, except I have run into a couple of cases where the query failed. It did not seem to have any trouble with queries with no results. The failed cases I found were when the first two letters were As or To. And the query errors were different. When I checked the data, the first records that began with As or To were followed by a space. But some other groups of letters followed by spaces worked. I think this may be that "As" and "To" are interpreted as query operators. Since qvalue would either be "As.." or "To..", I don't know why the query would think they were the same as As or To alone.

I'm not sure how to correct this. Any ideas?

Lynn

 

[Kliot]

Ah shucks, just trying to be helpful

To make the dropdown under objectpal use

#field.action(EditDropDownList)

I was afraid you might still run into the 2500 limit but it sounds like you found a good work-around.

The query failure is paradox interpeting the keywords as you suspected, put them in quotes to eliminate this problem.

s = "\""+self.value+"\".."

If you haven't done so I would also suggest you use a name other than "answer" for the query to prevent potential problems.

Perrin

 

[cobweb]

Thanks to all who have worked on this thread..but can I return to my first point, please?

I have followed Lance's advice (thanks!) and got some help; however if I could just get the lookup thingy to open without the dreaded CTRL&SPACEBAR my users will be happy (well, nearly)
Is this possible? Is it in the edit dropdownlist command that KLIOT mentions?

Thanks!

 

[Langley]

Look up tables and drop down lists are two different things. You are looking for something like:


doDefault
if self.isEdit() then action(dataLookup) endif


in the arrive event of the field.
Mac

"There are only 10 kinds of people in this world... those who understand binary and those who don't"

langley_mckelvy@cd4.co.harris.tx.us

 

[LWB]

Cobweb,

Sorry to have hijacked your thread. I guess I wasn't really asking about the same thing. Next time I will start another thread, even if I think they are related.



Kliot,

Thanks. You have been a tremendous help. That fixed the problem with the query. And the #field.action(EditDropDownList)command works.

But is there a command that just drops the box down without changing anything else? If the user does not click on the down arrow to open the drop-down edit box, I would like it to open after the first two letters are typed and the query has run. If I put the command in the code it resets the two letters already typed. And if the box is already open it does something else undesirable.

Lynn

 

[LWB]

Perrin,

I found part of my answer. I can use
Sendkeys("%{down}&quot
after the query is run to drop down the edit box. However if it is already down, it closes it. How can I test to see if the box is already in the drop-down state so I don't close it instead? I can't seem to find any property that describes the state of the drop-down edit box.

Lynn

 

[Kliot]

Lynn,

The problem with send keys and EditDropDownList is they will drop down the list if it's closed and close the list if it's open and I don't know of any way of testing to see if the list is already open before calling the action. Try moving the code to the arrive method of the field, it should work better but still not perfect. If Langley is still following this thread maybe he knows how to check the status of the list.

method arrive(var eventInfo MoveEvent)
doDefault
active.action( EditDropDownList )
endMethod

Perrin

 

[Kliot]

Lynn,

I just thought of another solution, on the action method of the list you can trap for EditDropDownList and keep track of it's status.

Asign a global variable to the field
Var
isListDown logical
Endvar

Attach this code to the list action of the the field

method action(var eventInfo ActionEvent)
doDefault
if eventinfo.id() = EditDropDownList then
if isListDown = false then
isListDown = true
else
isListDown = false
endif
endif
endMethod

Now modify your existing code like this

if isListDown = false then
action(EditDropDownList)
endif

now you will only be opening the list if it's closed.

 

[Kliot]

Forgot to mention to assign the variable in the open action

method open(var eventInfo Event)
isDown = false
endMethod

 

[LWB]

Perrin

Almost!!!! But something isn't quite right.

method action(var eventInfo ActionEvent)
doDefault
if eventinfo.id() = EditDropDownList then
if isListDown = false then
isListDown = true
else
isListDown = false
endif
endif
endMethod

The else statement in this code doesn't do anything. I put a MsgInfo into the first part and it executes fine if the user initially arrives on that field. I tried putting
isListDown=false
statements into both the depart and the changeValue methods for the field, but if the user closed the drop-down edit box without leaving the field and begins typing a new name the box does not drop down again. If the user leaves the field and comes back it works correctly.

I'm confused. It is so close to working correctly!

Lynn