Web Form for Many-to-Many relationship

[1-MEG]

I am currently evaluating Alpha 5 v10 and have a question that I cannot seem to solve, even after looking through the other posts.

I have created a DB in MSSQL with a few many-to-many relationships so that it is properly normalized. For example, I have a "Phones" table that can store multiple phone numbers for many different entities. Those entities are defined in other tables, such as "Persons". I then have linking tables such as "PersonsPhones" to form the many-to-many relationship.

Using the example above, here are the tables:

[Persons]
PersonID (pk, int)
First Name
Last Name

[Phones]
PhoneID (pk, int)
PhoneNum

[PersonsPhones]
PersonID (pk, int)
PhoneID (pk, int)

The third table uses both fields to form the primary key, and there are foreign keys on each individual field relating them back to the other two tables. Sample data:

[Persons]
1, John, Doe
2, Sally, Smith

[Phones]
1, 111-111-1111
2, 222-222-2222
3, 333-333-3333
4, 444-444-4444

[PersonsPhones]
1,1 (John Doe linked to 111-111-1111)
1,3 (John Doe linked to 333-333-3333)
2,2 (Sally Smith linked to 222-222-2222)
2,4 (Sally Smith linked to 444-444-4444)

What I want to create is a Linked Grid where the [Persons] are the main grid, and whenever a row is highlighted, all the Phones that are linked to that person appears in a sub grid of some sort. I have been successful in creating an SQL view that combines the [Phones] and [PhonesPersons] tables in to one flat view, and linking it as the sub grid. The problem is it is not updateable. I want to be able to add and delete phones and have the appropriate records inserted into the [Phones] and [PhonesPersons] tables automatically.

Any help would be appreciated.

[roberto]

Re: Web Form for Many-to-Many relationship

I have not done it with a View but I think I have done a similar task using JOINS. Grid --> Query (AlphaDAO) --> Based on: SQL Select Statement. There is a useful SQL Query Builder under the "Edit SQL" button on the "SQL Text" tab. The SQL Query Builder is a time saver when creating joins.

[bob9145]

Re: Web Form for Many-to-Many relationship

Michael,
Welcome to Alpha. I have struggled with this one myself. I am not sure if a join is update-able in MSSQL or which databases it might be. I use mysql and I don't think it is directly.

I have made a few posts on this issue. It doesn't seem like the grid handles this well. You can use the sql events to do just about anything you want but it takes additional coding.

If you eliminate the linking table you can easily use linked grids.You would just have the phone numbers in a separate table with a foreign key link to the primary table. However this doesn't resolve the many to many relationship.

I have not gotten much response on this maybe some of the alpha vets can weigh in.

As far as Alpha goes its a pretty amazing product and the people who use it and post on this board collectively are a powerhouse of knowledge.

Bob

[Peter.Greulich]

Re: Web Form for Many-to-Many relationship

Why do you need the PersonsPhone table? Why don't you just add a PersonId field to the phones table and use a 1:M linked grid(s)?

[bob9145]

Re: Web Form for Many-to-Many relationship

Peter,
What if it was a company number and other people were using the same number. There are a a lot of reasons why you might normalize data like this.

I guess the point is that linking tables are commonly used, so how are they best manipulated through the grid or linked grids?

Tks
Bob

[bob9145]

Re: Web Form for Many-to-Many relationship

Surprised that there is not more feed back on this scenario.
Guess the powerhouse of knowledge I was talking about took the last train for the coast.

[Peter.Greulich]

Re: Web Form for Many-to-Many relationship

I want to be able to add and delete phones and have the appropriate records inserted into the [Phones] and [PhonesPersons] tables automatically.

The same problem exists w. dbf. If you have a view the phones table is not updateable. You need to link to another grid for your edits or entries. Just add a button or link to your rows w. some Aciton JS linking to your edit/insert grid.