3 Replies Latest reply on May 3, 2009 3:13 AM by comment_1

    One Child, Two Parents where only one creates, the other adopts



      One Child, Two Parents where only one creates, the other adopts


      I'm a little puzzled about something. Lets say you have a list of line-items created by an invoice. Like, Invoice and LineItem are both tables where the LineItem is a child table. Another table, Process, will subsequently be related to a line-item record, only in this case, we're not allowing the Process layout (based on the Process table) to cause a line-item record to be created. Since the LineItem is a join table between the Process and Invoice tables, it makes sense to allow only one table to initiate the creation of a record in the LineItem table. So relating a record between LineItem and Process is done by assignment, and the LineItems are only created in the context of an Invoice record (which also means they always relate to one and only one invoice record, and never to none). 


      To assign a LineItem record to a Process record, we're going to insert a copy of our target Process record's primary key into the foreign key field in the LineItem table. To do this we need to see a list of LineItems that are not yet related to any Process record (that are unassigned). This could be done with a global text field, placed in the Process table, where we type "N" into the global text field, and then delete this field from our layout. We then create a text field in the LineItem table, and set the field up to automatically create "N" for each new record. To make this work, we must create a relationship between globalTextField in the Process table, and the textFieldSetInitiallyToN in the LineItem table, and set the the relationship operator to "equal" (so it's an equijoin). Then, in usage or practice, we set the text field in a LineItem record to Y (while also assigning it to a Process record by inserting the correct Process key copy into the foreign key field in the LineItem record -- which means we are changing or updating two fields in a LineItem record during the act of assignment). 


      But, what about not wanting null values? For instance, until a LineItem record's foreign key field is filled, its empty. The relational theory frowns on empty attributes. So what if instead of creating a global text field and setting it to "N", one were to create a global number field and set it equal to -1 (negative one, to be explicit and clear). Then also, instead of a text field in the LineItem record that is automatically set to "N" upon the records creation, if one were to use the already-created-foreign-key field but in this case set it up so its set to -1 upon each records creation. Then to have a relationship between global-number-field-set-to-"-1" in the Process table and the-foreign-key-initially-set-to"-1" in the LineItem table. 


      Now, when we assign a LineItem record to a Process record, the very act of inserting the correct Process primary key into the LineItem record's foreign key field will cause the LineItem to be both removed from the list of unassigned LineItem records, and also assigned to the correct Process record. In this way, the foreign key field in the LineItem would serve a dual function, and there would be no null values in the foreign key field, and only one field in the LineItem record would have to be updated to both assign a LineItem record to a Process record, and remove the same LineItem record from the list of unassigned LineItems.


      I'm confused on which path to take, and what I might be failing to see or realize. 


      (Note, that I've left unsaid how one creates a value list of unassigned LineItems, so hopefully the wise reader can focus on my question, which is confusing enough.)


      To add to this: what if use the -1 approach, as the first means of removing a LineItem record from the unassigned LineItems (again, we would remove by changing the value of -1 to a value that is a copy, echo, or clone of the foreign key of the Process record we wish to relate the LineItem to). Then as a second, more distant way (maybe meaning based on yet another table occurrence, maybe not?), we could also remove a LineItem from a list of unassigned LineItem records by changing the textfield"N" to a value not "N", like "R" for rejected, et. This way we could move line items out of the picture, without destroying the original invoice in the process.

        • 1. Re: One Child, Two Parents where only one creates, the other adopts

          Maybe it's a problem with your example: in an invoicing solution, the line items table is a join between Invoices and Products. There would be no reason to create a line item with no Product associated with it.



          Other than that, I see no reason why you couldn't leave the line item's ProcessID field empty, if that's what truly needed. If you want a portal that shows only unassigned line items, define a calculation field in LineItems =


          Case ( IsEmpty (  ProcessID ) ; InvoiceID )


          and use it to define a relationship back to Invoices. 

          • 2. Re: One Child, Two Parents where only one creates, the other adopts

            The invoice table is related to line items each time a line item is created. 

            Each time a line item is created, a product is assigned to the line item (I left that part out)

            Later line items are assigned to a process sheet or cover sheet.

            Each process sheet is for one product only, so the when we're assigning an unassigned line item to a process sheet

            we only see the line items with the same product foreign key as the process sheet (which is itself assigned to a product)


            For some reason, it took me a long time to realize that for the process sheet I don't need to create a process line item record, but rather need to assign an

            existing line item to a given process sheet. Process sheets are restrained to only include 25 related lines at a time. Process sheets are then assigned to a summary sheet that acts as a cover sheet for the process sheets. These process and summary sheets are exported to excel and then given to another business, which uses a different system.


            The thing I get confused about is when you have multiple parents for the same line item. I get confused as far as what best practice would be.  There will be more parents just the three I've mentioned so far (not including the summary sheet, which is a grand parent). So the join table isn't just between two tables, its between multiple tables, like at the center of a starfish. 

            • 3. Re: One Child, Two Parents where only one creates, the other adopts

              If I understand this correctly, the solution is the same as above, with only a small modification: make the calculation field in LineItems =


              Case ( IsEmpty ( ProcessSheetID ) ; ProductID )


              Now, since the process sheets table has both a ProductID and a ProcessSheetID (primary key) fields, you can define two relationships between the sheets and the line items:



              ProcessSheets:: ProcessSheetID = LineItems:: ProcessSheetID



              ProcessSheets:: ProductID = LineItems 2:: cUnassignedProductID


              Place two portals using these two relationships on the process sheet layout. One will show the items already assigned to the sheet, the other will show all unassigned items of the same product. Place a button in the second portal that does:


              Set Field [ LineItems 2:: ProcessSheetID ; ProcessSheets:: ProcessSheetID ]

              This will assign the clicked line item to the current sheet and "move" it to the other portal.