Sum of selected records...

[pjstarr]

Best wishes to all forum members for a happy, healthy and peaceful holiday season.

If anyone can take a break from their celebration to help with the following problem, it would be greatly appreacted. A parent table contains daily production time in hours and a child table contains a list of downtime codes and time when the machine is off line. The tables are in a set and linked by a field called Report Number. I can easlily link the total downtime to a field in the parent table by using the TOTAL() function, however I only want to total the records in the child table that are flagged by a logical variable call Add_Downtime. I have tried the Tablesum() function, using the Add_Downtime field as the filter, but that gives me all the total of all records, even when the report numbers are different.

Thanks in dadvance for your kind cooperation...

[Ray in Capetown]

Re: Sum of selected records...

Paul, you can filter the table underlying the browse.
You will see on the browse those records "disappear"
Then the TOTAL() function should total accordingly.

test this in the interactive code window, in this example, all related child records show, as you said.
Then I want to see only records where amt>-10 for example

tb=table.current(2)
tb.query_create("","Amt>-10")

viola, the unwanted records no longer appear.

[pjstarr]

Re: Sum of selected records...

Thank you Ray for your prompt reply. I will test your solution later today.

Happy New Year.

Paul, you can filter the table underlying the browse.
You will see on the browse those records "disappear"
Then the TOTAL() function should total accordingly.

test this in the interactive code window, in this example, all related child records show, as you said.
Then I want to see only records where amt>-10 for example

tb=table.current(2)
tb.query_create("","Amt>-10")

viola, the unwanted records no longer appear.

[SNusa]

Re: Sum of selected records...

Other options:
dbsum(c tablename,c tagname,a keyvalue,c field) will work without having to manually open/close the table.
Compute_field_statistics() is also useful for retrieval of certain data displayed in a browse.

TABLES WITH QUERY / FILTER APPLIED:
Same thing as Ray below, only using a block of code in case you want more control, like order.....:
Open table first (before setting the query using Ray's code.)

[B]dim tbl as P [/B]     [COLOR="#A9A9A9"]'explicitly assigning variables is smart & lists them in code auto-complete.[/COLOR]
[B]dim qndx as P[/B]

[B]tbl=table.current(2[/B])    [COLOR="#A9A9A9"] 'open child table using Ray's code[/COLOR]

[B]query.description="Amt. Above -10"[/B]      [COLOR="#A9A9A9"]'set query description[/COLOR]
[B]query.order=""[/B]      [COLOR="#A9A9A9"]'no need to set order[/COLOR]
[B]query.options=""[/B]      [COLOR="#A9A9A9"]'probably no need to set options[/COLOR]
[B]query.filter="Amt > -10"[/B]      [COLOR="#A9A9A9"]'set query filter[/COLOR]
[B]qndx=tbl.query_create [/B]     [COLOR="#A9A9A9"]'apply filter/run query[/COLOR]
[COLOR="#A9A9A9"]'qndx.records_get()[/COLOR]      [COLOR="#A9A9A9"]'number of records returned[/COLOR]
[B]ui_msg_Box("Quantity:",qndx.records_get()) [/B]     [COLOR="#A9A9A9"]'display number returned in message box[/COLOR]