Someone asked me recently if it were possible in UNIFACE to perform database lookups as the user enters each character into a field, and if it finds a single match to retrieve it automatically. After supplying him with the answer I thought I would share it with the whole UNIFACE community.
For the purposes of this example the database entity I am dealing with has a single primary key called PKEY. In my test form I changed the widget type to UNIFIELD as my editbox has autoselect=on - this causes all current characters in the field to be selected, which means that when you type in a new character it loses all previous characters.
I changed the <start mod> trigger of PKEY to be as follows:
macro "^NEXT_FIELD^PREV_FIELD^END_LINE"
This forces the <leave field> trigger to be fired, then returns to the field and moves the cursor to the end of the line. Without this little trick the <start mod> trigger is only fired on the first modification to the field, causing all subsequent modifications to pass by unnoticed.
I changed the <leave field> trigger of PKEY to be as follows:
$save$ = @$fieldname ; save current input
lookup ; is this key complete?
if ($status = 1) ; yes
retrieve/e ; retrieve it now
else
@$fieldname = "%%$save$*" ; append 'GOLD*'
lookup ; how many records match this key?
if ($status = 1) ; only one
retrieve/e ; retrieve it now
else
message "%%$status matches" ; tell user how many matches
@$fieldname = $save$ ; remove 'GOLD*'
endif
endif
I removed the default code in the <validate key> and <leave modified key> triggers as they assume you are entering a new key rather than looking for an existing one, thus generating a 'duplicate key' message when a perfect match is found.
I tried this out on a table which contained the following PKEY values: PA, PAB, PAT, PATRICK, PQ, and PV.
When I type in the letter 'P' it tells me there are 6 matches.
When I add the letter 'A' it displays the entry for 'PA'. This is not the one I want so I fire the <clear> trigger which contains the following code:
clear/e PKEY = $save$ macro "^END_LINE"
This clears the current screen, sets PKEY back to its current value, and places the cursor on the end of the line ready for new input. If you do not do this you will get an error message saying that you cannot change the primary key on a database occurrence.
I then add the letter 'T' giving me a key of 'PAT'. This finds a matching occurrence, so it retrieves and displays it. This is not the one I want so I fire the <clear> trigger again and type in the letter 'R'. This retrieves the occurrence with the key 'PATRICK'.
As you can see this is not very difficult to implement, and it may provide a feature that causes your users to kiss your feet in admiration.
Tony Marston
11th September 2002
mailto:tony@tonymarston.net
mailto:TonyMarston@hotmail.com
http://www.tonymarston.net