DBGrid doesn't display values filered via DBLookupbox
lazarus / 16-Databases

----------------------------------------------------------------------
talorigomat · 2014-04-06 07:20

I have a form with two DBLookUpComboBox (Level1Cmb and Level2Cmb) and
a DBGrid (Level3DBG) each connected to their own datasource and query
.  The idea is to show an organisation's structure.  So when the user
selects the top level of the organisation in Level1Cmb then Level2Cmb
will show the middle levels, and Level3DBG will show the bottom level.

When the form opens it displays the first record and everything seems
to work fine.  When you select a different value in Level2Cmb the
Level3DBG changes accordingly.  If you however select a different
value in Level1Cmb then Level2Cmb displays the correct values however
Level3DBG displays nothing.

The SQl in the SQlQuery component for Level3GDB is  "select ORGID,
ORG_NME AS Name, LEVEL_NUM, PARENT, INUSE from organisation WHERE
PARENT = :ORGID ORDER BY Name;"

I've attached a zip file with the project.  I'm using Firebird for the
database.

----------------------------------------------------------------------
mangakissa · 2014-04-06 09:38

Build a master/detail with your lookuptables.

----------------------------------------------------------------------
talorigomat · 2014-04-07 01:07

A master detail relationship already exists.  The components are set
up as:

Level1Qry has its SQL property set to: "select ORGID, ORG_NME AS Name,
LEVEL_NUM, PARENT, INUSE from organisation WHERE Parent IS NULL ORDER
BY Name;"

Level1Src property
  dataset = Level1Qry

Level1Cmb
  Datasource = Level1Src
  Datafield = Orgid
  Keyfield = Orgid
  Listfield = Name
  Listsource = Level1Src

---
Level2Qry properties are set as follows:
  SQL = "select ORGID, ORG_NME AS Name, LEVEL_NUM, PARENT, INUSE from
organisation WHERE 
              LEVEL_NUM=2 AND PARENT = :ORGID ORDER BY Name; "
  Datasource = Level1Src

Level2Src property
  Dataset = Level2Qry
 
Level2Cmb
  Datasource = Level2Src
  Datafield = Orgid
  Keyfield = Orgid
  Listfield = Name
  Listsource = Level1Src


---
Level3Qry properties are set as follows:
  SQL = "select ORGID, ORG_NME AS Name, LEVEL_NUM, PARENT, INUSE from
organisation WHERE
         PARENT = :ORGID ORDER BY Name; "
  Datasource = Level2Src

Level3Src property
  Dataset = Level3Qry
 
Level3DBG
  Datasource = Level3Src
 
The Organisation table itself has a self join where any value in the
PARENTcolumn must match a value in the ORGID column or be Null.

----------------------------------------------------------------------
talorigomat · 2014-04-08 08:31

What is occurring is that whenever a different value is selected in
Level1Cmb, Level3Qry Active property automatically gets set to false. 
Therefore need to set the its Active property back to true.

Not sure why this happens as it doesn't occur when a different value
is selected in Leve2Cmb.

----------------------------------------------------------------------
talorigomat · 2014-04-08 23:07

Just to add to get it to work I had to set the SQLQuery component to
active in the DBLookupComboBox's OnCloseUp Event Handler.