Using a script variable in a field rule...

[neecy]

Re: Using a script variable in a field rule...

Hi Stan-
Thank you-

I have established an interim table with the all the fields I need and posted them to my form, however, each session that is run will only summarize the columns that was entered for that day and when I open my form, I will come across 'ERROR' in some of the fields. How can I use Action Scripting, User Defined Functions, or XBasic to first checks for the existance of my field, and if it exists, returns the value, otherwise it could return "" or nothing?

Thanks -

Neecy

[Stan Mathews]

Re: Using a script variable in a field rule...

first checks for the existance of my field, and if it exists, returns the value, otherwise it could return "" or nothing?

That's not the way it works.

It seems that you will need a "final" table with all of the possible fields. After your summary operation, append the existing fields in the summary table to the final table.

A form built on the final table will not have errors for the unfilled fields.

Since we know that all fields will exist in the final table we just need to find out what fields are in the summary table to build the append. To illustarate the principle I created a duplicate of the customer table (dup) in AlphaSports. I then built an append script which checks the fields in the customer table and appends existing fields to "dup".

tbl = table.open("customer")
flds = tbl.fields_get()
mlist = ""
FOR i = 1 TO flds
	fname = tbl.field_get(i).name_get()
	mlist = mlist + if(len(mlist)>0,",","")+fname
NEXT i
tbl.close()

uflds = w_count(mlist)
'---
a_tbl = table.open("dup")
append.t_db = "customer"
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "All"
append.m_count = uflds
af = "append.m_field"
ae = "append.m_exp"
for x =  1 to uflds
	afld = af+alltrim(str(x))
	aexp = quote(word(mlist,x))
	evaluate_template(afld+"="+aexp)
	aexpv = ae+alltrim(str(x))
	evaluate_template(aexpv+"="+quote("@customer->"+unquote(aexp)))
next x
append.t_count = 0
a_tbl.append()

[neecy]

Re: Using a script variable in a field rule...

Hi Stan-

Thank you so much!!! :)

That worked perfectly!!! :)

Neecy

[neecy]

Re: Using a script variable in a field rule...

Hi Stan-

One last question. Is there anyway I can modify the current script to when if I specify my append.type = "Unique, replace existing", and my master table fields will replace if different from my transaction table. I want my case definition to equal "EITHER". The transaction table should replace current records in which there has be an update in the master or append if there is a new record added.

Thanks-

[Stan Mathews]

Re: Using a script variable in a field rule...

Try this.

tbl = table.open("customer")
flds = tbl.fields_get()
mlist = ""
FOR i = 1 TO flds
	fname = tbl.field_get(i).name_get()
	mlist = mlist + if(len(mlist)>0,",","")+fname
NEXT i
tbl.close()

uflds = w_count(mlist)
'---
a_tbl = table.open("dup")
append.t_db = "customer"
append.m_key = ""
append.t_key = ""
append.m_filter = ""
append.t_filter = ""
append.type = "Unique, replace existing"
append.m_count = uflds
af = "append.m_field"
ae = "append.m_exp"
[COLOR="Red"]ac = "append.m_case"[/COLOR]
for x =  1 to uflds
	afld = af+alltrim(str(x))
	aexp = quote(word(mlist,x))
	evaluate_template(afld+"="+aexp)
	aexpv = ae+alltrim(str(x))
	evaluate_template(aexpv+"="+quote("@customer->"+unquote(aexp)))
	[COLOR="red"]aecase = ac+alltrim(str(i))
	evaluate_template(aecase +"="+ quote("Either"))[/COLOR]
next x
append.t_count = 0
a_tbl.append()

[neecy]

Re: Using a script variable in a field rule...

Hi Stan-
I received an error at a_tbl.append(). I am attaching my code to see what is wrong.

Thanks-

Neecy

[Stan Mathews]

Re: Using a script variable in a field rule...

Yeah, that's why I said try....

I get glassy eyed when I try to figure these out.

Now try substituting this.

evaluate_template(aecase +"="+"Either")

for

evaluate_template(aecase +"="+ quote("Either"))

[neecy]

Re: Using a script variable in a field rule...

Hi Stan-

Sorry, still didn't work. Same error at a_tbl.append()

[Stan Mathews]

Re: Using a script variable in a field rule...

I'm not sure what you're paying me is worth all this time.

tbl = table.open("customer")
flds = tbl.fields_get()
mlist = ""
FOR i = 1 TO flds
	fname = tbl.field_get(i).name_get()
	mlist = mlist + if(len(mlist)>0,",","")+fname
NEXT i
tbl.close()

uflds = w_count(mlist)
'---
a_tbl = table.open("dup")
append.t_db = "customer"
[COLOR="red"]append.m_key = "customer_id"
append.t_key = "customer_id"[/COLOR]
append.m_filter = ""
append.t_filter = ""
append.type = "Unique, replace existing"
append.m_count = uflds
af = "append.m_field"
ae = "append.m_exp"
ac = "append.m_case"
for x =  1 to uflds
	afld = af+alltrim(str(x))
	aexp = quote(word(mlist,x))
	evaluate_template(afld+"="+aexp)
	aexpv = ae+alltrim(str(x))
	evaluate_template(aexpv+"="+quote("@customer->"+unquote(aexp)))
	[COLOR="red"]aecase = ac+alltrim(str([COLOR="Green"]x[/COLOR])) [COLOR="Blue"]'change loop variable to match this example[/COLOR]
	evaluate_template(aecase +"="+ quote("Either"))[/COLOR][COLOR="Blue"]'put quote back in[/COLOR]
next x
append.t_count = 0
a_tbl.append()

[neecy]

Re: Using a script variable in a field rule...

Hi Stan-

I don't know what happened, but it worked. I copied the code and made changes to the file name yesterday and it didn't work. Today, it worked. :)

Thank you for you assistance. :)

[Stan Mathews]

Re: Using a script variable in a field rule...

Sometimes the humidity level can mess things up.