Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Complicated Weekly Audit process: Multiple Criteria


bselph
 Share

Recommended Posts

Hello All,

 

Here is my problem: I have three tables that are related to document participation in group therapy.

 

1. TABLE: Clients --- each client may have multiple "group occurance", each occurance one client.

 

2. TABLE: Groups --- each group can have multiple "group occurance", each group assignment one "group"

 

3. TABLE: Group occurance--- each group occurance has one client and one group. (Fnkey from groups and clients). Also Recorded: scheduled, offered and recieved time for each group occurance and "reason" for no show.

 

GOAL:

A list of all clients who are "eligible" and ineligible for each week based on the criteria in "group occurance::reason" and wheather they attended group on that Monday. The eligibility shouls be modifiable due by the auditing user (some reasons for being ineligble are not available). Also total amount of time in group by week for those who are eligible.

 

How to a create a found set that only contains records from "client" table that have records from a specific week in "group occurance" table?

 

How do I set "eligibility" in each group occurance record based on all group occurance records for that week?

 

Is there a more elegant way to solve this problem?

 

Thanks!

Brac

Link to comment
Share on other sites

Hello All,

 

Here is my problem: I have three tables that are related to document participation in group therapy.

 

1. TABLE: Clients --- each client may have multiple "group occurance", each occurance one client.

 

2. TABLE: Groups --- each group can have multiple "group occurance", each group assignment one "group"

 

3. TABLE: Group occurance--- each group occurance has one client and one group. (Fnkey from groups and clients). Also Recorded: scheduled, offered and recieved time for each group occurance and "reason" for no show.

In "Filemaker speak" your "Group Occurance" table is a "join table" between Clients (many) and Groups (many), or the "join beween a many-to-many relationship."

 

GOAL:

A list of all clients who are "eligible" and ineligible for each week based on the criteria in "group occurance::reason" and wheather they attended group on that Monday. The eligibility shouls be modifiable due by the auditing user (some reasons for being ineligble are not available). Also total amount of time in group by week for those who are eligible.

 

How to a create a found set that only contains records from "client" table that have records from a specific week in "group occurance" table?

 

It appears you will have to search for Monday group attendance in your Group Occurrence table, and use the result(s) to decide about scheduling.

 

An alternative method is to use a Group Occurrence portal on a client layout.

 

 

How do I set "eligibility" in each group occurance record based on all group occurance records for that week?

I am not understanding your question. What criteria do you use to set eligibility?

Link to comment
Share on other sites

Okay...let me try again... I know I am in over my head when I can't even articulate the problem.smiley-laughing

 

I would love to have a layout that looked just like a subsummary report with a breakfield of "ClientID", displaying all groups attended in a particular week, except allow the user to input data. The user needs to indicate that the client was eligible or not. So I would need to create a field in the "group occurance" table to capture that data. The logic goes like this: if any group occurance for a particular client is "ineligible" then all group occurances for that client for that week are ineligible.

 

Is that any better?

Link to comment
Share on other sites

Okay...let me try again... I know I am in over my head when I can't even articulate the problem.smiley-laughing

 

I would love to have a layout that looked just like a subsummary report with a breakfield of "ClientID", displaying all groups attended in a particular week, except allow the user to input data. The user needs to indicate that the client was eligible or not. So I would need to create a field in the "group occurance" table to capture that data. The logic goes like this: if any group occurance for a particular client is "ineligible" then all group occurances for that client for that week are ineligible.

 

Is that any better?

Yes... and no.

 

I still think that a portal view may be your best option. I would number the weeks in the join table (or somehow identify them so that no two weeks share the same ID) and use a global field relationship for the portal to view "Group Occurrence" by week. That way you can look at all clients attending groups sessions for the entire week (sorted alphabetically to see repeats).

Link to comment
Share on other sites

Thanks. I ended up using a subsummary report with calculations and then having the user do the "audit" manually.

 

Thanks for your help.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use