0

What is the recommended way to summarize detail records in a SQLite table like the following SQL code. Have the user review the summarized records, currently in a DBGrid, then once all looks good the user can then highlight the row(s) and press a "Processed" button that should then update the detail records with a "processed" code.

Currently I am using Delphi VCL which has a datasource ( SQLite file ), a Devart UniDAC UniQuery and a DBGrid. The UniQuery has the following SQL code:

SELECT PartNum, StoreLoc, CostCenter,
  SUM( Weight )
  FROM tblMaster
  WHERE Weight > 0
  GROUP BY PartNum, StoreLoc, CostCenter
  HAVING Processed = 0

This actually populates the DBGrid perfectly and I can select a single row, or multiple rows. I should be able to then press a button that acknowledges the transactions which should change the Processed field in the detail record(s) from 0 to 1.

CREATE TABLE tblMaster ( PartNum text, Weight real, Emp text, Defect
text, StoreLoc text, CostCenter text, Date text, Time text, Processed
text );

Here is some sample data:

sqlite> select * from tblMaster
9986-0000|1.2|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0000|0.5|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0000|1.0|E1|D1|S2|CC1|6/28/2019|13:55|0
9986-0000|1.2|E1|D1|S3|CC1|6/28/2019|13:55|0
9986-0022|1.2|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0022|0.5|E1|D1|S1|CC1|6/28/2019|13:55|0
9986-0022|1.0|E1|D1|S2|CC1|6/28/2019|13:55|0
9986-0022|1.2|E1|D1|S3|CC1|6/28/2019|13:55|0

The only way I can think of is to take the highlighted row(s) from the DBGrid and simply read though each detail record and update the Processed field.

  • 1
    If you're basing it on selections in the DBGrid, there's not really an alternative to just iterating through the selected rows and performing individual updates. If you're updating multiple detail rows, you can do it with SQL and parameters in a loop, but you still have to base that loop on the selected rows in the DBGrid. – Ken White Jun 30 '19 at 03:58
  • Thanks Ken. I suspected so but wanted a second option. – Larry Jul 01 '19 at 01:45

0 Answers0