All-Encompassing Challenge

[ddreese]

I didn't know where else to put this, the problem I'm having deals with relationships, scripts, all the good stuff. Anyway, enough talk.

As simply as I can put it:

- Two files, Household Members and Allegations.

- If a record in Household Members has a value of 'Commission' or 'Omission' in the perpetrator field, that person (record) is going to need to have a record in the Allegations file.

- This will need to be as dynamic as possible. i.e. say someone accidentally labeled a person to be a Perpetrator by Commission, while their field should really be N/A (thereby not needing an allegation record).

So far, the way I've come along to solve this was whenever I create or edit a new household member, I have to hit an 'OK' button to return to whatever layout I came from. Therefore I can have it run whatever script I need to, otherwise the users won't be able to navigate back to their original location. Basically, the script needs to accomplish the following:

- If it's a perp by 'Commission' or 'Omission', Pull all the related records from the Allegations file based on a 'Case ID' file (this identifies all household members involved with the case). Check to see if the household member has an Allegations record. If so, good. If not, create one.

- If the household member is NOT a perp by 'Commission' or 'Omission', check to see if they have a record in the allegations file. If so, delete it. This could happen if someone mis-labeled a person as a perp, and changes their status later on.

Basically, what is happening is when a 'Household Member' is listed as a 'Perpetrator', they need to have a list of what 'Allegation' they have done to the 'Victim Child'. Many times, there are multiple Perpetrators for each case, and they don't necessarily have the same 'Allegations'. Originally, I just had one 'Allegation' record per case, it would list all the 'Allegations' (Sexual Abuse, Neglect, etc.) for the case, but we need to track it by who did what.

This doesn't seem to be overly difficult, but I haven't had enough advanced scripting to understand how to achieve this with scripts. I appreciate you taking the time to read this, and I will answer any further questions anyone has on the matter.

[Fenton]

I don't see this so much a scripting problem. Your relational model is correct, as far as it goes, but it has one area where "redundancy" is causing problems.

Whether a particular allegation is a "commission" or "omission" is an attribute of the allegation itself, not of the perpetrator. It really belongs in the Allegations file. You could also show it in the Household Members file, but best as a related field (either on its own, or in a portal; same thing), next best as a lookup; but the latter presents maintenance problems, as you are finding out.

This will solve your current problem, and will allow multiple allegations per perpetrator, which seems to me possible in the future. It introduces some reporting weakness in the Households file, but detailed analysis should come from the Allegations file anyway.

BTW, the primary auto-enter ID of the Household Members file is the perpetrator ID, not the household ID. The latter is a self-relational grouping mechanism; but important as it's also a foreign key in the Victims file.

You'd also need a Cases file, with a Case ID.