StrataFrame Forum

ONE TO ONE RELATIONSHIPS

http://forum.strataframe.net/Topic8551.aspx

By Tim Dol - 4/27/2007

I'm working with some legacy data that has two tables for customer information and I'm having a few problems. Perhaps I'm making this more difficult than it really is but I can't seem to get it to work like a 1:n relationship.

Example:  Table: Customer1 - Customer Code(Char(12),PK), Customer Name, Address...

              Table: Customer2 - Customer Code (Char(12),PK), Main Contact, Phone, Email

Note: PrimaryKeyIsAutoIncremental = False

I created two business objects. I set the ParentRelationShip on Customer2.

Questions:

1) How Do I handle Child Dialogs where I have two or more business objects to populate. My child dialog contains two BO's and the fields are mapped to both tables on the childformdialog.  On the parent form I added Customer1.Add() and Customer2.Add() before launching the childform.  When I save the childform, the data in Customer2 does not save.  Customer1 is fine. (Note, I also set the parent Business Object property on Customer2 on the parent form)

2) When Editing, I seek to the selected primary key on customer1 before launching the child form. Do I need to fill the Customer2 BO as well at this point or do this on the child form, which relates to question 3.

3) The Primary business object on the child form is Customer1. How do I retrieve the related data from Customer2 when navigating the BO?

4) With respect to Foreign Key Management and Auto Child Record Filtering, as described in the docs - Are these features supported with one to one relationships and if the PrimaryKeyIsAutoIncremental = False?

Thanks

Tim

By StrataFrame Team - 4/27/2007

I think the root of your problems is the PrimaryKeyIsAutoIncremented = False problem.  Basically, when you turn off auto-incrementing on the PK fields, you have to explicitly assign the value to the PK field, and you're not required to do this at a specific time other than it has to be done before you save.  When you add a record to a child BO and the relationship is configured (which you do have properly configured), the current value of the PK within the parent is pulled into the FK of the child.  When you change the PK value of the parent, it is not automatically copied down into the FK of the child... the only time a BO does this is when the PK is auto-incremented and the save occurs, allowing the parent to cascade the values down to the child.  So, if you add a child record before you set the PK on the parent, the blank PK value is copied into the child, and this looks like your problem because you're calling the Parent.Add() and Child.Add() one right after the other.  So, to fix this, you can do one of 2 things: wait until you have set the PK on the parent before adding the child, or manually copy the parent PK value into the child when the parent PK changes.  Do that, and the child record will save fine.
By Tim Dol - 5/10/2007

Thanks Ben, I was able to make this work based on your suggestion.

I do have another question regarding this example.  What is the best way to 'join' the data from these two tables so I can display on a listview or grid.(They should really be one table but I have to live with this for now).  Right now I have been adding custom properties to the 'Customer 1' table as needed to include the fields from 'Customer 2', but I have the requirement to include all or at least the majority of them. Should I just add a bunch of custom properties? Will this be slow?   Can I somehow combine both tables into one business object?

I would appreciate your input.  I have 4 or 5 more tables like this so I need to come up with a good solution.

Thanks

Tim

By StrataFrame Team - 5/10/2007

Adding the custom properties isn't going to be slow, so don't worry about that.  However, if you want to build a business object for display purposes that is a join of both business objects, you can create a view on your SQL Server that is a join of both tables.  Then, create a new business object and map it to the view on SQL Server (it will show up in the BOMapper as just a table).  That way you can have one business object that contains the fields for both.
By Tim Dol - 5/14/2007

Ben/Trent, I thought I had this working but it turns out I don't. 

I understand the fact I have to manually set the PK for my child records, but where is the proper place to do this?  I tried setting the Child PK equal to the Parent PK in the BeforeSave event of the Parent business object and also tried it in the child business object (separate tests), and the child record is still not being created.   Edits seem to work fine. 

Also, how do you suggest I handle the following situation.  In our legacy application the child record does not have to exist, so when I'm editing the Parent record I want to create the child record if it doesn't exist. Should I place code in the parent BO EditingStateChangedEvent to check the existence of the record and if it does not exist, perform a add() on the child BO?  Note: some of the child fields are on the maintenance form.

Thanks

Tim

By Greg McGuffey - 5/14/2007

Tim,



I'd try the SetDefaultValues event on the child BO (set child PK to the parent PK).




By Tim Dol - 5/15/2007

The primary key is one of the fields on the form (Customer Code) so I can't set in the defaultvalues section, although it may solve my other issue when the parent exists and the child does not. 
By Greg McGuffey - 5/15/2007

I'm actually talking about handling that event on the form, not in the BO (or in addition to). In the BO you'd have no idea what to set it to, but on the form, were I'm assuming the child BO is being handled, you would.