2 Replies Latest reply on Nov 3, 2011 7:05 PM by StephenMallery7287

    I'd like to be able to set an Alert (calculation) field in a master file, that tells me whether...



      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.