DeletePortalRow deletes ALL records in child table

[Rramjet]

I have a number of portals, each with a button in the portal row linked directly to the DeletePortalRow command. Each of these these portals represent the Session Details (in a child table) for a particular client (registered in the Master table). All the DeletePortalRow buttons work fine (they delete only the portal row that the button is clicked in) except in one particular portal... When I click on the DeletePortalRow button - it deletes EVERY record (related or not) from the child (sessions) table. A scripted DeletePortalRow behaves in exactly the same manner. The only difference (that I can see) is that this particular portal has more than one row of data (3 actually). It also has some text labels for the fields and a couple of different buttons (Sort by Date, GotoPR First & GotoPR Last).

I have looked at posts:

http://fmforums.com/forum/showtopic.php?tid/209568/

http://fmforums.com/forum/showtopic.php?tid/202374/

http://fmforums.com/forum/showtopic.php?tid/201204/ but although seemingly related, they don't give me any clues.

All I can think is that it must be related to more than one row of data in the portal row ... has anyone else come across this problem? Any suggestions welcome.

Regards,

Rramjet.

[bruceR]

You need to look at your relationship graph and the delete related record settings.

[Rramjet]

Hi Bruce -

Yes... Each Session table is related to it's parent via a serial number with "Allow creation...", "Delete related records...", and "Sort records ..." checked on the Sessions side but nothing on the parent side.

This holds true for about 15 sets of parent/session details table pairs (the parent table in each case is also related back to a Master table via the "same" serial number).

Each sessions table portal row has a delete button inside it. Each of these buttons, when clicked, deletes the portal row in which the button was clicked (this means it deletes the sessions table record that is related to the portal row - but critically leaves all other related records (the other sessions for the same particular client) in the sessions table, alone). Only in one portal (of 15 that do work correctly) does the DeletePortalRow button delete EVERY record (related or not) in the related sessions table).

Considering it all works fine for all the other portals... and nothing is different in the relationships between parent and sessions (child) tables for any of them (including the misbehaving one) could it be something to do with the layout of the fields in the portal row? (although this seems unlikely, that is the only difference I can see).

Perplexed

Rramjet

...Oh...I just realised another difference ...In Portal Setup... I unchecked the Sort by Session ID so I could insert the sort by Date button into the portal... but I tried removing the button and turning the Portal Setup Sort by back on ...and it still deleted ALL session records... huh... still perplexed...

Edited May 17, 2010 by Guest

[Rramjet]

Okayyy... I have now found the answer why! but not the solution...

I ALSO had some self-join tables (Sessions 2, Sessions 3, etc) to enable the production of reports. I had related these tables by the field required in the reports (like "Location"). Because I had 3 self join tables, and each had a different field for the self-join relationship, this covered all the combinations in the original sessions table. I think what happened is when I deleted Portal Row, the relationships meant that everything "related" got deleted also... meaning ALL the records...

(so Bruce ...you WERE precisely on the right track with your suggestion)

I then created a complex self join by including the Session_ID in the self-join relationship with the field required to produce the reports and presto! The DeletePortalRow button works to delete only the records with a particular Session_ID... of course this means the first session from ALL Session table records also disappear...but it is a step in the right direction and tells me WHY it is happening... Now I gotta work out how I can have the reports (and the self-join tables) and delete ONLY the record in question... perhaps if I could add the Client_ID from the parent table into that relationship, logically that would constrain it...but I don't think FMP will allow that...

A work in progress obviously... but at least we now know WHY the DeletePortalRow command was behaving as it did...

Okayyy (again)! I picked up the client ID in the Sessions table and added it to the self join relationship and bingo...! Now the DeletePortalRow button deletes ONLY that session for that client. Just what I wanted! Phew... probloem solved.

Bruce, you were right on the money...(and by-the-by) so were the original posts I referenced as they also remionded me to look toward the "Delete related records" section of the relationship... but it was just not THAT relationship that was causing the problems...it was the subsequent self-joins that did it (and I had completely overlooked them ... my database is relatively complex, with over 60 tables and multiple self-joins hanging off them all over the place so that one simply cannot see the whole database on one screen in the relationships/table view - and in that Morass, I simply missed the critical self-join tables. A salutary lesson for me - Don't forget about ANY of the relationships you create, they just may come back to haunt you when you least expect it (and in ways that you never dreamt of when you created the relationship)!

Well, apologies if I have wasted anyone's time. BruceR gave me the key...I just had to find the lock it fitted!

Best to all,

Rramjet.

Edited May 17, 2010 by Guest