Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Using a script to delete all portal records


GYSTInk
 Share

Recommended Posts

Trying to delete all portal records in a layout with multiple portals in tabs.

 

I am not able to use a local variable in my script as the Perform

Find criteria. The script halts trying to find records with the

Art_ID field equal to "$art_id". It is trying to match the name of

the variable, not the contents of the variable.

 

Is there a way to use a variable in the Perform Find criteria? If

not, can you suggest a way to do this? Worst case, I can loop through

every record in the Editions table and delete records if it has the

matching art id value.

 

The script is activated from a button on a layout showing the Artwork

table. I want to delete all records in the Editions table that match

the Art ID field for the current record in the Artwork table. The

Artwork table and Editions table are related, linking their Art ID

fields.

 

My Script:

Set Variable [$art_id; Value:Artwork::Art_ID] -- get the Art ID

for the current record in the Artwork table

Go to Layout ["Editions Table Layout" (Editions)] -- go to layout

showing the Editions table

Show All Records -- make sure all records are visible

Perform Find [Restore] -- show only the records that match the Art

ID in the current record in the Artwork table

Action: Find Records

Criteria: Editions::Art_ID:[$art_id] -- problem matching to the

value in the variable

Delete All Records [No dialog] -- delete all visible records

Show All Records -- show all the remaining records

Go to Layout [original layout] -- return to the layout with the

Artwork table

Link to comment
Share on other sites

Try it this way:

 

Set Variable [$art_id; Value:Artwork::Art_ID]

Go to Layout ["Editions Table Layout" (Editions)]

Enter Find Mode

Set Field [ Editions::Art_ID ; $art_id ]

Perform Find []

...

 

To clarify - you cannot store dynamic criteria in Perform Find[]. Putting a variable there causes a search for the text string "$art_id".

 

BTW, Show All Records before a find is a waste of CPU cycles.

Link to comment
Share on other sites

a) What comment said. For some unknown reasons FileMaker Inc didn't make Perform Find variable-savvy so it treats $VariableName as a literal.

 

b) But back up and wait a minute. All this is to delete all portal records? Simpler to just do this, you don't need a Find at all:

 

Go to Related Records [Portal_TableOccurrenceName, Show only related, via Layout ]

Delete All Records [no dialog]

Go to Layout [original layout]

 

I would usually precede the Go to Related with a custom dialog ("Are you sure you want to nuke all those portal records?" Yes/Cancel). You can also include this easy error-eliminator:

 

If [Get(windowmode)=1 or count(Portal_TableOccurrenceName::serialnumber)=0]

Exit Script []

End If

 

Anyway, you don't need to perform a Find for this. GTRR is faster and more precise.

Link to comment
Share on other sites

Go to Related Records [Portal_TableOccurrenceName, Show only related, via Layout ]

Delete All Records [no dialog]

Go to Layout [original layout]

 

Bad, bad, bad script in FM7+.

If there are no related records, it will delete all the parent records without asking!!!

Empty table. Bad Bad Bad.

 

I would usually precede the Go to Related with a custom dialog ("Are you sure you want to nuke all those portal records?" Yes/Cancel). You can also include this easy error-eliminator:

 

If [Get(windowmode)=1 or count(Portal_TableOccurrenceName::serialnumber)=0]

Exit Script []

End If

 

It is VITAL (not optional) that you include the test for no related records in the script. A better script would look like:

 

If [Count(Related::serial) > 0]

Go to Related Records [show only related records, From table: Related, Using Layout: Related (Related)]

Delete All Records [no dialog]

Go to Layout [original layout]

Else

Show Custom Dialog ["Error"; "There were no records to be deleted"]

End If

 

And you could add an optional custom dialog step checking if they really want to delete x child records then wrapping the Delete All Records in an If testing for the Yes response.

 

See attached file example with three alternate scripts - Bad, Better and Best.

Link to comment
Share on other sites

  • 7 years later...

Hi

 

I came across this thread as I'm looking for a method to delete related records in a portal when a field on the parent table is modified.

 

My scenario is this.

 

I have a 'development' table on which users select a 'developer company', once the developer company is selected, the users then select an number of related contacts from the developer company, which are stored in a 'jndevelopmentcontacts' table. This is shown as a portal on the development layout.

 

If a user changes the development company (i.e there was an input error), any contacts that had been input relating to that company also need to be cleared.

 

I've implemented the script above and in principal it works but I was wondering if there is an alternative method that avoids the user being navigated to the layout of the related table to delete the records? I don't need or want the users to see the related record lookup step on the 'jndevelopmentcontacts' table layout. Ideally, I want this to happen with the user only getting a dialog 'Are you sure you want to change the company' when they modify the developer company. The related contact records need to be deleted in the background.

 

I hope that makes sense.

 

If there is a more elegant approach, any pointers would be appreciated.

 

Thanks

 

Russell

Link to comment
Share on other sites

Use the GTRR option to Show in new window, and open that window using negative coordinates:

 

Set Variable ( $hasContactEntries ; not IsEmpty (Related::serial) ] # you don't care about the count; you just want to know if there is at least one

#

Show Custom Dialog [ "Really change company …?" ; "Really change …bla" & Case ( $hasContactEntries ; " All associated contact entries will be deleted, too." ) ]

If ( Get ( LastMessageChoice ) = 1] # Cancel, No, etc.

Exit Script

End If

#

If [ $hasContactEntries ]

Allow User Abort [ off ]

Go to Related Records [show only related records, Show in new window: Distance from top: -5000, … left: -5000, From table: Related, Using Layout: Related (Related)]

Delete All Records [no dialog]

Close Window

End If

 

Preferably using a target layout that's as non-busy as possible.

 

 

in principal it works

 

The poor guy … :D

Link to comment
Share on other sites

Thank you EOS, I keep missing the notifications that have come in. I'll try this now.

 

Thanks for the english lesson to.:)

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use