Lookup values from a comma seperated field

[den1s]

Hi,

In one of my tables i have a memo field that contains items selected from a checkbox list. When a user selects more than one item from the checkbox list, the field is populated with the ID_No(s) seperated by a comma.

When i run reports, i need the report to show the character value(s) i.e. the related name(s) of the entries instead of the ID_No(s).

So instead if the report showing;

001,003,008,101,201

I need it to show

Tea,Coffee,Milk,Bread,Butter

Is this possible, and if so, does anyone have any suggestions on how i would go about achieving this?.

Thanks in advance.

Denis

[drgarytraub]

Re: Lookup values from a comma seperated field

Hi Denis,

If the ID is always 3 characters, then it is easy to use something like extract_string() to separate the string in to separate id's.

Or you could use something like comma_to_crlf().

Hope this gets you in the right direction.

Gary

[den1s]

Re: Lookup values from a comma seperated field

Gary,

Thank you for your suggestions.

The ID is always 3 characters.

I am not an expert on coding, do you have an example you could share with me.

Thanks,

Denis

[drgarytraub]

Re: Lookup values from a comma seperated field

Denis,

There may well be a function to accomplish what you want in a report but I am not aware of one.

If I had to do this, I would probably use xbasic to preprocess the table BEFORE the report. If noone knows of a simpler way, and you need some help in the steps to preprocess, let me know. For example, you could add another field to your table - then use xbasic to fill that field with the names associated with the id's from the other field - then use the new field in the report.

Gary

[den1s]

Re: Lookup values from a comma seperated field

Gary,

This sounds like a plan.

For example, you could add another field to your table - then use xbasic to fill that field with the names associated with the id's from the other field - then use the new field in the report.

I would appreciate any coding help you can offer.

Kind regards,

Denis

[Stan Mathews]

Re: Lookup values from a comma seperated field

Given a table to do the lookup to (tolook).

Strlist = "001,003,008,101,201"

? strtran(*for_each(foo,lookup("[COLOR="red"]tolook[/COLOR]","id = "+quote(foo),"alltrim(name)"),strtran(strlist,",",crlf())),crlf(),",")
= "Tea,Coffee,Milk,Bread,Butter,"

Strlist = "201,101,008"

? strtran(*for_each(foo,lookup("tolook","id = "+quote(foo),"alltrim(name)"),strtran(strlist,",",crlf())),crlf(),",")
= "Butter,Bread,Milk,"

Would need a minor tweak to trim the trailing comma.

? left(strtran(*for_each(foo,lookup("tolook","id = "+quote(foo),"alltrim(name)"),strtran(strlist,",",crlf())),crlf(),","),-1)
= "Butter,Bread,Milk"

[Stan Mathews]

Re: Lookup values from a comma seperated field

And you could make that a function.

FUNCTION com_strg_lookup AS C (tablename AS C, fieldname AS C, strname AS C )
	com_strg_lookup = left(strtran(*for_each(foo,lookup(tablename,"id = "+quote(foo),"alltrim("+fieldname+")"),strtran(strname,",",crlf())),crlf(),","),-1)
END FUNCTION

? strlist
= "201,101,008"

? com_strg_lookup("tolook","name",strlist)
= "Butter,Bread,Milk"

Anyone using V6 or later can use.

FUNCTION comma_strg_lookup AS C (tablename AS C, fieldname AS C, strname AS C )
	comma_strg_lookup = crlf_to_comma(*for_each(foo,lookup(tablename,"id = "+quote(foo),"alltrim("+fieldname+")"),comma_to_crlf(strname)))
END FUNCTION

[drgarytraub]

Re: Lookup values from a comma seperated field

Great stuff Stan!

Gary

[Stan Mathews]

Re: Lookup values from a comma seperated field

Just the power available to us in Alpha. The trivial case is a single lookup() "101" retruning Tea. Wrap that in something that processes the items in a list and voila.

(Thanks for the kind words.)

[den1s]

Re: Lookup values from a comma seperated field

Hi Stan,

Thank you, I will give this try the next time i am in the office.

Thank you both for your invaluable input in this thread and of course this forum.

I will keep you posted on how i get on with it.

Denis

[den1s]

Re: Lookup values from a comma seperated field

Hi,

I managed to get a PC to try this out.

I have attempted to create a calculated field (Memo) in my table so i can perform the lookup. Unfortunately, the calculated option is greyed out.

Can you tell me where i should place the code?

left(strtran(*for_each(foo,lookup("tolook","id = "+quote(foo),"alltrim(name)"),strtran(strlist,",",crlf())),crlf(),","),-1)

Thanks,

Denis

[Stan Mathews]

Re: Lookup values from a comma seperated field

When i run reports, i need the report to show the character value(s) i.e. the related name(s) of the entries instead of the ID_No(s).

Define the calculated field for the report.

"Feed" it the list generated when

When a user selects more than one item from the checkbox list, the field is populated with the ID_No(s) seperated by a comma.

or the text of the field.

[den1s]

Re: Lookup values from a comma seperated field

Stan,

Thank you for your prompt reply.

Is it possible to perform this directly within a table? The reason being, my users will want to search for items by their name within a grid. I am not sure how i would search 001 when i need to really search for Tea, especially if 001 is amongst other id's e.g. 010,098,001,293 and so on.

Denis

[Stan Mathews]

Re: Lookup values from a comma seperated field

I think this is what you want. Enter some values in the ids_field and move to the next record (or just save the current record). The names_fld will be filled with the corresponding values.

The process is carried out by a field rule record event.

[den1s]

Re: Lookup values from a comma seperated field

Stan,

Perfect. Thank you for your assistance with this one.

Denis