How to Build a Hierarchy of TDBLookupComboboxes

[DavidAM]

It has been a number of years since I last worked with Lazarus; and many (many) more years since I used Delphi. I am re-learning Lazarus (and Pascal) by developing (what I thought would be) a simple utility. I am using Lazarus 3.2 (rev lazarus_3_2) FPC 3.2.2 x86_64-win64-win32/win64. Developing against a Sqlite database (version 3.35.4).

I have three tables: Activities with a Foreign Key to Categories with a Foreign Key to Groups. So I have a hierarchy.

  Groups
    | 1
    |
    | M
Categories
    | 1
    |
    | M
Activities

I am starting with a form to allow Add and Edit of an Activity record.

On the form I have 3x TSQLQuery (one for each table) and 3x TDataSource (one for each table), they are linked appropriately. The TSQLQuery controls are linked to a TSQLite3Connection and a TSQLTransaction (which are actually on the main form). This all seems to be properly linked and working. - I was able to activate the TSQLQuery controls and load the Fields list in each.

I also have 2x TDBLookupCombobox controls (cmbGroup and cmbCategory). I am trying to link these up so when I load an Activity (in a yet to be written procedure EditActivity(piActivityID: Integer)) they will display correctly and allow changes - the cmbCategories control will display the Category and the cmbGroup control will display the (Category's) Group.

cmbGroup has ListSource set to dtaGroups and ListField set to ShortName and KeyField set to ID. The DropDown list fills correctly (all Groups in the Table). It has DataSource set to dtaCategories and DataField set to GroupID.

cmbCategory has ListSource set to dtaCategories and ListField set to ShortName and KeyField set to ID. The list fills with ALL Categories in the table. It has DataSource set to dtaActivities and DataField set to CategoryID.

I am working on the first Procedure AddActivity(piGroupID: Integer). This is called from the main form, passing in the GroupID for the new Activity. I would like to 1) set the cmbGroup "value" to the passed GroupID (displaying the Group's ShortName); and 2) have the cmbCategories list refreshed and limited to the Categories in that Group. But I cannot assign to the ID in the Groups table, and I cannot assign to the GroupID in the Categories table - "Operation not allowed, dataset "xxxxx" is not in an edit or insert state." (xxxxx is either sqlCategories or sqlGroups depending on which I am trying to assign to).

Also, if I select an entry from the DropDown List in cmbCategory, it does NOT update the selection in cmbGroup. I would expect it to since the DataSource and DataField are set correctly in cmbGroup.

Is it even possible to have a hierarchial set of DBLookupComboboxes, or do I need to start down a different path? If it is possible, how do I accomplish 1 and 2 above? And is there anything special I will need to do when I get to the EditActivity procedure (as far as displaying the correct entry in the cmbGroup control)?

Thank you for considering this little problem.


p.s. I have very little free time to work on this; so I apologize in advance for any delay in responding to or testing responses I get here.

[dseligo]

1. You didn't posted complete compileable project Timers.lpi file was missing, and there was method FormContextPopup which didn't have implementation.
2. If you put sqlCategories.Edit; in line 166 of file ufmEditActivity (in front of sqlCategories.FieldByName('GroupID').Value := piGroupID;) then 2nd forms opens without error.
3. You have memory leaks in project. In ufmMain.pas in line 257 you create buttons, but you don't free them. You can create them like below (instead of btn := TButton.Create(nil);) and they will get freed when program ends:

Code: Pascal  [Select][+][-]

1. btn := TButton.Create(frmMain);

[DavidAM]

Thanks for the response.

1) I apologize, I will be sure to include the .lpi file in any future postings.

2) I didn't think I should Edit the sqlCategories, as the form is for Editing the Activities table.

3) I have a method to Destroy the buttons (line 303). It is called when closing a database. Since the number of Buttons will be different depending on which database is opened, and since the user can open and close databases, I decided to destroy them when the DB is closed. I will review the code, to see if I left out a call to RemoveButtons() anywhere. Is it OK to use Destroy or should I use Free?

I have actually figured out how to accomplish the hierarchy, I was typing my solution when you posted your response. I will post my solution in just a few minutes.

Thank you, again, for taking the time to review my code and provide your insights.

[DavidAM]

Well, I managed to get this working!

I added a where clause to the sqlCategories SQL statement with a parameter for the GroupID:

Code: SQL  [Select][+][-]

1. SELECT ID, GroupID, ShortName, FullName, IsInactive
2. FROM Categories
3. WHERE GroupID = :GroupID
4. ORDER BY ShortName

The "entry point" procedure (for a New Activity) is basically:

Code: Pascal  [Select][+][-]

1. procedure TfrmEditActivity.TimerNew(piGroupID: integer);
2. var
3.   iModalResult: integer;
4. begin
5.   Self.Caption := 'New Activity';
6.   sqlActivities.Append();
7.  
8.   // Without the Close/Open the Group does not get selected
9.   sqlCategories.Close();
10.   sqlCategories.ParamByName('GroupID').AsInteger := piGroupID;
11.   sqlCategories.Open();
12.  
13.   iModalResult := Self.ShowModal();
14.  
15.   if iModalResult = mrOK then
16.   begin
17.     // Save the record
18.     sqlActivities.ApplyUpdates();
19.   end;
20. end;
21.  

The key point was to Close the sqlCategories control, assign the GroupID to the Parameter and then Open the sqlCategories control (lines 9 thru 11). This caused the Group combobox to select the correct entry and the cmbCategories List to contain only entries that belong to the specified group.

Then I had to add an OnSelect event procedure for the Group combobox:

Code: Pascal  [Select][+][-]

1. procedure TfrmEditActivity.cmbDbGroupSelect(Sender: TObject);
2. var
3.   iGID: integer;
4. begin
5.   // Capture the selected GroupID
6.   iGID := sqlCategories.FieldByName('GroupID').AsInteger;
7.  
8.   // Without the Close/Open, the Categories List does not refresh
9.   sqlCategories.Close();
10.   sqlCategories.ParamByName('GroupID').AsInteger := iGID;
11.   sqlCategories.Open();
12. end;
13.  

When a Group is chosen (in the combobox), I had to reload the sqlCategories control. This required me to Close it, Assign the new GroupID to the parameter and then Open it. The significant point was that I had to get the ID of the selected Group from the GroupID field of the sqlCategories control before I closed it.

[dseligo]

Is it OK to use Destroy or should I use Free?

Destroy is more 'low-level', Free checks for 'nil' before calling Destroy, so it's safer to call Free.

I have actually figured out how to accomplish the hierarchy,

I am glad you work it out.

[CharlyTango]

1) I apologize, I will be sure to include the .lpi file in any future postings.

There is a distinct function in Projects - Publish Project which will deliver all necessary Files of a Lazarus Project to a zip file