10 Replies Latest reply on Aug 6, 2014 11:46 AM by sccardais

    Relationship Question

    sccardais

      Title

      Relationship Question

      Post

           Two tables (Owners and Lots) are related Owners:OwnerID = Lots::CurrentOwnerID. The OwnerID of the Owner who currently owns each Lot is in the CurrentOwnerID field in the Lots table.

           A layout based on Owners to show all the Lots owned by each Owner includes a portal showing Lots that match the relationship. The portal works fine but for some reason this layout is also showing data (blank rows in the portal) for Owners that don't have any matching ID's in the Lots table.

           In other words, it is showing previous owners that no longer have any Lots.

           Shouldn't the relationship between Owners and Lots (Owners::OwnerID) = Lots::CurrentOwnerID filter these out?

            

            

        • 1. Re: Relationship Question
          philmodjunk

               What kind of field is CurrentOwnerId?

          • 2. Re: Relationship Question
            sccardais

                 CurrentOwnerID is an ordinary text field entered manually in the Lots table. Not calculation, etc.

                 I think I want to omit records that don't have related records. I did a manual find for blanks in the CurrentOwnerID field in the portal but got "No Records Match ..." so I need another way to omit Previous owners whose OwnerID's don't appear in CurrentOwnerID in any of the Lot records.

                  

            • 3. Re: Relationship Question
              philmodjunk
                   

                        CurrentOwnerID is an ordinary text field entered manually in the Lots table.

                   Ouch! that leaves your system very vulnerable to user data entry errors. I'd use a script to update that field not manual data entry.

                   But with your current setup, if you are getting records that shouldn't be there, you've got either an error in how you set up your relationships or in how you designed your layout.

              • 4. Re: Relationship Question
                sccardais

                     Yes. Ouch Indeed. Did it manually just to test the approach. 

                     A script is needed to set the value in one field based on a comparison of values in another field of related records. I'm thinking about how to do this.

                     The CurrentOwnerID is in Lots. Lots and Owners are linked with a join table, Transactions. Transactions have foreign match fields LotID and OwnerID. Transactions also has TransactionDate. If there are multiple records in Transactions with the same LotID, the one with the most recent date is the CurrentOwner.

                     The script should capture the OwnerID of the most recent transaction for each lot and set the value of Lots::CurrentOwnerID with that value.

                     Not sure how to do that. Portion of relationship graph is attached if my explanation above isn't clear.

                • 5. Re: Relationship Question
                  philmodjunk

                       But your original problem was that you were seeing records in your portal that shouldn't be there. Is that still an issue?

                       The relationships look correct, so I'd suspect how your layout is designed is the most likely cause of this issue if you still have not figured that out.

                  • 6. Re: Relationship Question
                    sccardais

                         No. That was not the problem. (But I admit that I struggled with my explanation yesterday!)

                         The portal was displaying the correct data but as I cycled through the Owner records, I was seeing Owner records with no values in the portal. In other words, the Owners had no related records in the Lots table -- matching Owners:OwnerID = Lots::CurrentOwnerID because they were Previous Owners. Someone bought the same Lot later.

                         I figured out how to fix this problem but have not figured out how to script the process of entering the correct OwnerID into Lots::CurrentOwnerID based on the TransactionDate in table Transactions.

                          

                    • 7. Re: Relationship Question
                      philmodjunk

                           You can use the OnObjectSave trigger on the transaction date field to update the current owner ID field in Lots.

                           But you will also want to limit deleting a transaction record to performing a script to delete the record. That way, your script can delete the record and then perform the same script to update this field just in case the record you deleted was the most recently dated transaction record.

                      • 8. Re: Relationship Question
                        sccardais

                             I don't want to delete the transaction record.

                        I want to replace the value in Lots::CurrentOwnerID with the most recent OwnerID for the Lot in the Transaction table.

                             When a Lot is purchased, we create a new Transaction record - entering LotID, OwnerID (of new Owner) and the Transaction Date. Since the Transaction Date is more recent, it accurately identifies the Current Owner.

                             EXAMPLE: Assume the first buyer of Lot 19 in Transaction table was OwnerID #1 who bought the lot in Jan 1, /2012. To identify this person as the current owner, we set Lot:CurrentOwnerID to 1 for Lot #19.

                             Someone else buys lot #19 two years later on Jan 1, 2014. Their OwnerID is #57. Once the new Transaction record is committed, I want to replace OwnerID 1 in Lot:CurrentOwnerID with  57 for Lot 19 in the Lot table.

                        • 9. Re: Relationship Question
                          philmodjunk

                               Of course you don't want to delete a transaction record, but there may be times when you have to in order to correct a data entry error and you don't want that unlikely event to leave the current owner id field with incorrect data if you have to do that.

                               The idea is for your script to "replace the value in Lots::CurrentOwnerID with the most recent OwnerID for the Lot in the Transaction table." any time that data in the transaction table changes in a way that may require that this update take place.

                               The ways that this can happen is:

                                 
                          1.           A new transaction record is added.
                          2.      
                          3.           The Owner ID field is updated.
                          4.      
                          5.           The transaction date field in transactions is changed and this makes a different transaction record the "most recent" transaction for a given lot
                          6.      
                          7.           The most recently dated transaction record is deleted.

                               Events 3 and 4 are rare and usually would only happen as a way to correct a mistake, but they can happen so you should allow for that possibility in your system design.

                               . Events 1 and 3 be handled by the OnObjectSave trigger on the transaction date field if you always manually enter that date. The same script can be performed via OnObjectSave on the Owner ID field to cover the point where you either select or change the owner ID.

                               And Event 4 requires that delete record script mentioned earlier.

                               For the first 3 events, your script might look like this:

                               If [ Not IsEmpty ( Transactions::_fkOwnerID) and Not Isempty ( Transactions::Transactiondate ) ]
                                  Commit Records
                                  Set Field [ Lots::_fkCurrentOwnerID ; Transactions::_fkOwnerID ]
                               End If

                               For event 4, you'd leave out the IF step and just update the field.

                          • 10. Re: Relationship Question
                            sccardais

                                 Thank you. Very helpful.