Create multiple vars and set with table data

[MoGrace]

I am using a table with data provided by Dan Latshaw and so far have accomplished the following:

varlist = table.external_record_content_get("serv_tmpl","serv_id","sortkey")
?varlist
= S013
S001
S002
S003
S014
S004
S015
S005
S020
S017
S012
S022
S008
S011
S023
S019
S018
S007
S010
S009
S024
S016
S006
S021

datalist = table.external_record_content_get("serv_tmpl","serv_desc","sortkey")
?datalist
= Case Management
Medicaid
Medical
CHIP
Food Stamps/ SNAP
WIC
Housing/ Rental Assistance
HPRP
Section-8 Public Housing
Job Training
Other Employment Services
Subsidized Or TANF Daycare
Transportation
Welfare To Work
WIAS-Workforce Investment Act
MATP-Med Assist Trans Program
Mental Health Services
Primary Health Services
VA Medical
VA Services
D/A Substance Abuse Treatment
DV-Domestic Violence
Other TANF
SSDI/ SSI/ SOAR Income

for each foo in vList
evaluate_template("dim global "+foo.value+" as c")
next

What I want to do now is set those 24 variables with the corresponding data from the datalist - how do I do that?

[Stan Mathews]

Re: Create multiple vars and set with table data

Try

varlist = table.external_record_content_get("serv_tmpl","serv_id","sortkey")
datalist = table.external_record_content_get("serv_tmpl","serv_desc","sortkey")
qx = 1
for each foo in vList
evaluate_template("dim global "+foo.value+" as c = "+word(datalist,qx,crlf()))
qx = qx + 1
next

[MoGrace]

Re: Create multiple vars and set with table data

Hi Stan,
I was trying to do something like your example and that was where I got stuck - in the meantime I found this works too:

tbl = table.open("serv_tmpl")
tbl.index_primary_put("sortkey")
tbl.fetch_first()
while .not. tbl.fetch_eof()
	evaluate_template("dim global "+tbl.serv_id+" as c = "+quote(alltrim(tbl.serv_desc)))
	tbl.fetch_next()
end while
tbl.close()

However, I like yours better!

[Stan Mathews]

Re: Create multiple vars and set with table data

Mine needs the quotes too.

evaluate_template("dim global "+foo.value+" as c = "+quote(word(datalist,qx,crlf())))

[Stan Mathews]

Re: Create multiple vars and set with table data

When dealing with more than one or two values as you are I lean towards creating a collection.

Example code below has some extraneous statements combining the two strings which would be obviated if I had access to the table.

coll_list = ""
qx = 1
for each foo in varlist
coll_list = coll_list+foo.value+";"+word(datalist,qx,crlf())+crlf()
qx = qx + 1
next
delete depts. 'clear any previous values
dim depts as u 'this is the collection
depts.initialize("1;2","1","2",coll_list) 'this does the work
? depts.get("S014") 'retrieve a value
= " Food Stamps/ SNAP"
? depts.get("S001") 'and again
= " Medicaid"
? depts.size() 'not sure if you would need this
= 24
? depts.exist("ABC1") 'can test for a key before trying to use its value
= .F.

? depts.dump("key~value") 'can examine the collection
= S013~Case Management
S001~ Medicaid
S002~ Medical
S003~ CHIP
S014~ Food Stamps/ SNAP
S004~ WIC
S015~ Housing/ Rental Assistance
S005~ HPRP
S020~ Section-8 Public Housing
S017~ Job Training
S012~ Other Employment Services
S022~ Subsidized Or TANF Daycare
S008~ Transportation
S011~ Welfare To Work
S023~ WIAS-Workforce Investment Act
S019~ MATP-Med Assist Trans Program
S018~ Mental Health Services
S007~ Primary Health Services
S010~ VA Medical
S009~ VA Services
S024~ D/A Substance Abuse Treatment
S016~ DV-Domestic Violence
S006~ Other TANF
S021~ SSDI/ SSI/ SOAR Income

[MoGrace]

Re: Create multiple vars and set with table data

How would you change the script for the table?

[Stan Mathews]

Re: Create multiple vars and set with table data

I would use table.external_record_content_get() with a twist to populate the list in one pass. Example below builds a string from the ordgde table, pulling a numeric field (ex_posit) and a character field (fscde) and placing the semicolon between them. The collection is initialized from this list as suggested earlier.

