PDA

View Full Version : Sum of selected records...


ABC123

pjstarr
12-27-2013, 04:11 PM
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
12-28-2013, 10:20 AM
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
12-30-2013, 08:57 AM
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
12-30-2013, 08:46 PM
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.)



dim tbl as P 'explicitly assigning variables is smart & lists them in code auto-complete.
dim qndx as P

tbl=table.current(2) 'open child table using Ray's code

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