Identifying Duplicate Dates Within a Record

[wallow]

Hello everyone,

I'm relatively new to FileMaker (particularly the scripting end of it) and have been learning quite a bit from the forums here over the past few weeks. And although Ive searched these lists repeatedly, I'm having problems getting a script I've been working on to function properly.

This is what I need, I hope someone can point me in the right direction: I am basically finished creating a database for a primary care/detox facility, except for one thing. Among various other fields for patients' names, complaints, date of admission, etc., there is a field for the date of their initial Medical exam and a date field for their initial Psychiatric exam. The problem is that most insurance companies will not permit billing from both the Medical and Psych departments on the same day, so they want some indicator alerting the provider if the same date is entered for both fields on a single patient record. A few insurance companies WILL allow this, so a simple alert message that they can "OK" out of will suffice.

How would I go about having FileMaker execute a script of some sort that will check each record upon completion for duplicate dates in these two fields?

Any suggestions will be greatly appreciated. Thanks a lot.

wallow

[andygaunt]

Hi,

Why not create a calculation field (result of text)

If ( Date 1= Date 2, "You have matching dates", "")

Then you can place this field on the layout and color the field to red text so that it stands out. Saves scripting and will show instantly the user tabs out of the fields if the two dates are a match.

[wallow]

Thanks Andy!

That would be perfect. The fact that I wasn't aware of that no doubt shows how new I am....

I was initially thinking that a script would be necessary because I need to figure out a way to alert users of duplicate Psych. and Medical dates not only for each record within this file, but also between these fields on two entirely separate databases. (I didn't want to bore anyone with the details, but there are two other separate DB's for follow-up exams, one for the Medical Dept. and one for the Psych. Dept. An alert also needs to pop up when the follow-up date on the Med file, for instance, is the same as the follow-up date on the Psych file.)

Am I right in assuming that a script would be necessary in this case, or is there a better solution?

Regardless, your suggestion will work perfectly. Thanks again, Andy.

[andygaunt]

Sometimes (in fact most) those "boring" details are the important details.

Your related databases. Is there only ever 1 related record, or can there be multiple related records in both of the related files.

If only 1, then this can be accomplished with a calc field as suggested earlier, using the related date field in the calculation to match up to the date field in the current file

[jasonwood]

Am I right in assuming that a script would be necessary in this case, or is there a better solution?

You could probably use the same technique, provided there could be a 1 to 1 relationship between the two databases (a unique identifier that would identify them as a pair).

Then you could do...

If (myDateField = MyRelatedDatabase::myOtherDateField, "Other date is the same as this date", "")

[wallow]

Thank you all. Your suggestions worked perfectly for the Master file!

As for the two "Follow-Up" DB's, there unfortunately will not be a direct 1:1 relationship between them. When a new record is created for a patient on the Medical Follow-Up file, for instance, they may or may NOT also be scheduled on the Psych Follow-Up file. I need to figure out a way to create an alert based on whether they are also listed within the other file for the same date. (I hope that makes sense.) In other words, if "Patient X" is scheduled on one list and not the other, then nothing happens, but if "Patient X" is scheduled on BOTH lists for the same date, then an alert of some sort pops up.

I've been learning quite a bit from these forums, but this still seems to be beyond my current grasp of things. I suppose the user could simply open both files and eyeball it for duplicate dates if necessary, but if I can avoid that, I'd like to. Perhaps a script that I could attach to a "Save" button or something that would execute a search on the other file for the same patient/date? If there's a simpler way, as your other suggestion provided, that would obviously be better.

Anyway, thanks again for all the help so far. I appreciate it very much. Any ideas regarding the above would be extremely helpful!