1 Reply Latest reply on Aug 20, 2010 9:13 AM by philmodjunk

    Deleting Portal Rows with Join Table and Many to Many

    JosephGabrielli

      Title

      Deleting Portal Rows with Join Table and Many to Many

      Post

      Okay here goes, I hope I'm able to explain this.

      I have a setup right now, where I have a many to many relationship setup via a join table.

      I have setup a portal to show the related records in the primary user layout.

      The portal is linked to a table that contains line item products with associated monetary values.

      So when I delete a portal row, instead of deleting the record in the join table (and thus removing that relationship) deleting the row (via the Delete Portal Row script attached to a button in each row), deletes the record on the related "many" table, not the join table.

      So I'm presently setup to delete my entire table of line items if things continue this way.

      I've tried changing the various deletion settings, in the relationship graph, I've tried all boxes checked and unchecked.

      I've also tried removing the setting for the portal that allows deletion of portal records, it still asks me if I want to delete the record, and still removes the line item from the table on the other side of the join table.  

      Is there anyway to make sure that records in the join table are deleted and not data from the many to many tables?

      I'm including a screenshot of 3 different areas of the database. The two portals, the rel graph, and the script to add portal entries/records.

      Thanks

      Joe

      PS I did use a tip I found on the forums here, for using a table instance and a second portal associated with it, to add items to my primary portal without leaving the primary layout (IOW not having to navigate to the join table to create records there), the join table has no real data, just two foriegn keys and a primary key to associate the many to many relationship between my sales table and line items table.

      fmpscreen.gif

        • 1. Re: Deleting Portal Rows with Join Table and Many to Many
          philmodjunk

          You've got something or several somethings not set up correctly.

          Here's what you should have to get this to work:

          Your portal should be based on the Join table, tblSalesJoin--not either "many" table. You can include fields from tblExtras in this portal to show data from that table in the portal.

          In the relationship TblOpportunity---<TblSalesJoin, you can enable the delete option for TblSalesJoin records and you can enable the delete option for TbleSalesJoin records in the relationship: tblExtras >-----TblSalesJoin to enforce referential integrity. Do not select the delete option for either tblOpportunity nor tblExtras.

          Now deleting a record from the portal will delete the Join record, will delete the Join record but leave the related records unaffected.