Tale of 2 Portals and 3 tables

[Justin Close]

This question is related to handling records in a transactional way by using portals to create related records.  In my first use case, though, the transactional part isn't really important; but there is a second use case where I would like to use the same structure (scripts and schema) where transactions would be important.
The general structure currently:  I have 4 tables related in a straight chain, ala an invoices-lineitems-sub_lineitems fashion.  But the 1st table isn't relevant in this issue I don't believe.  I attached a picture of the relationships.  The important ones in this case, I believe are the Revisions, LineItems, SubLineItems tables.

The relationships are pretty straightforward, excepting that I am cascading some values down the chain.

Quotes::QuoteID_pk = Quote_Revisions::QuoteID_fk
---------------
Quote_Revisions::aRevisionID_pk = Quote_LineItems::aRevisionID_fk
Quote_Revisions::QuoteID_fk = Quote_LineItems::aQuoteID_fk
----------
Quote_LineItems::aLineItem_ID_pk = Quote_SubLineItems::aLineItem_ID_fk
Quote_LineItems::QuoteID_fk = Quote_SubLineItems::aQuoteID_fk
Quote_LineItems::aRevisionID_fk = Quote_SubLineItems::aRevisionID_fk
Quote_LineItems::subLineItem_key_g = Quote_SubLineItems::aLineItemID_fk

I have used the QuoteID and the RevisionID as part of the relationships all the way down the chain, on the idea that they would make data entry simpler:  when the new related record is created those fields would automatically populate.  Yes, it is duplicate entry, but I would like to have those IDs in each table locally so that an ESQL call can be made against a single table without having to create complicated joins in the query.  The 2nd use case will be doing this.  In the LineItem <=> SubLineItem relationship I also added a global key field to control which line items would be shown in one of the portals (see below).

The UI is this:

One layout, based on Quote_Revisions.  This layout has a slide panel with 2 parts: one part has a portal looking into LineItems, the second panel has a portal looking into SubLineItems.  Each portal has 'create records' enabled.  There is a button on each portal that will create a new related record in that portal.  So you start out looking at a Revision record with it's data shown at the top, and the portal to Line Items is first.  Then if you click on a Line Item portal row, it sets the global key field and switches the slide panel to the other pane that shows the SubLineItems portal (but of course, still showing the Revision data).  And then you can push a button on the SubLineItems portal that fires a script to create a new SubLineItems record there.

The problem I have is in creating the related records in this SubLineItems portal.  It seems that when I try to create a new related SubLineItems record through that portal two things are happening:  one, the record gets created, but it also creates a new LineItems record in the process; two, the SubLineItems record gets related to the 1st LineItem record, even if it was the third LineItem you clicked on to navigate to the SubLineItems portal (and thus the global key field is set to, say, 3).

I'm guessing that my troubles are coming from the fact that this portal to sublineitems is starting from the context of the layout's base table, which is Quote_Revisions.  Thus the magic key technique, when it creates the record at that third level, relates it to the first related record in the set at each chain.

But how can this be accomplished with transactional techniques?  Would I have to create a new relationship directly from Revisions to SubLineItems just to accomplish this?  Using the portal to create the record doesn't quite seem to be working.  I would like to stay on the same layout; I imagine that making a new layout based on the LineItems table (instead of Revisions), would then allow the new related record to be based off the context of a specific LineItem record instead of it having to try and figure things out from the context of Revisions.  But that's not an ideal solution it seems - having to create that new layout:  Screen flash, no nice slidey effect, repeated work, etc.

I also suppose that I might alleviate some of my pain if I left the relationships with just 1 or 2 predicates instead of the 3 or 4, and then manually wrote the other duplicate/related data via script steps.  Not real difficult, fortunately.

The 2nd use case is that my next task is to create a system for duplicating a set of related records.  This is where the Quote_Revisions table comes into play.  We want to provide a method for the user to copy and existing Revision, and all of its related records, to a new Revision that they can then edit.  This is why I feel I need to use transactions, so that when creating the multiple related records I can easily revert things if a problem occurs in the process. 

Wow, that went on longer than I thought it would.  Any one willing to take a crack at this? 

- Justin

 

[dwdata]

Hey Justin,

Attached is a little sample file that demonstrates some scripting techniques. Also, check out the relationship graph. The UI uses relation tunneling to get Revisions's Items to display properly.

Hope this helps! As always, please reply if you have any questions.

quotes.zip