Finding records that are not there??

[John Chamberlain]

I have created a file that contains name fields for all persons in a group. Within this file certain individuals are identified as Registered Nurses or Social Workers.

I have a second file that indicates one or more classes attended by persons within the overall body of names from file #1. Some classes are requied for everybody, while others only require attendnce by RNs and Social Workers. The files are related by the person's identity number (which is assigened by file #1).

I have been asked to provide an exception report that will show all RNs and Social Workers who have NOT attended one of the classes. This puts me in the position of trying to find records in file #2 that are not there.

Can anyone tell me how to get out of this box??

Thanks,

John Chamberlain

[Ugo DI LUCA]

Hi John,

Why not use a calculation :

MatchnotAttendees = Case(Is Empty(your relationship), 1, 0)

Then do a find for all records where MatchnotAttendees = 1

[LiveOak]

I think you mean IsEmpty(Relationship::SomeFileThatIsAlwaysPopulated) or notIsValid(your relationship). Make sure the calculation is set to always calculate.

-bd

[John Chamberlain]

Hi Ugo

Guess I need to amplify:

File #1 contains name data for all employees and volunteers, some of whom are RNs or Social Workers.

File #2 consists of records of attendance for those that have had instruction of any kind. At the moment there are only two different classes (call them A & :, but soon there will be others, each with some individual requirements (all MDs, all staff, all volunteers, etc.).

All employees and volunteers have to attend class B, so it is not much of a problem to run an exception report for this one.

Class A is only a requirement for RNs and Social Workers, many of whom have taken Class B. Thus, some RNs and Social Workers have taken Class A & B, some have taken just A or B, and some have taken niether.

If a person has taken B then the relationship is true.

What I need is a find for those persons who are RNs and Social Workers and have NOT taken Class A. (whether or not they have taken Class B.

Now, are you as confused as I am?

Regards,

John

[Pupiweb]

Try with:

Make a relational value list (using the relation you have between employees and attendance)

A calc field

ValueListItems(YourFile, "RelationalVL name")

will return a return-delimited list of classes taken by each employee

Make a calc like

Case(

EmployeeType = "RN" and PatternCount (TheAboveCalc, "Class A") >0, "OK",

EmployeeType = "Social Worker" and PatternCount (TheAboveCalc, "Class A") >0, "OK",

"NOT OK")

In plain english it would mean that if an employee is a RN or a Social worker and has taken class A the result is "OK"

Adjust the calc to your needs

HTH

[John Chamberlain]

Sorry to be dense. I tried to get past your first caclulation field and failed. I can't find any reference in any of the 5 FMP books that I have to a Relation Value List. I think perhaps you are too far advanced for me.

Please explain using the following field names:

In the Bio Master file:

Employee Type (consists of 5 types including "Employee RN/SW")

In the Attendance Master file:

Class Name (in this case "General Inpatient Level of Care Part 1")

I need a find, that I can later use in a script, that will find those who are Employee RN/SWs, and have not taken the General Inpatient Level of Care Part 1 class.

I have attached simplified versions of these two files

[John Chamberlain]

Here is the second file

[Ugo DI LUCA]

Hi John,

In the Bio Master file, go to Define Value List.

1. Create a new value list called "list of classes"

Check the option "from another file"

Choose the Attendance Master file

You will get a list of all fields from this file.

Pick-up the Class field.

-----> You now have a value list of all classes created in Attendance

2. 1. Create a new value list called "list of classes related"

Check the option "from another file"

Choose the Attendance Master file

Check the option "related"

Pick-up your relation IdentityN

[Ugo DI LUCA]

To conclude with this,

create another value list "attendancechecking" in the Bio File

Choose the previous calc as the field for that list.

You wil get a list OK, NOT OK.

Then create a global field "globalcheckattendance" and a self-relationship Bio: globalcheckattendance::Bio:Previous calc.

Draw a portal using this relationship.

This portal will show you either the persons who did attend to class A (choosing "OK" from the value list) or that didn't attend.

Stronger and more dynamic that any find....

[John Chamberlain]

Sorry Ugo, the option "from another file" is only in FMP 5+. I am stuck with FMP 4 because that is what they have on their PCs. Is there a way to do this in FMP 4?

Regards,

John

[Ugo DI LUCA]

Hi John,

Going back to my FM 4.1.

You can create a value list from another file. What you cannot do is set up a relationship for this value list.

This will be less dynamic but can work anyway :

1.Create a global field in the Bio File and define its value to be exactly the same as the Class A Id in the Attendance File.

You could do the same for any Class, with as many globals as Class_Id's in the Attendance file.

2.Create a concanated calc field in the Attendance Master File.

c_ConcanateAttendance = IdentityN

[John Chamberlain]

Thanks, Ugo.

THis is a lot for my old brain to absorb - it will probably occupy my weekend.. I'll let you know how I made out later.

Regards,

John

[Ugo DI LUCA]

Hi John,

Good News for u,

You can index a non indexed calc in FM 4.1 as well. Just follow the steps in the Article section for this or ask for more guidance here (or private).

[John Chamberlain]

Hi Ugo -

I can't seem to find any article on indexing non-indexed calcualtions - can you give me a title for this article?

Thx,

John

[Ugo DI LUCA]

here

[John Chamberlain]

Thnaks Ugo - I'm still trying to get steps 1 - 5 to work, but this will help once I do.

Regards,

John

[John Chamberlain]

Thanks UG0 - I'm still trying to get steps 1-5 to work, but will use this thread once I do.

Regards,

John

[John Chamberlain]

OK Ugo - that makes two dinners I owe you - got everything working perfectly.

THANKS!

John

[Ugo DI LUCA]

I'm on a diet

But some Seafood in Newport Beach is still OK. I'll be in Santa Anna for some days in May...

[John Chamberlain]

I hope those dates are between the 13th and the 20th or on the 1st or 2nd. If so, let's get together for dinner.

Santa Ana is only a 50 minute drive from where we are, and Newport Beach is just fine with me.

Regards,

John

[Ugo DI LUCA]

Hi John,

Too bad....

24 to 30 are my dates...in Costa Mesa (don't know why I said Santa Ana, it must be close anyway).

I'll be back in september

[John Chamberlain]

Okay - let's plan on September - sorry I won't be around when you are here, but my son and I will be in Indianapolis for the 500, then up to Michigan to visit my great grandchildren.

Contact me at [email protected] when you have firm dates for September.

Regards,

John