calc field question

[milesjg]

Can the tablesum function be used in a calculated field.?
I am trying to show a calculated summary field based on a filter for display at the bottom of a browse.
The records in the browse are child records and I am trying to show the sum of just those child records.
I tried tablesum("tablename","field="+variable,"fieldname") and it will not work in the calculated field expression buider. Works everywhere else. The expression builder says invalid expression. Tried what I could think of.
Thanks in advance for help.

[csda1]

Re: calc field question

Hi James,

Yes it does work. Your expression

tablesum("tablename","field="+variable,"fieldname")

looks wrong. The filter needs to be an expression. If variable ="a1234" the middle part evaluates to a filter expression

"field=a1234"

instead of

"field= 'a1234' "

So try an expression like

tablesum("tablename","field=' "+variable+" ' ","fieldname")

Note extra spaces are there for clarity.

[milesjg]

Re: calc field question

Thanks. It made some progress. The code;

1.) tablesum("tablename","field=' "+variable+" ' ", "fieldname") gave the message "field" variable not found. Meaning that it is taking variable to be a field.
Variable is a N. If I change the code to;

2.) tablesum("tablename","field=" "+variable+", "fieldname") the calc expression window show a numeric field designator with a numveric value.

So instead of "field= ' "+variable+" ' " I used "field=" "+variable+", which works in the calc field definitions window, but when placed on a form by drop and drag and looking at properties the expression builder says -invalid exspression-. Neither work. The expression builder hates them both and the form told me to come back later if I wanted it to display anything.
Thanks for the help.

[csda1]

Re: calc field question

Thanks. It made some progress. The code;

1.) tablesum("tablename","field=' "+variable+" ' ", "fieldname") gave the message "field" variable not found. Meaning that it is taking variable to be a field.
Variable is a N. If I change the code to;

2.) tablesum("tablename","field=" "+variable+", "fieldname") the calc expression window show a numeric field designator with a numveric value.

So instead of "field= ' "+variable+" ' " I used "field=" "+variable+", which works in the calc field definitions window, but when placed on a form by drop and drag and looking at properties the expression builder says -invalid exspression-. Neither work. The expression builder hates them both and the form told me to come back later if I wanted it to display anything.
Thanks for the help.

If variable is a numeric, then the expression should be more as you 1st had it. But you are referencing the field called field in tablename to be equal to the number. If the table does not have a field named field it will never work. Use the name of the field that you want to compare. And the expression should look like

tablesum("tablename","fieldname_to_compare="+ltrim(str(variable)), "fieldname_to_sum")

[Tom Cone Jr]

Re: calc field question

"Field" is a reserved word and should not be used as the name of a variable. Personally I think it's bad practice to use reserved words to name any object in your application, including fieldnames, formnames, etc.

[milesjg]

Re: calc field question

Chalk another one up for me. I forget how easy it is to explain something when it's in front of oneself, which seems obvious, and make it obscure to another.
table is named "genaccts" I was calling tablename.
field to sum on in "genaccts" is "aamount" I was calling field.
variable it called autovar.
The actual code is - tablesum("genaccts","auto=" + autovar,"aamount")
When field "auto" is equal to the variable "autovar" the sum function produces the sum of the records that are linked to the master record with the key value of autovar.
The master has a field called auto. The child has a field called auto. They are linked by the field "auto". "Auto" is a increment field in the master.
There is a browse which when a record in the master table is displayed all the linked records in "genaccts" are displayed. I am tryin to display a sum of the linked records in "genaccts".
"autovar" is a variable that is set to the value of the field "auto" in the master table. Thus when the record changes in the master the sum of all the links in "genaccts' for the master record are shown by the tablesum()
I use this inmany palces elsewhere exactly as mentined but not in a calc field before.
The code for table name produces "invalid expression" when trying to use it to define a calc field.
Sorry about that.

[Tim Kiebert]

Re: calc field question

Is the field 'auto' character or numeric? And what is the type of autovar?

Although it will be good to get the tablesum working properly for you there may be an easier way to get what you want. From the drag drop list drag the child field 'aamount' onto the form. This should bring up the field genie. Choose 'Total of the field'. You might want to edit the long default name for the calculation and then press OK. This will place the new calculated field on your form and create an entry in the calculated field list.

[milesjg]

Re: calc field question

The field "auto" is a numeric auto inrement field.
Here is what I discovered. The code;
1.) tablesum("genacts","auto=",+autovar,"aamount") will not work in the xy->calc field maker thingy. It cannot be defined in the expression builder. Gives error messages on both parts.
You can define, in the calc field builder; (xy on the menu)
2.) tablesum("genaccts","auto=" "+autovar+","aamount"). It will give you a
N:0 at the bottom. You cannot define this in the expression builder. You get the message invalid expression.
Number two displays the name of the field on the form. No number.
If after you place the calc field (as defined in 2.)) on the form and select properties you can edit the expression so that it is 1.). You cannot edit it if you select the expression builder from the property window. Only if you edit it
as it appears on the window. It then displays the sum of the filtered records.

[Tim Kiebert]

Re: calc field question

