Empty Portals

[madie]

I have an events database that is the end users use to track invitees, rsvps and run reports. This is set-up to be a temporary holding tank for current events and then all the information is to be transferred to our legacy system. I have a table for all the invitees, a table for all the RSVPs and a table for all the events. I use a portal to show all the "active" events a person is currently invited to.

As part of the regular clean-up of removing events from this file I need to be able to find the people in my invitee file who do not have any current rsvps.

What I need is to be able to find the people who are in the invitee table who no longer have a related rsvp record in the rsvp table (hence their portal would contain no records). I was hoping there was a way to do a find using my portal in the invitee record to do this.

Any suggestions?

[eos]

You're on the right track, apart from the fact that you wouldn't actually use the portal, but the relationship the portal is based on.

To find all persons without an RSVPs, create in Invitees a new calc field - lets be creative and name it "RSVPcount" - with type Number and definition " count ( RSVPtable::primaryKeyOfRSVPtable )". Use the RSVP table occurrence you display in the portal (or any other that is directly based on the primary InviteesID).

This field holds the count of the related RSVP entries; to find invitees without a related entry, search for all records where this field is empty (attention, potential pitfall; it doesn't contain a "0", it's just empty. Of course, you can use other methods to populate this field somehow, depending on the number of related records, and then check on the contents). Since you say this is a regular house-cleaning task, best write a script to do it (with the added bonus that you don't need to clutter any layout with the actual field, if you use Set Field).

Since the calc field is not (and cannot be) indexed, the search might take a tad longer than with an indexed field, but as long as we're not talking hundreds of thousand of records, it should work nicely.

If speed is of the essence, here's a more creative approach:

Write a script with following steps (this assumes that all mentioned tables reside in on file; don't forget to add error capture steps etc.):

- Go to Layout [ a layout that is based on the RSVP table occurrence of the portal ] ;this is used to establish the context for the GtRR (see below)

- Show All Records

- Go to Related Record (GtRR) with option "Matching all records in current found set", and for "Get Related Records from:" choose the Invitees table occurrence the layout is based on you show the RSVP portal on... still with me??

Executing these steps will bring you to the Invitees table showing all Invitees *with* an RSVP; now as last step you just use

- Show Omitted Only

and there are all Invitees *without" an RSVP.

This should work more or less instantly, using relationships and thus indexes (at least in this case; but should one side of the relationship use globals, a GtRR in that direction wouldn't make much sense anyway). Don't forget to always check for any unusual circumstances, especially when using GtRR and possibly having a Delete action as next step - if there are no related records, you would stay on the last layout and wreak havoc on perfectly innocent data that were meant to last for years to come :

Hope this helps!

Cheers,

eos

[madie]

I tried your more "creative" scripting solution and it worked great! Thanks for the help.