calculated field help

[gmeredith17]

I have a set (1:many) with s_order as the parent and S_release as the child linked via fields son_id=rel_son_id. S_release has fields rel_qty (numeric field) and status (character field).

I am trying to create a calculated field on a form that will give me the total for rel_qty where the status="s".

I've been messing about with the expression builder using the total and if functions but cannot get a valid expression. Help file doesn't seem to have much in the way of examples and for me the building of expressions would be a great 'how to' addition to the help with numerous examples. Probably just me but I really struggle with the syntax.

Anyone help with this one?

[gmeredith17]

Re: calculated field help

okay, as soon as I wrote the thread I went back and got it to work. Don't know why the solution was so elusive prior to posting.

total(if(S_Release->Status="s",S_Release->Rel_Qty,0),GRP->s_order,GRP->s_release)

[Stan Mathews]

Re: calculated field help

Total() is a report writer function.

tablesum("S_release","rel_son_id = "+quote(son_id)+" .and. status =' s'","rel_qty")

if rel_son_id and son_id are character fields.

[gmeredith17]

Re: calculated field help

Thanks Stan.

I have seen it mentioned a few times and indeed the help file states the same. What I find puzzling is that Alpha use the total() function in the Alphasports DB sample on the invoice form.

total(INVOICE_ITEMS->Extension,GRP->INVOICE_HEADER)

You would think if it isn't suitable for form use then they wouldn't use it. So is it a case of the old adage 'do as I say not as I do'. An expression (no pun intended) which I recall my father using on a number of occasions during my childhood.

[DaveM]

Re: calculated field help

Hi geoff,


EDIT: good for you



.

[Stan Mathews]

Re: calculated field help

Total() is perfectly fine for use on a form in the manner which you did. The problem arises when users limit themselves to total() and don't avail themselves of alternatives.

The grouping parameters are "fixed" for a form in that you must use the parent child relationships for the set upon which the form is built. You can't define groups for a form.

Tablesum() and dbsum() don't suffer the same limitation and can also be used in field rules. Relying on total() in all instances is not possible and the tendency to do so will limit one's understanding of the alternatives.

[Tbaker]

Re: calculated field help

Geoff

First, let me say that I totally agree with the statement that "Total() is a report writer function"

What is confusing, if you go the web help

http://support.alphasoftware.com/alp...ns/TOTAL().htm

This is what the web help says about the total() function

This function is typically used in Reports to total records in a group, or in Forms to total records in a one-to-many child table

Interesting and confusing -

But again, we do know that as a fact the tablesum() function works just fine.

Tom Baker

[gmeredith17]

Re: calculated field help

Stan,

Relying on total() in all instances is not possible and the tendency to do so will limit one's understanding of the alternatives.

I totally agree and find the tablesum method easier to understand and appreciate your solution.

In addition I have now taken the time to have a look at the quote() function. Although I new it existed I hadn't realised how useful it can be when creating expressions.

So at least two new things learnt today.

Thanks again.

Geoff

[gmeredith17]

Re: calculated field help

Tom,

Interesting and confusing

Yes it is but it doesn't take much to confuse me.

My local help has

Limitations

This function is a report writer function, not intended for table level field rules or other expressions. While the function may perform in some areas outside of the report writer, its use there is not supported.

This is not shown on the V9 web help. I wonder why it was removed. All I can say is without the help of this board and the experience of its users I would be totally lost.

Geoff

[Stan Mathews]

Re: calculated field help

In addition I have now taken the time to have a look at the quote() function. Although I new it existed I hadn't realised how useful it can be when creating expressions.

If you think quote() is nice check out

s_quote() the s is for super smart
replace_parameters()
between_char() and its siblings

Writing Expressions

Have fun!

[G Gabriel]

Re: calculated field help

This is what the web help says about the total() function



Interesting and confusing -

Interesting and confusing but shouldn't be.
Confusing because, as in many other software, help files are written by robots or humanoid objects with no sense of humor nor any intent to elaborate. You have to sort of understand how things work and then you could understand the codified help file.
Yes total() works in reports and forms based on a set and for a good reason: it all has to do with how and when alpha evaluates expressions. In a report, alpha runs temp tables, evaluates all expressions then spits out the final report. In a form, evaluating expressions is the first thing that happens when you either activate the form, or when you fetch from record to record. If you are using total() in a report and say you use it to total a field in the parent (or as in group grand), alpha will have a chance to pre-calculate that in the temp tables before spitting the final report.
If you use it to total children records, again when you fetch the parent, alpha will have a chance to total the children. But if you use it to do a total for the parent (or grand) in a form, or in a form based on a table not a set, when you fetch, alpha cannot calculate that as it does not calculate the entire parent's records, only the record at hand.

This is my translation from Humanoid to Human, hope it helps.

Fellow human.

[gmeredith17]

Re: calculated field help

G, thanks for the translation. It is a help.