Inserting into Multiple Table from a Single GRID

[rogerrabbit222]

Here is a basic scenario that needs help. This is a feature which is often used in various projects and I want it to work from the GRID;

Assume we have a External DB structure with 2 table which hold a 1-to-1 relationship. I am using MS-SQL 2005.

The tables are:

1. user: iduser_pk,username,password
2. user_details : id,iduser_fk,fname,lname,email,phone,

Note: A record in user_details SHOULD NOT EXIST without its parent record in user table.

Building a GRID using a join on these 2 tables for Update joined table is not a problem at all.

But we want to solve these scenarios:

1. A SINGLE Updatable GRID which will Add New Records and INSERT INTO BOTH TABLES.
2. Failure to INSERT into either one of the tables should RollBack the entire process. i.e. No records should be inserted in the user or the user_details table.
3. Each field may have their own ValidationRules that can be set from the GRID. Invalid entry should not make the GRID to insert.

The basic requirement is to have a GRID allow INSERT into multiple tables from witthin a GRID.

[peteconway]

Re: Inserting into Multiple Table from a Single GRID

When you open a the grid select the view function at the top uf the dialog - view sets can be written to as one file.

[rogerrabbit222]

Re: Inserting into Multiple Table from a Single GRID

When you open a the grid select the view function at the top uf the dialog - view sets can be written to as one file.

Where is this view function that you are referring to?

[peteconway]

Re: Inserting into Multiple Table from a Single GRID

Open Grid Builder
Design
Query (DBF)
Display Data From
View not Table

[Bob Moore]

Re: Inserting into Multiple Table from a Single GRID

The view is available for a DBF file.
For SQL, it is not an option.

One option that comes to mind is to use a dialog for entry of all values required and write some xbasic code to create a record for the parent and the child tables with the appropriate values upon submission of the form.

You could also create a table that is essentially the union of both tables and base a grid on that. Once it is submitted, you could then write some xbasic code to create the records and submit the appropriate data to the respective tables. You could also let SQL Server do the work with a trigger tied to a stored procedure.

[Al Buchholz]

Re: Inserting into Multiple Table from a Single GRID

You could also let SQL Server do the work with a trigger tied to a stored procedure.

Or you can build a view in SQL and have Alpha access that view.

That is not an Alpha view, but an SQL built view...

[Maroine81]

Re: Inserting into Multiple Table from a Single GRID

Thank you Al for your perfect solution ... that is exactly what i was looking for.

I have a parent table that has 2 children tables in one to one relationships (hierarchy with a type discriminator) who each has 2 other children ... I designed it that way in MS SQL to remove redundancies. The MS SQL view is the right approach to allow inserts into the children tables and force validation rules.

Thanks again

[Al Buchholz]

Re: Inserting into Multiple Table from a Single GRID

Looks like a consensus solution to me...

Glad it helped...

[MarionT]

Re: Inserting into Multiple Table from a Single GRID

Al.....

can you please expand the fishing lesson?

I have a SQL backend, with views.

Are you saying that I can build an updatable grid in A5, using a SQL view as the datasource??? And insert rows? How does the SQL Insert code get generated? How would A5, or SQL, know where to put the values?

[Al Buchholz]

Re: Inserting into Multiple Table from a Single GRID

Looks like Maroine has a solution.

Maroine can you share?

[MarionT]

Re: Inserting into Multiple Table from a Single GRID

In case it helps me or someone else someday, this is what I learned today

My challenge (for SQL backend):
Insert 1 row in an updateable grid in A5 as:
Company | Location | StartDate | EndDate
XYZ | Tulsa | 9/1/2012 | 9/30/2012

End up with:
1 row inserted into ParentTable as:
Company | Location | ParentID
XYZ | Tulsa | 1

2 rows inserted into ChildTable:
ParentID | Date | ChildID
1 | 9/1/2012 | 1
1 | 9/30/2012 | 2

Here's what I did:
1. Create a DummyTable in SQL: Company | Location | StartDate | EndDate

2. Create a Stored Procedure in SQL which xfers data from the dummy table to the Parent and Child tables

3. Ignored all references & suggestions for dealing with SQL Triggers.... I have not used Triggers, and I couldn't figure out why they'd be necessary for this task.

4. Follow Selwyn's #78 video sequence, about Customizing SQL Insert, Update and Delete Statements, using Stored Procedures to Update your Database.

Note - I wasted about 1-2 hours, because I had dim flag as 1 (ie, the digit 1) and it needs to be dim flag as l (ie, the letter between k and m)


This works, and fees like a good accomplishment to have learned today.

[Maroine81]

Re: Inserting into Multiple Table from a Single GRID

Sure Al ... I will be happy to.

MarionT ---> here is how I was able to insert update and delete into multiple tables in the mssql backend using an updatable view and a single grid.

Let's start with the creating an SQL hierarchical ERD where you have for example the following structure and relationships: (Vehicule is the parent table and has two 1 to 1 relationships with SUV and SEDANS) and then SEDANS is parent itself that has 2 children TWODOOR and FOURDOOR:
ERD_Hierarchy.png

CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL CHECK(vehicle_type IN ('SUV', 'SED')),
UNIQUE (vin, vehicle_type)
);

CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SUV' NOT NULL CHECK(vehicle_type = 'SUV'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE
);

CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT 'SED' NOT NULL CHECK(vehicle_type IN ('2DR', '4DR', 'SED')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE
);

CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '2DR' NOT NULL CHECK(vehicle_type = '2DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE
);

CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT '4DR' NOT NULL CHECK(vehicle_type = '4DR'),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type) REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE
);

Keep in mind that the type disciminator (in this case vehicule_type) differentiates between the children tables and that the children tables inherit all the parent's attributes and relationships!!! This is considered best practice for normalisation purposes when creating these types of parent-child 1 to 1 relationships.

Now after that, you can create a view based on both the parent and child tables (make sure you include the primary keys and types of each table in the view)

Once you have done that you create "INSTEAD OF triggers" on insert / delete and update to make the view UPDATABLE. Here is simple tutorial on how you can do that.

Last but not least you create a grid that is based on the updatable view you just created! which will allow you to insert update and delete records into multiple tables using ONE single grid.

Voila!

[MarionT]

Re: Inserting into Multiple Table from a Single GRID

thanks so much for the detail -
and the link to the tutorials on triggers -
I haven't created any yet, but they seem manageable to me.

I'll play with this later tonight

I like the idea of having a variety of methods in my pocket for achieving the same end result

[Maroine81]

Re: Inserting into Multiple Table from a Single GRID

You're welcome
I am glad I could help

[MarionT]

Re: Inserting into Multiple Table from a Single GRID

Maroine81

I'm not an actual developer, just a couple of years learning under my belt.

I may be mistaken, but I think our methods both achieve the same thing, no?

I'm doing it via a 'cache' table, and a stored procedure which is executed from A5 (the SP xfers the data/updates from the cache to the proper tables)

you're using triggers


I guess I feel more in 'control' with the Stored Procedure, but that's probably just because I know how to use them, and I haven't learned triggers yet...