I created a test file with a portal. It is populated with 4 rows of related records. I attached an OnObjectExit script trigger to one of the fileds, and it runs correctly when exiting that field on any row.
I'm confused by your question, though:
"it still only triggers on the first row added to the portal rather than the first one in the list" - what's the difference between the 'portal' and the 'list'?
"I need the script to trigger on the field no matter which row it is in, and preferably, to fire when the top row is exited" - you say 'preferably', which sounds like you actually prefer it to run when the top row is exited, but not when the other rows are.
Do you mean that there is one record (Say the 'Mr Smith' record) in the portal that the script trigger will run on exit, but if you click out of the 'Mr Jones' or 'Mrs Brown' record in the portal it won't run? And it doesn't matter whether 'Mr Smith's' record is number 1 or 4 on the list, that is the only one it runs on?
What is the script you are trying to run?
You may want to post your script for us to see:
To post a script to the forum:
- You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
- You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
- If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
- If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
Thanks for looking at this for me. Basically I have a donations database. Each donor has a record, and in that record is a portal that tracks their donations (the date the donation was made and the amount, etc). Donors also have a status: current and lapsed. Donors become current when a donation record is added to the portal. The date of their donation is used to calculate an expiry date (one year after their donation). If the donation date is reached and they have not made another donation, then their status changes to lapsed (thanks to a calculation field). When a new donation record is added to the portal, then their status is updated to current and they get a new expiry date.
This all functions fine.
I also have a database for contact lists so we can add contacts easily to mailouts and invitations, etc. When a donor is current I want them automatically be added to the Donor contact list. When they lapse, I need them to be deleted. Once they make a new donation and become current again, I need them to be added to contact list again and so on.
I have been able to get this to work by adding a script trigger on the donation date field in the donation portal. So when a donation is made, and a date entered into the donation field, then a script runs that adds or removes the donor from the contact list database depending on whether their donor status is "Current" or "Lapsed". So the idea is, when a donation is made (and their status changes to "current", then they are added to the contact list, but when their status lapses they are removed). I have managed to get this to work for the first donation record entered into the portal. When the donation is entered and they become current, they are added to the contact list, and once that donation lapses (after a year), they are removed.
But when I add a new donation to the portal, the script doesn't re-add them to the contact list. It does nothing. However, if I change the date of the first donation record to make it current then the script works and re-adds the donor to the contact list database. I've tried changing the sort order but it doesn't make a difference.
Does that make more sense? Any thoughts? I can try adding my script if that helps...?
I suggest posting your script. I can guess that your script is losing the focus on the active portal row before data from that portal record can be used by the script to do what you want, but that is just a guess.
You might also consider putting all contacts and donors in the same table and use fields in the table to designate them as "current" or "lapsed". This would eliminate the need to add/delete records from contacts every time their status changes.
I have attached an image of the script with my database.
I have actually just managed to get the script to work by putting the script trigger on the "Category" field in the portal instead of the "Donation Date" field. This seems to work fine!
For some reason the script triggers in the first portal row on the "Donation Date" field but won't trigger when a new portal row is added. But it DOES trigger if I put the script on the "Category" field. I definitely seem to be loosing some vital piece of information that allows the script to trigger on the "Donation Date" field when a new portal row is added - what am I missing?
I don't see any script. Either you did not upload a file of the correct type, the image just hasn't appeared yet or some other glitch kept it from appearing.
Sorry Phil - totally didn't upload it properly.
Let's try again!
I think you need a commit records step as the third line of your script. The donor status calculation may not properly update if you don't.
But don't think this method of creating and deleting record in another table is a good way to manage your contact list.
Phil you are brilliant! I knew it would be something as small as that!
As you mentioned, I do already have all my donors in a "Contacts" database, which is related to the "Donations" database which keeps track of all our donations.
The reason I have "Contact Lists" database is to allow users of the database to create groups of contacts that can be added to event guest lists (in an events database). Most of these lists need to manually compiled by the user (eg. Director's VIP list), while other contact lists, as you said, can easily be retrieved by searching for Current Donors, etc. The reason I created the "Contact List" database, which is related to the "Contacts" database, is so that users can then add an entire contact list to an event by clicking an "Add Contact List" button, which takes you to a list of all the Contact Lists, which, when selected, adds all those contacts to that particular event. I was really trying to keep things consistant to make it more user friendly (the staff at my work at terrified of Filemaker pro!)
This all sounds very convulted as I say it out loud - and I'm sure you are frowning at me! And I'm sure you're completely right! But this is the best solution we have come up with so far based on all the variables and what we need our database to do.
Thanks for your help - I will be keep thinking about it and keep refining!
Yet you can create such "contact list groups" without using a second table of contacts.
How would you do it?
I took a closer look at your script before posting. It may be that you are already doing what I was about to suggest.
I mistakenly thought that you had two fields with all contact info in them, but it looks like your contact list is a list of ID numbers that might be used to link to your donor records. If so, never mind on that.
But I do spot an issue with hou you find a record to delete it. You shouldn't have to loop through a set of records to find the correct one to delete, that will be much slower than performing a find or using a relationship to find that record.
Yeh - sorry I didn't really make it clear - that's exactly what I've done. Phew - glad to hear I don't have to go back to the drawing board!
That's a very good point. Hmmm... from memory I think the reason I did that was because if there are no matching records an error message pops up that I've never figured out how to get rid of. There's no "perform without dialog" option - is there any way to stop it?
If you use
Set Error Capture [on]
There will be no dialog popping up if no records are found.