Portal Record Deletion

[Keith Shelton]

Hopefully an easy one for everyone, except me!

Simple Notepad file with Portal plus related Notepad Line Items file.

Items are dealt with and then deleted from portal but how can we retain the items (archive them) in the Line Items file when they are deleted on the portal?

We have not clicked 'also delete . . .' in the relationship box.

Thanks for any comments.

[The Bridge]

One option: Instead of using Delete Portal Row, which is what I assume you're using, try a Set Field() function which will set the foreign key for the related records to "". This will effectively remove the item from the portal without deleting the related record itself.

[Keith Shelton]

Thanks Peter. Can you expand a little as I am guessing, more than understanding, what you are suggesting. I should have also said that it is FM4 on Mac.

[The Bridge]

Okay, I haven't used FMP 4 (skipped from 3 straight to 5), but I think it will still work.

This Script step should do the trick:

Set Field ["Relationship::Foreign_Key", ""]

Where Relationship is the relationship that the portal is based on, and Foreign_Key is the field that the relationship matches in the related file. The idea is that setting this foreign key to "" will cause it to disappear from the portal without deleting the record proper.

Apply the above script step to a button within the portal row for it to work.

Hope this helps!

[danjacoby]

Rather than setting the key field to "", set it to something that is never an ID (such as an arbitrary alphanumeric string). That way, you can more easily view the "deleted" line items, either through a search or through a portal.

[Keith Shelton]

Thanks for ideas but still having problems. It may be that I haven't got relationship set up properly or I didn't give enough info.

Notepad File: 4 Records for 4 people to use. Each record has Portal linked to same Notepad Line Items file. Each Portal can have several notes still to be dealt with.

Notepad Line Items File: Simple file with individual records for notes.

If I upset the Relationship I lose all notes from Portal, i.e. not just the one note completed.

Any thoughts please.

[Keith Shelton]

Still have problems. Anyone got any other suggestions before I return to wrestling?

[Geeksharka]

The key to this (no pun intended) is to use a complex key instead of a simple one.

The key should exist of two components. The first component represents what the primary key, and the second part represents the variable part of the key -- based on the status of the child record.

This way a parent Key would consist of its unique ID, plus a static piece of data matching the 'on' status of the child.

I use this for relating To-Do's to employees customers, SO's, WO's, etc. etc.

For example:

If a TODO record needs to relate to an Employee, then the TODO record needs to match the Employee's unique ID.

Furthermore, because I want to the TODO _only_ to show until it's been done _but_ I don't want to actually delete the TODO, I need to the relationship to be based on the EMP ID, _and_ the TODO's status. That way, when the TODO is marked as Done, it is not deleted, but does disappear from the Employee's TODO portal/list.

This is how:

In the TODO (child) table, create a calc field that concatenates the EMP ID field and the DATE_DONE field.

Use this new calc field (EMP ID & DATE_DONE) in the relationship from the EMP top the TODO file. As long as the TODO is not done, its end of the relationship will match the EMP ID -- thius showing up in the portal. Once you mark the TODO as done though (which can be taken care of from the EMP portal via a SET FIELD script), its concatenated field will no longer match the EMP ID, thus disappearing from its portal.

Sorry for the confused explanation, but this will work for your setup too. Just make the child key field a concatenation of the primary key, and a variable status element. That way once the status of the child record changes, it will vanish from the portal based on the complex key relationship.

HTH

[Keith Shelton]

All now working. I was stupidly changing ID on Portal instead of in individual record in Line Items File.

Thanks to everyone.