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.
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.
Comment