There is another method .from_table() which is more direct but it ignores marked records (a bug I think).

? table.external_record_content_get("ordgde","alltrim(str(ex_posit))+';'+alltrim(fscde)","ex_posit")
= 20;255869
30;259309
40;160093
50;126797
60;126946
80;125864
90;385740
100;255844
120;258004
123;443760

[MoGrace]

Re: Create multiple vars and set with table data

That is simpler, coll_list replaces the need for the varlist as well as the datalist. Thanks Stan, I think that example clarifies how a collection can be used very well.

[MoGrace]

Re: Create multiple vars and set with table data

I thought I would try making a generic function so I could set other collection variables and am not having much luck getting a return value.

This is the rough script:

FUNCTION SetCollectVar AS C (datalist AS C, varname AS U )
'Call before function	
'Example: dataList = table.external_record_content_get("servtmpl","alltrim(serv_id)+';'+alltrim(serv_desc)","serv_id")

cmd = "delete "+varname
evaluate_template(cmd)
cmd = "dim "+varname+" as U"
evaluate_template(cmd)
cmd = convert_expression(varname+".initialize(\"1;2\",\"1\",\"2\",dataList)","V")
evaluate_template(cmd)
cmd = convert_expression(varname+".dump(\"key~value\")","V")

SetCollectVar = evaluate_template(cmd)
END FUNCTION

This is my testing in the IW:

dataList = table.external_record_content_get("HOUSING","alltrim(housingid)+';'+alltrim(housing)","housingid")

?SetCollectVar(datalist,"house")
[COLOR="#0000FF"]ERROR: Variable type mismatch: attempt to pass data of type 'C' to argument 'varname ' is of type 'U'.
[/COLOR]
'==============

varname = "house"

cmd = "delete "+varname
?cmd
= "delete house"
evaluate_template(cmd)

cmd = "dim "+varname+" as U"
?cmd
= "dim house as U"
evaluate_template(cmd)

cmd = convert_expression(varname+".initialize(\"1;2\",\"1\",\"2\",dataList)","V")
?cmd
= house.initialize("1;2","1","2",dataList)
evaluate_template(cmd)

cmd = convert_expression(varname+".dump(\"key~value\")","V")
?cmd
= house.dump("key~value")
[COLOR="#FF0000"]?evaluate_template(cmd)
= ""[/COLOR]

[COLOR="#0000FF"]'note that the variable is populated[/COLOR]
v= house.dump("key~value")
?v
= H001~Emergency shelter  including hotel or motel paid by emergency shelter voucher
H002~Foster care home or foster care group home
H003~Hospital or other residential non-psychiatric medical facility
H004~Hotel or motel paid for without emergency shelter voucher
H005~Jail, prison or juvenile detention facility
H006~Long-term care facility or nursing home
H007~Owned by client, no ongoing housing subsidy
H008~Owned by client, with ongoing housing subsidy
H009~Permanent housing for formerly homeless persons
H010~Place not meant for habitation
H011~Psychiatric hospital or other psychiatric facility
H012~Rental by client, no ongoing housing subsidy
H013~Rental by client, with VASH subsidy
H014~Rental by client, with GPD TIP subsidy
H015~Rental by client, with other ongoing housing subsidy
H016~Residential project or halfway house with no homeless criteria
H017~Safe Haven
H018~Staying or living in a family member's room, apartment or house
H019~Staying or living in a friend's room, apartment or house
H020~Substance abuse treatment facility or detox center
H021~Transitional housing for homeless persons (including homeless youth)
H022~Other
H023~Client doesn't know
H024~Client refused
H025~Data not collected
H026~Subsidized Housing
H027~On the Street
H028~Nursing Home

[Stan Mathews]

Re: Create multiple vars and set with table data

FUNCTION SetCollectVar AS C (datalist AS C, varname AS U )

varname AS C

The name of the collection variable is character, the value of that variable gets dimmed as U in the function.

[MoGrace]

Re: Create multiple vars and set with table data

Hi Stan,
I tried it as C first, then A, then P (typeof() returned P for the other collection var) and last as U. It didn't make any difference - same error.

But sometimes new functions don't update properly, so I will change it back to C and reopen the adb and see if that makes a difference...