find as you type code

vasilis6669

Hi,

1) Can someone explain how to implement find as you type in a form search criteria.
For example, I have the customer search field and i want to search for all customers containing the word 'george'
i used setfilter(fieldname,searchvalue) and it only returns exact match. i want the program to return results of %george%

2) How can i display unique values of a column in a drop down list from a table?


Thanks,
Vasilis

Luc_VanDyck

1) SETFILTER(Fieldname,'*@' + searchvalue + '*')

2) a drop down list is populated at design time, so you can't use the values of fields in a drop down list (= option field)

vasilis6669

Hi,

2) for example post code has another column names city name. Do i have to create another table to store city names to be able to display them in an option field?

Thanks,
Vasilis

kriki

vasilis6669 wrote:

Hi,

2) for example post code has another column names city name. Do i have to create another table to store city names to be able to display them in an option field?

Thanks,
Vasilis

The option-field-values are defined at compile-time and not at run-time. So it is impossible to change them programmatically. So no need to create an extra table to store city names.

If you want to show unique values for a field in a lookup form, you have to program it yourself. When someone hits F6 in the field, you read the table and put all unique values in a temptable. This table you can then show in your form to have the user select a value.

This is +- the code (example on City of table Post Code):

recPostCode.RESET;
IF recPostCode.FINDSET THEN
  REPEAT
    CLEAR(tmpSomeTT);
    tmpSomeTT.City := recPostCode.City;
    IF tmpSomeTT.INSERT(FALSE) THEN ; // if a value exists, it will not give an error on the insert
  UNTIL recPostCode.NEXT = 0;
IF tmpSomeTT.FINDFIRST THEN ; // position on first record
IF FORM.RUNMODAL(FORM::"Your Form on TT",tmpSomeTT) = ACTION::LOOKUPOK THEN
  City := tmpSomeTT.City;

vasilis6669

Can you define the variable name tmpSomeTT?

Alos, in which part of the form i add this code?

Thanks,
Vasilis

kriki

-"tmpSomeTT" is the table you need (existing or new [no need to put in license because you only use it as a temptable]) to use as a temptable. In my example with City, you would only need a temptable with 1 field : City:Text50.

-Where to put the code: Again in my example: you would put that code in the OnLookup-trigger of each City-field where you want that functionality. Best put it in the table, not in the form.

vasilis6669

Hi,

I create a new table named tmpCity with a column city text 50.
Then on the lookup of the form I added the code mentioned above.

Now on the new form i added a text field with sourceExpr CityFilter(text 50)
on the table relation i used tmpCity. When i compile and run the form i cannot select from the list.

Can you assume what i am doing wrong?

Thanks,
Vasilis

kriki

-"tmpCity" is the name of the variable you need. It is for a record on table "City" and you use it as temptable (you need to put the property temptable on the variable).
-field CityFilter: you need to put the code in the OnLookup-trigger of the field.