Admin Approval of New Records

[alterbentzion]

(I hope I'm posting this is in the right area of the forums. Please let me know if it belongs elsewhere!)

One of the functions of our database is to track interactions between our staff and our constituents. Each interaction record is linked to the record of a constituent. Staff members have read/write access to the data entry layout, but they have limited, read-only access to the list of constituents.

Occasionally (hopefully, often!) our staff meet people who may or may not already be in our database. If these people are, in fact, new, they must be entered into our contact list before any interactions with them can be logged. So I'd like to allow staff members to add new records. But how can I prevent them from creating duplicate records for people who are already there?

Is there a way that I can allow the entry of new records, subject to admin approval at a later time?

Or should I rethink the whole system and do it another way?

Thanks for your help.

[comment]

How do you identify duplicates?

[alterbentzion]

Hi, Comment! It's always so nice to see your avatar.

For now I dedupe manually. I haven't set up any automated/scripted duplicate checking, because (a) I don't have it quite figured out, and ( our organization is very small and for the last twelve years I've been the only regular data entry person, anyway. (I'm also the receptionist, graphic designer, gofer, accountant, et al. I'd gripe about how FileMaker - which I do love! - wasn't as easy-to-learn-while-busy-with-a-career as advertised, but I know there's a very long thread about that elsewhere on FMForums...) :rolleyes:

[comment]

OK, so leaving duplicates aside for the moment, you can add an Approved field and set it to "view only" in the users' privilege set.

[alterbentzion]

[thinking aloud]

Okay. That would leave it up to me to remember to periodically scan the contact list for unapproved records, wouldn't it? But I can add a script trigger that would send me an e-mail if anyone besides me adds a record. (Can FileMaker do that silently?)

Of course, if the user had, in fact, added a redundant contact, and I were to remove it, someone would have to go back to the "Interactions" table and straighten out the interaction that the user had logged. Actually, I think I'd have to fix the interaction log before I could delete the duplicate contact.

Am I making this over-complicated?

Maybe I should just add a button to the layout, by which a user can send me an e-mail asking me to add a contact to the list...

[/thinking aloud]

[comment]

Those are mainly questions about workflow, that only you can answer.

remember to periodically scan the contact list for unapproved records

Perhaps you could make that a part of your startup script.

[Vyk]

This would depend on what criteria you are basing your duplications on but it can be quite simple. This is very rudimentary but bear with me here.

In my "clients" table, I have a calculated field with the values:

Fname &CR&

Lname &CR&

Fname&" "&Lname

email &CR&

I gave them a button that takes them to a decorative helper layout with a global field called "Contact". They can then enter in a name or an email and continue. The script kicks off and does a find against the calculated field. If >= 1 results are returned, I take them to a list layout showing the contacts returned where they can then choose a contact to add information to, or they can choose the "I, do not see my contact here" option and I take them through the steps needed to create a new contact. The contact table has a status flag field that gets set as "1" when the client is entered.

Nightly, I have a script that runs through and finds all "1" values and then compares them on a more stringent level with corp names, phone numbers, emails, etc. If any dupes are found, and it does happen (f.ex. Rod gets added as Rodney once and Rod a second time with personal and business emails), the script then grabs the PK's of the new contact and the agent swaps in the PK from the already existing client. The server sends a mail out to the agent letting them know that they entered a duplicate contact and that contact has been merged with <insert name> contact. The "new" contact's info and the keys for the relationship are then moved into a holding table (keys are in a return delimited list field) and stored for 30 days just in case. The agents then have the ability to go back into the system in less than 30 days and follow a path I setup for them to restore a contact if infact the script was in error.

It's by no means the most elegant way of doing things, but it has saved me a hell of a lot of hassle since I implemented this. It also makes it easier for the agents to be able to see whether or not the contact has been talked to by another agent and what transpired there.