I'd like to be able to set an Alert (calculation) field in a master file, that tells me whether certain conditions exist in a related file.
The challenge is that the master file contains one record to the related file's many records -- how to discriminate and find the specific conditions I am looking for?
The master file is my database of clients. I have a related file, Projects. Some projects are Open, others are Closed. (I keep Closed projects for future reference.) I want to be able to be alerted when a client has an Open project.
Before I had so much data and the desire for more sophisticated tracking, I just kept project notes in a handful of fields in the master file. Everything was within one record. So I was able to use a simple Alert calculation:
If(Project title >0, "Project Open","")
Thus, whenever there was a project (which had to have a title), the field would return "Project Open" in my main layout, reminding me that I could go to the projects layout for all the information.
Now, with a related file and a little bit of history, including both Open and Closed projects for the same client, everything has a title (whether open or closed), so that's no good. I tried keying off of a project status field ("Open", "Closed") in the related records, but there are multiple records for each client, some with Open some with Closed. It can't discriminate.
Is there a way I can set an Alert calc that will tell me when there is, among the many related records, one that is Open?
Whatever I try either "sticks" on or off (tells me there is a project, even if they're all closed; or tells me nothing at all, even if there is an open project).
I attach a screen shot of a piece of my main workspace, where I schedule calls and tasks and log calls made etc. The Projects tab contains a portal that shows all the project data from the related file. The "Project(s)" alert shown is what I am after -- among the hundreds of clients, I want it to alert (remind) me when I am there that there is a project Open.