Summary Operation vs xBasic tbl.summarize()

[Tom Henkel]

We have a curious situation here. We have been running a tbl.summarize() piece of code for years. I needed to make some changes to the table structure, and now, it seems that the xBasic code doesn't seem to want to work. I have a summarize operation that works, and creates the appropriate records in the summary table. We needed to create the xBasic option because we need to pass a variable into the filter, and the operation doesn't afford us the option of having variables (as far as I know). What is even more curious is that if I convert the operation to xBasic using the "XB" on the toolbar, it created the exact code I had in my script, however, it doesn't summarize the data.

this is the code;

'from the interactive window
dim pmtdate as D = {12/21/2017}
filter = replace_parameters("(isblank(\"GAAS\").or.GAAS=\"N\").and.G09_ISS_DT=ALLTRIM(REMSPECIAL(DTOC([varD->pmtdate]))).AND.RELEASE=\"Y\".AND.(Rec_Type = \"EA\".or.(Rec_Type = \"TRA\".and.substr(G09_ISS_DT,3,2)<>\"01\")).AND.APPEND_DATE={}",local_variables())
?filter 
= (isblank("GAAS").or.GAAS="N").and.G09_ISS_DT=ALLTRIM(REMSPECIAL(DTOC({12/21/2017}))).AND.RELEASE="Y".AND.(Rec_Type = "EA".or.(Rec_Type = "TRA".and.substr(G09_ISS_DT,3,2)<>"01")).AND.APPEND_DATE={}

'now the code from the script...

	'build the filter using the date variable
	filter = replace_parameters("(isblank(\"GAAS\").or.GAAS=\"N\").and.G09_ISS_DT=ALLTRIM(REMSPECIAL(DTOC([varD->pmtdate]))).AND.RELEASE=\"Y\".AND.(Rec_Type = \"EA\".or.(Rec_Type = \"TRA\".and.substr(G09_ISS_DT,3,2)<>\"01\")).AND.APPEND_DATE={}",local_variables())
	a_tbl = table.open("daily_supsvc")
	sum.db = "dimps_sum2.DBF"
	sum.db_dd_delete = .T.
	sum.order = "G01_CASE"
	sum.filter = filter 
	'sum.filter = "(ALLTRIM(GAAS)=\"\".OR.GAAS=\"N\").AND.G09_ISS_DT=REMSPECIAL(DTOC({12/21/2017})).AND.RELEASE=\"Y\".AND.(REC_TYPE=\"Ea\".OR.(REC_TYPE=\"Tra\".AND.SUBSTR(G09_ISS_DT,3,2)<>\"01\")).AND.APPEND_DATE={}"
	sum.options = "I"
	sum.fields = 5
	sum.field1 = "G01_Case"
	sum.code1 = 0
	sum.field2 = "G01_Case"
	sum.code2 = 2
	sum.field3 = "G10_Ck_Amt"
	sum.code3 = 1
	sum.field4 = "val(substr(G01_Case,2,6))"
	sum.code4 = 1
	sum.field5 = "Seq_No"
	sum.code5 = 6
	a_tbl.summarize()

I've also attached a picture of the operation with it's filter.

If anyone sees something glaring, please let me know.

Thanks,

Tom

[Stan Mathews]

The only thing I would suggest is to restart the machine running the code. I have encountered unexplained behavior that suggested the original code was somehow held in memory and being executed rather than the altered code.

[MoGrace]

Are you closing a_tbl after the op runs in your script?

[Tom Henkel]

Stan, Did that to no avail.

Robin, Yes, we did close the table.

ISSUE Resolved.

I checked another application that produces the same basic end result and saw that we were using a variable in the filter of the summary operation. We just declared it as a global in the script beforehand.

I declared a global variable "pmtdate" in the IW, then edited the operation, placing the variable in the summary folder, and MAGIC, it worked!!!

This is much cleaner than modifying code, and best of all, it works. I'm still stymied by the problem, but we REALLY needed to get this working as it's been down for over a week.
This process produces a text file in a specific format for another program to use the text file as input to an OLD mainframe application. The outcome of it is that we (the state) are paying clients or vendors for specific types of assistance. The run I did this morning was for over 40K, so you can see that it is kind of important that it works.

Thanks, for looking.


Have a great holiday!

Tom

[MoGrace]

What about the variable 'filter' is that global as well? I wouldn't think pmtdate needs to be global since you are using replace_parameters to set the value beforehand.

[Tom Henkel]

Robin,

I put the entire expression from "filter" into the record selection filter of the operation. Never really thought about leaving it out of the expression and just declaring the "Filter" as a global...

Tom