DONE: Can you force agreement in cascading Dropdown/Lookup?

Question

I'd like to force cascading drop down menus to stay in valid relationship when the hierarchy gets beyond two levels. I think I followed the rules for creating these properly. An example will help.

-- I used a Sandbox db to host tables for the example
***
USE Sandbox
GO
IF OBJECT_ID('dbo.Parent')IS NOT NULL DROP TABLE dbo.Parent
IF OBJECT_ID('dbo.Child')IS NOT NULL DROP TABLE dbo.Child
IF OBJECT_ID('dbo.GrandChild')IS NOT NULL DROP TABLE dbo.GrandChild
GO

CREATE TABLE Parent (

ParentId   TINYINT     IDENTITY(1, 1) PRIMARY KEY NONCLUSTERED

, ParentName VARCHAR(32) NOT NULL UNIQUE CLUSTERED)
GO

CREATE TABLE Child (

ChildId   SMALLINT    IDENTITY(1, 1)

, ParentId TINYINT NOT NULL FOREIGN KEY REFERENCES dbo.Parent(ParentId)
, ChildName VARCHAR(32) NOT NULL
, PRIMARY KEY CLUSTERED(ParentId, ChildId)) -- PK set to properly cascade
GO

CREATE TABLE GrandChild (

GrandChildId   SMALLINT    IDENTITY(1, 1)

, ParentId TINYINT NOT NULL
, ChildId SMALLINT NOT NULL
, GrandChildName VARCHAR(32) NOT NULL
, PRIMARY KEY CLUSTERED(ChildId, GrandChildId)) -- PK set to properly cascade
GO
-- FK using both columns of Child table
ALTER TABLE dbo.GrandChild ADD CONSTRAINT FK_GrandChild_Child FOREIGN KEY (ParentId,ChildId) REFERENCES dbo.Child(ParentId,ChildId)
GO

-- test data
INSERT INTO dbo.Parent (ParentName) VALUES('Alex Alexander'),('Gus Augustine')
GO
--SELECT * FROM dbo.Parent p

INSERT INTO dbo.Child (ParentId,ChildName)
VALUES(1,'Al Alexander')
,(1,'Alec Alexander')
,(1,'Aleck Alexander')
,(1,'Lex Alexander')
,(1,'Sander Alexander')
,(1,'Sandy Alexander')
GO

INSERT INTO dbo.Child (ParentId,ChildName)
VALUES(2,'Aggy Augustine')
,(2,'Augie Augustine')
,(2,'Gusty Augustine')
,(2,'Tina Augustine')
,(2,'Austin Augustine')
,(2,'Gussy Augustine')
GO

--SELECT FROM dbo.Child c
**

Now open dbFront and go to the Grand Child form.

The Child drop down is properly restricted - you have to pick a Parent first.
Pick a parent, then you can pick a child. So far so good.

But now that the menus are both activated you can change the parent drop down to an invalid choice.

If you do cross up the parent and child then enter a grand child name and hit save, the FK error is thrown.

I'd like to prevent the ability to pick an invalid parent. Possible?

Comments

Perhaps make it so that, on change of the parent value, the child menu is cleared and reloaded from the DB. This will prevent the mixed values in the child menu

---

Thanks for all of the details.   I will look at this soon..

Answer 1

This functionality was introduced in dbFront 1.0.9.1628

When a new parent row is selected the child select will be reset to match.

For detailed instructions on creating a cascading dropdown or lookup see: Cascading Lookups/Dropdowns