Removing records from join table

[Tar Ashland]

Thanks to Reed's excellent many-to-many example (see attached), I was able to create a project that allows a user to relate a number of tables to my master table using the standard: TableA-=<TableAB>=-TableB approach.

I now need to enable the user, while sitting in TableA's layout, to delete the relation in TableAB without deleting my perfectly good value in TableB (which is likely related to a bunch of records in TableA)

Going to the related record in TableAB doesn't work because it seems to match only on the TableA_id and not both the TableA_id and TableB_id.

Any thoughts on how to do this? I smell a script, but I am not certain how to approach it.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

M2M Example.zip

[RalphL]

Not quite sure what you want to do. If you want to remove a record from A and all records in AB that are related to it, you can set that up in the relationship. If you want to remove a single record from AB that is a little more complex. I assume that in join table you have a primary key that is a catenation of the two foreign keys. With another relationship based on this key you can isolate the single record in AB. Or if you have a portal in A that views AB you can have a button in the portal row that will go to the related record and delete the record.

[Tar Ashland]

Lets see if I can be more specific.

TableA has a primary key TableA_ID. TableB has a primary key TableB_ID. TableAB has the two PKs and two globals g_TableA_ID and g_TableB_ID.

I have a portal on TableA's layout that shows all of the related records from TableB. I want to assign a script to the elements of the portal that will remove the record in TableAB where TableA_ID is the current TableA record I am looking at and TableB_ID is the portal item I clicked on.

If I were doing this in SQL, I would say (pseudo code) "DELETE from TableAB WHERE TableA_ID = thisRecords.TableA_ID and TableB_ID = theClicked.TableB_ID"

I have tried "Delete Portal Row" but that removes the record in TableB. I also tried going to the related record. This works if TableA only has one related record in TableB. If it has more, the "Goto Related Record" takes me to the first record in the returned set (this is where TableA_ID is correct, but TableB_ID might NOT be)

My hunch is that I need to set my handy-dandy globals in TableAB and then tell it to go to the record where TableA_ID = g_TableA_ID and TableB_ID = g_TableB_ID. Then delete the record. Just not sure how to get it to match on both criteria.

Thanks for any help.

[Reed]

Here's the modified file. Note that the new portal shows records from AB, and the delete script has a couple of extra steps to refresh the relationships.

FileMaker Version: Dev 7

Platform: Mac OS X Panther

M2M Example.fp7.zip

[spk]

I'm trying to find the same solution as Tar, where deleting an item from the portal will remove the corresponding record in the lookup table. In the current file, when you delete the portal record associated with TableA, it deletes a record from TableB, but does not delete the corresponding lookup record from TableAB.

I'm assuming it needs to be scripted somehow. I know how I would do it in SQL, but not sure how to transfer that over to FMP.

[Reed]

Just download the example I previously attached to this thread.... it just changes the portal to show records from AB instead of A or B. Deleting rows here will have the desired effect.

Dana