Find by key, using index built up on 2 fields

[BDennis]

Hi,
I'm trying to use the find by key action script to do a find on an index which is built up from two fields; "firstname" and "lastname"
I input variables via a dialog box, but I don't see how to use both the variables to do the search on the table.

Brian.

[Robert Adler]

RE: Find by key, using index built up on 2 fields

Brian:
I have pasted the sample from the help.chm. I added the second field to the search.

"tbl".fetch_find():

dim tbl_product as P
dim indx_1 as P
tbl_product = table.open("c:\data\product.dbf")
indx_1 = tbl_product.index_primary_put("id_index")
rec = tbl_product.fetch_find(inv_item-"product_id+inv_item-"Field2)
if (rec " 0) then
on_hand = tbl_product.qty_stock
end if
tbl_product.close()

I hope this helps.

bob adler

[BDennis]

RE: Find by key, using index built up on 2 fields

Hi Bob,
Thanks for the response.
I was hoping that I am missing something, and that it can be done simply with action scripting. It must be a fairly common situation ?

Brian.

[Tom Cone Jr]

RE: Find by key, using index built up on 2 fields

Yes, it's fairly commonplace. The Find by Key "Action" will permit you to define the search key using an "expression". Since you can't use a single variable and must combine them, use the "expression" option and specify

var1 + var2

as the expression. The expression must evaluate to the same expression used to define the index key in the table. This can be a bit tricky sometimes so let us know how it goes. Spare no details.

-- tom

[Robert Adler]

RE: Find by key, using index built up on 2 fields

Brian:

Attached file based on Customer.dbf in Alpha Sports. The script named Brian contains a search based on two fields input using variables.
The search is not calling the index directly but it is using two variables. I can't confirm if Alpha is using the Index.

The second is the one that comes with alpha sports.

I hope this helps.

[BDennis]

RE: Find by key, using index built up on 2 fields

Hi Tom,
It seems that in this kind of expression the field length is important.
I can't get it to work from inputing two variables normally.
Using manually "find by key" on a "lastname"+"firstname" index, on a test form, it leaves a space after the lastname input, before the firstname input, which seems to correspond to the length of the lastname field. If I respect that spacing, (my input + spaces = field length), then the find goes ok; otherwise not.

So, maybe I need an expression which limits the maximum input on each variable to the length of the field ( not normally a problem) and fills in any spaces to make the total of the input+spaces to be that of the field length.

Does that make any sense, if so would appreciate any simple, if possible, suggestions.

Thanks for your help.
Brian.

[Tom Cone Jr]

RE: Find by key, using index built up on 2 fields

Brian,

DBF tables use fixed width fields for character and numeric data.

If your lastname field is 20 wide, and you input "Smith", the actual field value is "Smith" plus 15 blank spaces. This means if you define an index as Lastname + Firstname, Alpha uses the entire field width for both Lastname and Firstname in the index key. You wind up with something like:

"pre"
"SMITH JOHN "
"/pre"

This is the index key for a Lastname + Firstname index.

A common solution to this is to trim blank spaces in the index definition:

trim(Lastname) + trim(firstname)

So the new index search key would be:

SMITHJOHN

This might work well for you here.

-- tom

[Tom Cone Jr]

RE: Find by key, using index built up on 2 fields

Brian,

DBF tables use fixed width fields for character and numeric data.

If your lastname field is 20 wide, and you input "Smith", the actual field value is "Smith" plus 15 blank spaces. This means if you define an index as Lastname + Firstname, Alpha uses the entire field width for both Lastname and Firstname in the index key. You wind up with something like:

"SMITH               JOHN          "

This is the index key for a Lastname + Firstname index.

A common solution to this is to trim blank spaces in the index definition:

trim(Lastname) + trim(firstname)

So the new index search key would be:

SMITHJOHN

This might work well for you here.

-- tom

[BDennis]

RE: Find by key, using index built up on 2 fields

Hi Tom,
Yes, you are correct. That was the way to do it.

Now I have to throw in the problem of accented letters on the index, but I'll start in on that next week. I need to do some searching on this board for that info.

Thanks,

Brian