Notify 'text' if field contains certain data

[AlanP]

Thank you in advance to everyone who helps on this forum...you are great!

What I'd like to do is have text appear that says "ALERT" (or something to that effect) when certain text is entered in a field.

For example:

We have a 'notes' table that is accessable via a portal in our main layout. If we were to enter in notes something like "user is due for an evaluation", we would like "ALERT" to appear somewhere on that layout because "evaluation" was in the notes.

We'd also like other keywords to appear, so 'evaluation' wouldn't be the only thing it would hit on.

Users on this forum have told me to use the following when there is *any* text in the field so we can have text appear on the layout...can the same be done with my situation?

If( not IsEmpty ( Personnel Notes );" Personnel Notes";"")

THANKS!!!

[Lee Smith]

Here is a quick down and dirty file showing the components, maybe it will help you.

Pattern.fp7.zip

[-Queue-]

Of course, it would be most efficient to combine the components.

If( PatternCount( Text; "evaluation" ); "ALERT" )

[Lee Smith]

Oh, sure, take the easy way out.

LOL

[AlanP]

wow thanks guys!

It works great but I'm not sure how to add more 'keywords' to the mix. For example we need an alert when they go to a different department (but only specific ones).

Also I'm using a portal into the table where the department entries are made. I noticed with the calculation that it only looks for the FIRST record that matches the keyword...is there a way to tell it to only look at the last entry made?

Once again thanks in advance...you guys are fast!

[AlanP]

hey does anyone know how to do this? Much thanks!

wow thanks guys!

It works great but I'm not sure how to add more 'keywords' to the mix. For example we need an alert when they go to a different department (but only specific ones).

Also I'm using a portal into the table where the department entries are made. I noticed with the calculation that it only looks for the FIRST record that matches the keyword...is there a way to tell it to only look at the last entry made?

Once again thanks in advance...you guys are fast!

[comment]

You can add more 'keywords' by using an OR statement in the test, e.g.

Case (

PatternCount ( Notes ; "keyword1" ) or

PatternCount ( Notes ; "keyword2" ) or

PatternCount ( Notes ; "keyword3" ) ;

"ALERT" )

A calculation refering to a related field refers to the FIRST RELATED record. The order of related records is determined by the sort order in the relationship's definition (creation order being the default).

[AlanP]

wow that works! thank you everyone! I had to make another calculation to show the last department they worked in:

Last ( Employment History::Department )

  with a title of "CALC Last Department"



and then added it to your calculation and it worked great!

Case ( 

PatternCount ( CALC Last Department ; "keyword1" ) or

PatternCount ( CALC Last Department ; "keyword2" ) or

PatternCount ( CALC Last Department ; "keyword3" ) ; 

"ALERT" )

[AlanP]

Let me ask a new question about this calculation...

Is it possible to create a 'tickler file' type of calculation inside this one if a certain date is passed the alert text will appear?

For example let's say we give an employee until 10/01/2005 to complete a test. Can the calc only show the "alert" text when the current date passes 10/01/2005?

Thanks guys :D

[comment]

Yes, of course - you should be able to figure this one out by now:

Case (

Get (CurrentDate) > Deadline

and (

PatternCount ( CALC Last Department ; "keyword1" ) or

PatternCount ( CALC Last Department ; "keyword2" ) or

PatternCount ( CALC Last Department ; "keyword3" )

) ;

"ALERT" )

Note that this needs to be an unstored calculation in order to update from day to day.

[AlanP]

:yay: Thanks Comment that makes sense!

I didn't need the keywords so this is what I put in for the calculation:

Case ( Get (CurrentDate) > CALC TEST DUE DATE ; "ALERT" )

I already had a calc that showed the last test due date from the portal.

Thanks again! I'm slowly learning this stuff!

Yes, of course - you should be able to figure this one out by now:

Case (

Get (CurrentDate) > Deadline

and (

PatternCount ( CALC Last Department ; "keyword1" ) or

PatternCount ( CALC Last Department ; "keyword2" ) or

PatternCount ( CALC Last Department ; "keyword3" )

) ;

"ALERT" )

Note that this needs to be an unstored calculation in order to update from day to day.

[AlanP]

Oh I forgot to add we have a 'test received' field as well...so that calc I modified of yours would always say "Alert" so I did the following:

Case ( Get (CurrentDate) > CALC TEST DUE DATE and IsEmpty ( CALC TEST RECEIVED );"ALERT" )

and that seemed to work!

[AlanP]

OK I'm having another problem (sorry). When we issue a new test to an employee (create a new record in the test history portal) the alert won't work because it is looking at the original test received field.

So for example here is what the portal looks like originally:

Test#1 Issued 1/1/2005 Due 1/3/2005 Rcvd 1/4/2005

When we add a new record:

Test#2 Issued 1/5/2005 Due 1/8/2005 Rcvd (Blank)

Test#1 Issued 1/1/2005 Due 1/3/2005 Rcvd 1/4/2005

The script will show the first received date field contents (1/4/2005)

My question is how can we tell the calculation to only look at the newest test received field even if it is blank (no information) and not look at the first entry?

Case ( Get (CurrentDate) > CALC TEST DUE DATE and  not IsEmpty ( CALC TEST ISSUE DATE ) and  IsEmpty ( CALC TEST RECEIVED );"Test Overdue" ))

Note: The "CALC" you see in the code just show the 'last' entries...for example Last: ( Test History::Test Issued )

[comment]

I am having trouble following this - it seems far removed from your original question. Could you explain what this solution is about, which tables are involved, and what are you trying to achieve?

[AlanP]

Sorry Comment! I feel stupid! My post was confusing and it turns out all I needed to do was sort (via the relationship) all the fields in the Test History table to descending and that did the trick (I then updated my calculations to reflect this).

I've attached what this part of the layout looks like (featuring the alert text)

What was happening (before I did the sort, etc) was the 'alert' text worked great (when the test wasn't received by a certain date it would alert you). However when you entered a new test to be issued it would only look at the last test received date (and not at the blank test received date for the current test that was issued).

Edited August 2, 2005 by Guest