Formula fields in HFCS data files

Hi,

I want to add a formula item to a data file, specifically in this case I want to add a field to a sales order header file called NumberOfLines and then have that automatically calculated as the number of records on the sales order line table with a matching Sales Order Number.

Simple query using the COUNT SQL syntax, but the editor rejects it and I'm wondering if this is a limitation of the feature?

Can anyone enlighten me?

Many thanks
Reg

Hi, why?

You can show the total number of items in any Table control - without any formula.

Kind regards,
Guenter Predl
office@windev.at

Hi,

It's a value I would find useful in various places within my application, including away from tables.

And were I to have a table showing 1 row for each sales order header I would still need a calculated column containing a formula to show a count of that specific order's number of lines wouldn't I?

Cheers
Reg

I never used formula itens for calculations using data from other tables. I don't know if it works or not.
In fact i never use formula itens because they are caculated in every read acccess and it looks to me that it's a potencial performance problem.
[doc.windev.com]

For this kind of situations i try to solve it using one server trigger.
[doc.windev.com]

One AFTER trigger in the sales order lines table to count the number of records and do one update ot the sales order header will solve your problem and it will only have performance impact when you insert or delete order lines.

Regards

Paulo Oliveira

I join a table invoices with invoicelines and show every invoice with the total amount of the lines, using the SUM() function.
Works as expected and I'm pretty sure a COUNT can be used as well.

Did you try/test the query in the HF Control Centre ?

The query editor is not 100%, you may end up using the sql-text as is, instead of the visual designer. But such a query is perfectly useable in your code, the same way you use other ones.

Arie

Thanks for the advice Paulo / Arie,

Both perfectly fine answers but not quite suitable in my system, probably due to poor design on my part (I write the system around 10 years ago while I was learning Windev from the book)!

I've sorted it for my immediate need via the Displaying A Row event in a table.

Many thanks
Reg