Not able to select second Mr smith in lookup table

[werder101010]

I'm probably overlooking something! I use a table lookup to lookup addresses. Linking field is the name of the customer. �Unique records only� is not selected. I use no validation for the customer field name.

The lookup table contains more than one mr Smith. I'm not able to select the second mr smith. It always shows / selects to the first mr Smith from the lookup table. I can't find the solution on the board or in the helpfile. What am I overlooking?

[DVSOL005]

Re: Not able to select second Mr smith in lookup table

Werder,

I had this problem only a couple of days ago.
Do you have separate fields for lastname and firstname?
If so, there you will find the key to the solution.
In the display order field of the lookup I used lastname and only the first records showed up in the lookup.
My solution was: alltrim(Lastname)+" " +alltrim(Firstname)

Hope this helps.

Regards,

[werder101010]

Re: Not able to select second Mr smith in lookup table

Thanks (bedankt) Jo,

I see my post can be easily read the way you did. In my case the second mr. Smith is visible in the lookup but after selecting the second mr. Smith in the lookup it actually selects the first mr Smith.

If I understand correctly your problem / solution is a little different? I do use separate fields for last name and first name. Any suggestions?

[DVSOL005]

Re: Not able to select second Mr smith in lookup table

My pleasure Werder.

The difference is very likely the linking field. I use a personel number and yours is the name field. My assumption/requirement is that the personel number needs to be unique, hence my solution.
Your solution could be identical but then again I don't know your requirements at that point. I would suggest to use both Lastname and Firstname as linking field.
How about uniqueness of the records in case of for instance Jan Jansen in Almelo and Jan Jansen in Den Bosch?

Succes,

[werder101010]

Re: Not able to select second Mr smith in lookup table

Tried to get it working for a couple of hours. I don't know what I'm doing wrong. Maybe you can check my sample database and see whats wrong?

[DVSOL005]

Re: Not able to select second Mr smith in lookup table

Werder,

I will have a look and get back to you later.

Regards,

[Steve Wood]

Re: Not able to select second Mr smith in lookup table

On your lookup definition you have Lastname filling in the Client field, should be Client_Nr.

[DVSOL005]

Re: Not able to select second Mr smith in lookup table

Werder,

I think the first fill field is incorrect, should be different, Client_Nr.
Then it works fine.

Regards,

[werder101010]

Re: Not able to select second Mr smith in lookup table

Great, it works! Thanks for your help Jo and Steve. I do like the lastname to fill the client field but I never knew this could be achieved by using your approach and select “display no”. This way the second field in the lookup (source) is used to fill the client field. Thanks again!

[werder101010]

Re: Not able to select second Mr smith in lookup table

I encountered a little problem along the way: I use a script to create a directory with two fields: �adm_nr� and �client�. The script refers to the �client� field but the directory is actually created with the information present in the linking field: �client_nr�. Is this normal behavior and how can I prevent this from happening?

I attached a sample and this is the script I'm using:
tbl = table.current()
number = alltrim(tbl.main_table->Adm_Nr)
client = alltrim(tbl.main_table->Client)
base_path = "C:\client\\"
dir_name = number+" "+client
dir_create_recurse(base_path+dir_name)

[Stan Mathews]

Re: Not able to select second Mr smith in lookup table

I'll take a look at your zip but

number = alltrim(tbl.main_table->Adm_Nr)
client = alltrim(tbl.main_table->Client)


should be

number = alltrim(tbl.Adm_Nr)
client = alltrim(tbl.Client)

if Adm_nr and Client are in the table referenced by table.current

[DVSOL005]

Re: Not able to select second Mr smith in lookup table

Werder,

Please check result of debug in attachment.
This has to do with the lookup. Client nr is stored and Client name is displayed.
So in your script you retrieve what is stored.
I don't have a solution at the moment but I guess another lookup to retrieve the name should be considered.

Regards,

[DVSOL005]

Re: Not able to select second Mr smith in lookup table

Sorry, forgot atatchment.

[Mike Wilson]

Re: Not able to select second Mr smith in lookup table

Werder,
There are several problems.
1) The link in your set is wrong. number-number, not client-number
2) Your client table is corrupted. I had to rebuild the client table.
3) What Stan said

[DVSOL005]

Re: Not able to select second Mr smith in lookup table

Werder,

Stan was right but result is the same.
Mike mentioned something about corruption, I didn't have that issue.

You want the client's surname in the folder name?
In order to avoid the initial problem that you solved throught the way the lookup is constructed you could work your way around this issue by creating another field and do a lookup. see attachment Werder-02.
Attachment Werder-03 shows the changes and the result.
Werder04 shows final result.

I tested this and it works!

Regards,