Jump to content
Sign in to follow this  
Oyseka

How are these Orphans created

Recommended Posts

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

Share this post


Link to post
Share on other sites

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. :wink3:

* 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 by LaRetta
Explained 'properly error trapped'

Share this post


Link to post
Share on other sites

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.

Screen Shot 2018-10-13 at 11.50.24.png

Screen Shot 2018-10-13 at 11.53.18.png

Screen Shot 2018-10-13 at 12.03.13.png

Share this post


Link to post
Share on other sites
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 by LaRetta
corrected typo

Share this post


Link to post
Share on other sites
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 by LaRetta

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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 by LaRetta

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By KevinP
      What does the superscript letter in the upper left corner of a field mean?
      I'm trying to relate fields in one table to fields in another table . . . 
       
      Thanks!
       


    • By GeoSteven
      All files/tables in this discussion are fmp12 using FM 17 and both files are opened when initiating the app. "logging" is opened as the primary file and "claims" is opened as an external data source.
      I have a table called "newclaim" in "logging" which contains mailing address information including a USPS zip code. I have a table called "lu_ZipCode" in "claims". There is a relationship set between "newclaim" and "zipcode" on a field called "COID" (company ID). This field is automatically populated in both tables with "DCSI". This is an old method (used in Fm6) of establishing a simple relationship so that global fields can be used to pass parameters into scripts for zip code lookup and verification. 
      In the parent script (in "logging") I set the related field "lu_ZipCode::_gZipCode" with (for ex) "12345" with the intent of calling a subscript in "claims" then using "12345" to do the lookup. However when the subscript is called the "_gZipCode" field is empty. As a workaround I called the subscript passing the "12345" as a parameter which of course works as one may expect. After setting the parameter to a local variable in the subscript I'm able to do the lookup then populate the global fields "lu_ZipCode::_gCity" and "lu_ZipCode::_gState" while still in the "claims" file. Yep, sure enough when I go back to the parent file the global fields I just populated are empty. 
      It's worth pointing out here that prior to yesterday "zipcode" was a separate file and the methods described above worked like a charm. This effort is being put forth to consolidate this old FM6 solution with 40+ files into just a few files. I imported "zipcode" into the "claims" file then copied and modified the scripts anticipating that this would be a quick little project to get rid of one more file.
      I know there are better methods (ie JSON) to get this done and I will probably go that route. At the same time I'm left scratching my head on why this tried and true method doesn't work. Why would global fields I can populate from one side of the relationship not be populated when viewed from the other side? 
    • By amerioca
      Hi everybody,
       
      I recently amended a db from an old Lynda course, controlling shore excursions for river cruise ships. For my limited expertise (I am still a rookie at the very best) the construction is somewhat complex as there is many vessels, different routes, different charterers and consequently different shore excursion programs and prices. In some cases the shore excursion program of different vessels and operators is similar but not the same and so on.
      I have 7 different tables partly related (pls see graph attached):
       
      For every journey ::customers and ::trip are assigned a tour code and date (“tour code” is not unique as journeys repeat themselves during the season, “Tour Code” in conjunction with “Date” is unique)
      Every tour operator/vessel has many trips on every journey. All table occurences and fields are uniquely defined by unique id.
      Now I want to create an opening layout reflecting all relevant cruise information such as passenger list from ::customer and ::trip (shore excursions) for the specific journey, as well as other information in text fields.
      But I seem to be unable to get the right relationship and layout setups together. I tried all possible relations and layout setup combination but nothing worked so far.  
      On the same layout I was able to either retrieve the passenger list from ::customer for each journey or the offered shore excursion (trip) list from ::trip  but not both together on one layout.
       
      What is it, I am doing wrong? Any ideas are appreciated

    • By H
      Hi
      i have 3 Tables Property , Units , Tenant.
      Each property has many units , Each unit has one tenant active but may have many inactive tenants from previous years.
      All working fine BUT.....
      i would need to show a table in the property layout of the units and each one only with its active tenant.
      The portal is based on the units table. 
      However the tenant just show up the tenant that was first input to the unit.
      My question is , how could i get that only the active tenant should show to the unit?
      i would really appreciate it someone could guide me on this.
    • By Hproth
      I'm sure there is a simple solution to this problem, but I can't seem to figure it out!
      I have a custom app that allows the user to write letters amongst other things. 
      Each patient is linked to a GP and a Consultant, when writing a letter to a patient there is the option to CC both the linked GP and Consultant. However, occasionally another GP or Consultant might need to be CC'd in the letter (in addition to the ones already linked to the patient). So I would need the address of this additional consultant/GP to also be CC'd in the letter. For the life of me I can't work out a simple way to do this. 
      Does anyone have any suggestions?
      Thanks in advance.
       
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.