The field "auto" is a numeric auto inrement field.
Here is what I discovered. The code;
1.) tablesum("genacts","auto=",+autovar,"aamount") will not work in the xy->calc field maker thingy.
Maybe the mistakes above are typos in this message but I see "genacts" where you have previously mentioned and use below "genaccts". Also you have an extra comma after "auto=" that should not be there.

It cannot be defined in the expression builder. Gives error messages on both parts.
You can define, in the calc field builder; (xy on the menu)
2.) tablesum("genaccts","auto=" "+autovar+","aamount"). It will give you a
N:0 at the bottom. You cannot define this in the expression builder. You get the message invalid expression.
Number two displays the name of the field on the form. No number.
If after you place the calc field (as defined in 2.)) on the form and select properties you can edit the expression so that it is 1.). You cannot edit it if you select the expression builder from the property window. Only if you edit it
as it appears on the window. It then displays the sum of the filtered records.

If both the auto field and the autovar variable are numeric then the following should work

 [COLOR=Black]tablesum("genaccts","auto=" + var->autovar,"aamount")
[/COLOR]

Your autovar variable should be defined with a scope of at least session. If still no joy it may be time to upload enough of your app for someone to take a look.

[csda1]

Re: calc field question

Hi James & Tim

If both the auto field and the autovar variable are numeric then the following should work

 [COLOR=Black]tablesum("genaccts","auto=" + var->autovar,"aamount")[/COLOR]

You can't count on the expression builder being able to convert numerics to strings. The expression should be

 [COLOR=Black]tablesum("genaccts","auto=" + ltrim(str(autovar)),"aamount")[/COLOR]

If you still have problems, try filling in absolutes and find out what part is not working. E.g.

tablesum("genaccts",".t.","aamount")
tablesum("genaccts","auto=" + ltrim(str(1234)),"aamount")
tablesum("genaccts","auto=1234","aamount")
tablesum("genaccts","1234=" + ltrim(str(autovar)),"aamount")

[Tim Kiebert]

Re: calc field question

Hello Ira,

Good point. The expression I suggested I tested in good ol' Alphasports but as you say probably best not to count on the auto conversion. I did find though that in the form calculated field dialog I had to prefix the variable with 'var->' otherwise the variable was not recognized. However, if from there I went into the expression builder then the unprefixed variable was recognized and a result returned.

[milesjg]

Re: calc field question

Tim;

Yep, those were typos. The formula you gave me does the same thing. It says in the calc field window "can't find field var->autovar". If however you place it in quotes - tablesum("genaccts","auto=" + "var->autovar","aamount")
it will accept it. Show n:0 at bottom. Placed on the form it show nothing though. (Name of field) Once again it can be edited (by method mentioned) to what you wrote - tablesum("genaccts","auto=" + var->autovar,"aamount") and it works.
But...you were cookin with natural gas when you suggested the drag and genie thing. Made all this moot. With "no work." Though I'd still like to know why the edit gyrations are necessary to git dis stuff to work. There has to be some deep, existential underlying purpose in a (grander sense) for all this time spent on this.this subject.
Really appreciate the help.

[MikeC]

Re: calc field question

James,

Though I'd still like to know why the edit gyrations are necessary to git dis stuff to work. There has to be some deep, existential underlying purpose in a (grander sense) for all this time spent on this.this subject.

Different scenarios require different scripting....context is everything and changing this will most times change the script that is written. So each problem solved will have different solutions in different contexts. By "context" I am referring to various things such as where the script is called from (form, browse, report, autoexec, etc.), what event it is called from (OnInit, OnFetch, OnPush,etc.), and where the script is to be used (calc field, variable, parent field, child field,etc.) and more.

So even though you might have basically the same script you most times have to modify it according to context...no generic solution IOW.

[Tim Kiebert]

Re: calc field question

Tim;

Yep, those were typos. The formula you gave me does the same thing. It says in the calc field window "can't find field var->autovar". If however you place it in quotes - tablesum("genaccts","auto=" + "var->autovar","aamount")
it will accept it. Show n:0 at bottom. Placed on the form it show nothing though. (Name of field) Once again it can be edited (by method mentioned) to what you wrote - tablesum("genaccts","auto=" + var->autovar,"aamount") and it works.
But...you were cookin with natural gas when you suggested the drag and genie thing. Made all this moot. With "no work." Though I'd still like to know why the edit gyrations are necessary to git dis stuff to work. There has to be some deep, existential underlying purpose in a (grander sense) for all this time spent on this.this subject.
Really appreciate the help.

James,
I am glad that you were at least able to get the job done.
I would recommend trying some of Ira's alternatives substituting in some fixed data for the variable to narrow down the problem. You haven't mentioned how and where the variable is declared or how you are populating its value. I am suspecting it is a scope/context issue as Mike has mentioned.

[[email protected]]

Re: calc field question

I have two tables in a set called locinventory, locations and inventory.
When I display the parent record, I would like to total the cases in inventory for that location shown in the browse. It would seem this is the way to do it, but no joy for me.

calc field on the parent record;
tablesum("inventory.dbf" , "Location_Code = Locinventory->Location_Code", "Locinventory->Cases_Date_1")

Even when I change my filter expression to ".T." it doesn't evaluate. Can I sum a field in a child table from the parent?

Thanks
Bill Belanger
(My 1st day back with Alpha and I'm stuck)