Here is the layout:

Course

coursepk

coursename

period



LinkTable

linkpk

coursepk

studentpk

period



The above table has multiple records for each course per period.



Student

studentpk

name



grades

gradepk

studentpk

coursepk

period

positioninperiod

testscore



The grid(s) would have (but is fexible):

coursename,studentname,period,testscore



The only updateable field is the testscore and the data appears in positioninperiod order.

Hi Terry,



A couple of questions:



1) Its not clear what what "Period" means, e.g. if LinkTable.Period is the same as Course.Period then one is redundent.



2) Grades.Period seems very suspect. Period has now appeared in three different tables and, it seems, they can all have independent values. Is this correct?



That being said I take it that all tables have existing values and all you want to do it update the Grades.TestScore column. If this is so I would:

.

a) Have one Grid using a BO based on the Grades table. Populate with a stored procedure that sorts on Course.CourseName, Student.Name, Grade.Period and Grade.PositionInPeriod. Show these columns:



   Grades.CoursePK - is a DevEx lookup edit to table Course

   Grades.StudentPK - is a DevEx lookup edit to table Student

   Grades.Period - is whatever is in that column

   Grades.PositionInPeriod - is whatever is in that column

   Grades.TestScore - is a maintainable column.



b) Probably be best to show the GroupHeader box in the grid and put Grades.CoursePK in there (which will be the Course.Name, not the PK of course).



Cheers, Peter

OK I will work on this. Thanks.

I did not design the data structure, I just have to use what is there.

Period is like 8-9am is period 1 and 10-11am is period 3 which might be Algebra classes for a particular teacher.

After some more work on this I will probably come back to this when I get some actual code and forms.....

Taking on existing data structures can be frustrating, and I have just scanned through this post and have seen some good advice from Peter. One thing that could change a some of these answers might be if you are free to add SPROCs or updatable views. If you cannot touch the database at all, then obviously you are 100% stuck with these structures, sprocs, and views. But if you can add some to meet your specific needs, then it would be easier to work with.



At any rate, there was a lot of good advice given here so I will just let you go with what was given for now, just thought I would throw that out there.

Hi Terry,



"Period is like 8-9am is period 1 and 10-11am is period 3 which might be Algebra classes for a particular teacher"



Ok, but I think you can see that having three seemingly independant Period values doesn't seem to be correct but, as I said, if your only interest is in updating a single value is the Grades table then it doesn't make much difference.



I also notice you have also introduced "Teacher" who doesn't appear in any of the tables you have mentioned.



It's sometimes very tough working with an existing database....



Cheers, Peter

I just introduced teacher to explain what a period was. I'm starting to attack this problem and will be back for help I am sure.

In one of your responses you mentioned a lookup edit, am I correct if I want the grid to simply look like this:



and so forth where we are only putting in the score that lookup edit is not what I want to use?

TIA.

I could not get the columns to line up...... sorry

Hi Terry,



I only mentioned lookup edits so you can display the names associated with the Course and Student rather than the PK values. I didn't mean to imply that you would actually edit the data.



Another approach (maybe better in this case) would be to extract these names and include them in your stored procedure that extracts the data for the Grades BO. After you have created the BO from the Grades table, add these extra fields as Custom Fields in your BO. Once you have added the Custom Fields and recompiled the BO they will be visible in the grids data source.



In case your are not familiar here's an example of adding a couple of custom fields to a BO. Note "boPUNType" referred to below is the name of my BO.



Custom Fields are very useful. You can base a BO on a table and add any amount of other info into the BO from other sources however Custom Fields are not updatable. The following example is just a couple of fields but, in this particular BO, we have about 15 Custom Fields.





'''

''' LocationInStore

'''

'''


BusinessFieldDisplayInEditor(), _

Description("LocationInStore"), _

DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _

Public ReadOnly Property [LocationInStore]() As System.String

Get

Dim loValue As Object

If Me.CurrentDataTable.Columns.Contains("LocationInStore") Then

loValue = Me.CurrentRow.Item("LocationInStore")

Else

loValue = DBNull.Value

End If



If loValue Is DBNull.Value Then

Return String.Empty

Else

Return CType(loValue, System.String)

End If

End Get

End Property



'''

''' Quarantined

'''

'''


BusinessFieldDisplayInEditor(), _

Description("Quarantined"), _

DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)> _

Public ReadOnly Property [Quarantined]() As Nullable(Of System.Boolean)

Get

Dim loValue As Object

If Me.CurrentDataTable.Columns.Contains("Quarantined") Then

loValue = Me.CurrentRow.Item("Quarantined")

Else

loValue = DBNull.Value

End If



If loValue Is DBNull.Value Then

Return CType(Nothing, Nullable(Of System.Boolean))

Else

Return New Nullable(Of System.Boolean)(CType(loValue, System.Boolean))

End If

End Get

End Property





Protected Overrides Function GetCustomBindablePropertyDescriptors() As MicroFour.StrataFrame.Business.FieldPropertyDescriptor()



Dim boPUNType As System.Type = Me.GetType()



Return New MicroFour.StrataFrame.Business.FieldPropertyDescriptor() { _

New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _

"LocationInStore", boPUNType), _

New MicroFour.StrataFrame.Business.ReflectionPropertyDescriptor( _

"Quarantined", boPUNType)}

End Function



Cheers, Peter

I think I just had the AH HA Moment. The Lookup Edit is not used to look up a student--the grid uses it to lookup the student name that corresponds to that record in the grid. Is that correct?

Hi Terry,



That's it. Just a way of presenting useful info to the user.



Cheers, Peter