How are these Orphans created

[Oyseka]

Hi All, I have a database where the relationship between the Quotes table and the QuoteItems table is set up such that if a record is deleted in the Quotes table, the related records in the QuoteItems table are also deleted. I have now been shown an instance on one Customer where there are four Quotes missing from the Quotes table but all the QuoteItems are still in the QuoteItems table. How can this be done, I am unable to recreate a circumstance where I can delete a quote and leave the quoteitems in place.

Any help appreciated

[LaRetta]

Hello to South Africa!!

Three ways come to mind that it might happen (but there might be more):

1. You have indicated 'Delete related records in this table when a record is deleted in the other table' on the wrong side of a relationship.

2. You have a Go To Related[] step which fails to switch layouts or accidently isolates the parent Quote records of a record-set of QuoteItems and your script has then issued Delete All Records step on the Quote table.

3. Your User is in a portal and button issues a 'Delete Record' instead of a 'Delete Portal Row.'

The easiest thing to check is the relationship.  It's easy to accidentally specify the wrong side.  It helps if you mark all your relationships by using text to add a plus or minus next to the occurrence where those options are specified.  I also like to include an 's' to indicate there is a sort.  In this way, it will be easier to spot the culprit.

As for #2 and #3, that would require a bit of detective work.  If you use GTRR[] and it is not *properly error trapped, it can fail and if one of your scripts then issues a 'Delete Records', you again can be on parent record set.  I hope it's #1 since you can then fix the issue quickly and still enjoy your weekend. 

* properly error trapped ... if you test for Get ( LastError ) or 101 (record is missing) but you issue a 'match found set', it can fail and not switch layouts.  You must instead trap with 401 to catch it.

Edited October 13, 2018 by LaRetta
Explained 'properly error trapped'

[Oyseka]

Hi (once again) LaRetta, Thank you for your response. The user can only delete a Quote from the iOS Quote layout using a scripted button, the script of which is very simple so I don't see how your suggestions could operate on the file and I believe that the relationship is set up correctly.

[LaRetta]

12 hours ago, LaRetta said:

As for #2 and #3, that would require a bit of detective work. 

How then are orphans created?  You have IDs with 'copy' after them.  Why?  Is it possible that the parent records aren't deleted but rather their ID has been changed so they are no longer related?   Do you prohibit modification of the primary key ( in field definition)? I also recommend adding detailed error trapping and logging, such as:  Dan Smith's Logger.  Robust logging can help you identify issues.  Maybe until you identify the issue, don't delete but rather flag records for deletion and clean them up later.

It is also possible that the __Quoteid Copy of Quotations is incorrectly holding the parent ID if the parent has been created from within QuoteItems ( since you have Allow Creation on the parent Quotations side).   The __QuotidMine is also suspect.  We do not have enough information to identify the issue.   I suggest you hire a competent Developer to review your solution.  And no, this isn't a plug since I'm not currently available. 🙂

Edited October 13, 2018 by LaRetta
corrected typo

[LaRetta]

7 hours ago, LaRetta said:

It is also possible that the __Quoteid Copy of Quotations is incorrectly holding the parent ID if the parent has been created from within QuoteItems ( since you have Allow Creation on the parent Quotations side). 

Sorry, this is a bit deceiving because I didn't finish my thought.  We don't know your record-creation or duplication scripts or your auto-enter calculations nor have we seen your graph so we don't know where the breakdown happens but if an ID is improperly set or modified other than through that 'Allow Creation' relationship then the QuoteItems 'parent' could disappear (although the record would not actually be deleted).

I again suggest you take a look at those duplicate 'copy' keys and also verify that you have the proper key fields on the layout if you have multiple table occurrences of those tables and be sure keys are set to 'prohibit modification of value' and set to 'unique'.  If you create an empty clone of your file and post it, we might be able to spot the issue.

Edited October 14, 2018 by LaRetta

[Oyseka]

10 hours ago, LaRetta said:

You have IDs with 'copy' after them.  Why? 

Good day LaRetta, sorry for the delayed response but the electricity went yet again and has only just been restored. The reason for "copy" is that we use Mirrorsync to enable both on and offline record editing and creation and that is used as part of the set up. Every ID field has a copy which is actually the original on creation ID and the ID field that is visible is an auto enter calculation field from the original. The "Copy" fields are not visible or accessible on any user layout

 

10 hours ago, LaRetta said:

 Is it possible that the parent records aren't deleted but rather their ID has been changed so they are no longer related?🙂

That was my thought as well because I can find no method of deleting the Quote without also deleting the QuoteItems. If however someone had actually managed to change the the QuoteId to break the relationship then the CutomerId and name would remain on the quote but that is not the case as the Customer is now not found in the Quotes table so the Quotes have actually been delete

 

11 hours ago, LaRetta said:

It is also possible that the __Quoteid Copy of Quotations is incorrectly holding the parent ID if the parent has been created from within QuoteItems ( since you have Allow Creation on the parent Quotations side).

As stated the visible QuoteId is a calculated field from the "Copy" even if a user could create a quote from the QuoteItems table. The user never actually goes to the QuoteItems table and the QuoteItems are only available to them through the portal on the Quote

 

11 hours ago, LaRetta said:

 The __QuotidMine is also suspect

Yes sorry, this one is indeed weird. For reasons known only to themselves the mines require a quarterly quote but a monthly service and invoice so we had to come up with a plan to carry a different QuoteId across three  months invoices as well as our normal unique QuoteId.

 

This is the first time that a Quote has gone walkabout in this manner after 14 months of testing and use

 

Thank you for your assistance and insights into where to look for the problem

[LaRetta]

50 minutes ago, Oyseka said:

The user never actually goes to the QuoteItems table and the QuoteItems are only available to them through the portal on the Quote

Then why is Allow Creation turned on for the Quotations side?  Nonetheless, I don't believe that addresses your issue. 

Tables which must stay in balance, such as Invoice/LineItems or Quotations/QuoteItems should be handled transactionally and when created/edited on FMGo online or offline, it is critical good logging and error trapping be in place.  Please see: Transactions.  I doubt this is something which can be resolved from a forum post, unfortunately.

Maybe your sync is failing in uploading new Quotations but uploads their QuoteItems.

Edited October 14, 2018 by LaRetta

[Oyseka]

Hi LaRetta, You are quite correct, there is no reason to allow creation from the QuoteItems table, I will rectify that.

I suppose that because the problem is so odd to me I was sort of hoping for a "If you get struck by lightening and the dog farts at the same time while Venus is in decent" this sort of thing can happen.

Thank you once again for your assistance though.

[LaRetta]

I hear ya.  I was sure hoping we could help you identify it as well and maybe someone else here can.  Funny person!!