Hi.

I'm new to FM and have attempted a database to keep track of conversations, issues and incidents within out staff.

What I am looking for is to have a hidden field named 'Final Notice' that will show within the employee's profile if they were placed on Final Notice.

I have unique tables for the overall employee stats, issues, incidents and conversations all linked by the Employee ID field.

Within the issues and incidents tables there is a drop down list of disciplinary action, ie verbal, written or final notices.

I'm looking to have the hidden 'Final Notice' field within the employees table show text (Is on Final Notice, YES or what have you) if either of the issues or incidents tables have an entry noted with Final Notice.

Any and all help would be great.

THANKS!

With FM9 you can use conditional formatting to show/hide some text on a layout so you wouldn't need a separate field. You'd just make a condition for each criteria.

If you are wanting a calculated field it would look something like:

Case(

issues::disciplinary action = "final notice" ; "final notice" ;

incidents::disciplinary action = "final notice" ; "final notice" )

Edited January 21, 200817 yr by Guest
Comment's post below is correct; use his calc for multiple related records.

That would look only at the first related record in each table. To test all related records, you'd need something like:

not IsEmpty ( FilterValues ( "Final Notice" ; List ( Issues::DisciplinaryAction ) & ¶ & List ( Incidents::DisciplinaryAction ) ) )

But I am curious how a field can be both hidden and show:

What I am looking for is to have a hidden field named 'Final Notice' that will show within the employee's profile

Your could put your calc field on the Employee record without a field label, so if it doesn't have a value, it would "not show."

Do you see a portal of issues and incidents on the Employee form view? If so, why the need to hide Final Notice, since anyone can see what's going on with this employee by reading the incidents?

So I tried some of the calcs. and they work, sorta.

The Case function stated above

Case(

issues::disciplinary action = "final notice" ; "final notice" ;

incidents::disciplinary action = "final notice" ; "final notice" )

works but only shows Final Notice if the first record in the portal is marked Final Notice. It won't search below the first record in each portal.

Second, the isNotEmpty function stated above

not IsEmpty ( FilterValues ( "Final Notice" ; List ( Issues::DisciplinaryAction ) & ¶ & List ( Incidents::DisciplinaryAction ) ) )

works but only shows a numeric value of 1 which won't quite do.

I've tried each of these attached to a calc. field without a label to no avail.

I do have portals but we have a lot of people who will be in this and need to know quickly if they are on final notice or not.

Is there any way to combine the two functions?

I am willing to bet you will find the answer yourself within 5 minutes after reading the help on Case() function.

Look at the two calcs you quote. Note that each Case statement has first an expression that gets evaluated on the left, and a result on the right to return if the expression on the left is true.

Then note that the second item you quote is an expression; it returns 1 when true.

So...

If you can stuff any expression into a Case statement, why not stuff the second one quoted into the Case statement in your first quote?

Not to be obtuse...but the answer's in your own post : And, as Michael says, at your fingertips in the Help file.

Which, actually bears looking into any time you're stumped. It's pretty comprehensive, with sample code for each function.

In other words:

Case( 

       not IsEmpty( 

             FilterValues( "Final Notice" ; 

                            List( Issues::DisciplinaryAction ) & ¶ & 

                            List( Incidents::DisciplinaryAction ) 

                           ) 

                      ) ;

      "Final Notice" )

Another fun way to write it: 

GetValue( 

          FilterValues( "Final Notice" ; 

                         List( Issues::DisciplinaryAction ) & ¶ & 

                         List( Incidents::DisciplinaryAction ) ) ; 

         1 )

In other words:

The List functions return the related records. From the two lists, we filter out and return only the records whose value is "Final Notice." At that point we might have one or more items, but we just want the first one: GetValue( ... ; 1 ) gives us that. If there is no match for "Final Notice," we'll just get a blank, which is what we want.