Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×

Finding records that are not there??


John Chamberlain

This topic is 7845 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 confused.gif

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 7845 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.