Duplicate Records elimination concept

[cdr-dev]

I have a customer who wants to be able to determine before a new recorded is created, if a existing record exist.

What I am conceptualizing, is a script that will display a message if the user is creating a new entry and inputs a field entry(in this case, a patient ID number), and when they hit enter, it will check for duplicates and if a match is found, it will display the message "duplicate record already exists, otherwise it will continue on with creating the new record.

What I have though about so far is this. I am not sure what commands to apply to the code so if any of you FM scripting experts could list what entry or method to better perform this type of function, that would be excellent!

When "New entry" button is selected, it will perform a new entry - enhanced script:

Enter Find Mode

Go to Field "Patient ID Number"

// User enters the number, then hits enter

If "Patient ID Number = The user input, Then

Display message = "Duplicate record exists"

Else If no entry is found

Create New record

End If

I am not familiar enough yet with Filemakers function dialog so if you guys could walk me through this senario, this would solve this problem.

Thanks in advance.

Chris

[stanley]

Chris:

One way to do this kind of thing is to create a set of global fields which will only be used during the data entry phase. Create globals for all the fields you want to perform this kind of duplicate-field testing on. When someone wants to create a new record, send him to a layout containing these globals (remember to clear them first), and let him enter only that data first. Have a "continue" button attached to a script which will then run through your system looking for matches to your globals. Because the globals are stored throughout the db, you can parse them one at a time, essentially as you've outlined in your post. If there are no duplicates, copy the globals into their corresponding text/number fields, and allow the user to continue entering the rest of the data.

-Stanley

[cdr-dev]

Stanley,

Your concept is really good! but I have some key questions,

How would you define the syntax for this type of configuration?

How would you define when the user has entered "enough" data in this instance?

If we look at just one field for an identifier, we should be able to determine from there if a dupe is going to exist or not. Using a "If Then, Else If" statement we should be able to search the database. I just don't know the proper command syntax to follow. Does that sound reasonable or would that be out of scope for a script to handle?

If there is a way I could capture what the user types in the field, could I use a "get" calc function to capture what she type in and assign it a string?

I guess I should mention, I want to keep this programatically as simple as possible with only the required fields defined in the layouts and let the scripts do the data manipulation. As a matter of principle, I prefer the scripts and plug-ins to do their assigned job in the system.

Chris

[-Queue-]

Create a relationship between the global id field and your id field. When the user continues, Commit Records/Requests and test If [not IsEmpty(relationship::id)]. If it's not empty, a related id already exists and you have a duplicate.

[transpower]

In field validation, choose "Unique."

[-Queue-]

That is one possibility. However, it's better not to allow creation of possibly duplicate records at all via the global validation technique. Field validation can be a serious pain-in-the-ass. If you want to go this route, be sure to test it thoroughly and make sure users have an 'out' and don't get trapped, and limit validation on other fields as much as possible, especially any set to always validate.

[cdr-dev]

Ok, I have created a relationship between ID and a Dupe ID field. If I set the Dupe ID field to global storage, then I do not get any display for text when the ID field value is entered. The strange thing is when I set the Dupe Id field to just a regular field and allow records to be added and deleted per the relationship, I type in the ID field and nothing shows up in the Dupe ID field until I type a single number, then all the sudden it showed up. Strange problem...any ideas?

dumb question, but where do I find the commit records/request function? I can't find it in the custom dialog. Is it somewhere else?

[-Queue-]

Danger, Will Robinson! I think you're a bit confused on the concept. You most definitely do not want to be able to add or delete related records in this case, nor do you want the temporary entry field to be non-global.

Here's the idea, in a little more detail. You want to have the initial id entered into a global text or global number field (depending on which your ID field is; the types must match or the relationship won't work) on an entry layout with only the global and a Continue button. No record has yet been created. When the user continues, the script attached to the button should

Commit Records/Requests <- located under the 'Records' category in the script step list

If [not IsEmpty(relationship::id)]

New Record/Request

Set Field [iD; globalID]

Else

Show Custom Dialog ["It already exists."]

End If

Go to Layout [some Layout]

Does that make a little more sense?

[cdr-dev]

We are about 98% there! Yes...that cleared up quite a bit.

I am new to learning these scripts but I am catching on. I appreciate you taking the time to illustrate this!

So one small clarification I need. When you say the field types must match or the relationship won't work, are you referring to if both types are text and number define fields or the global definition itself for fields?

Queue, Transpower, Stanley, you guys know your stuff well. Thanks for helping.

[-Queue-]

The global's type must match the ID's type. So, if your ID field is defined as number, you'll want a global number field; if it is defined as text, you'll want a global text field.

[cdr-dev]

Queue, Few questions for today For the Global ID field, in the properties of that field under the "validation" tab, would there be any particular setup here?

In the "Validate data in this field" seletion, I have selected "only during data entry" and "allow user to override during data entry".

For the "Require:" selection, nothing is selected.

I did a table view of the global id field in browse mode and for some reason, 2 entries are showing up, both of which are the same ID number that I had typed in to do testing with. The global field selection is checked for this field and allows one reptition. The Global ID field in the second table the relationship is identified between the ID field in the first table and they are both identified as number.

I also tested on the second account number 3 times and when I view that account number, it shows it listed 3 times which is also how many times I have tested this account number as well. The global field appears to be displaying only one number but showing how many times it was validated. Is this how it is suppose to work? If I do a find for an existing number again, be it the first number or the second number. Guaranteed I will not get the message "record already exists" and it will take me to the entry screen.

Strange situation. Let me know I should clairfy further. And thank you again!

[-Queue-]

No, you would not validate the global. The validation occurs via the script when they continue.

Bah, I should have used IsEmpty instead of not IsEmpty. I flipped the order of the Ifs and forgot to flip that also.

[cdr-dev]

If I take the "not" out of the If statement, then it always displays the custom dialog message and goes to the first record instead of a new one. Even if I enter a number that isn't in the database.

[-Queue-]

Make sure you're using the correct relationship, from globalID to ID. If you are, then it should work.

You can zip and attach the file if that doesn't work.

[cdr-dev]

Queue, I have attached the file here. See what you might be able to uncover.

Login is admin, no password

[-Queue-]

While in Layout Mode on the New Patient Entry Screen, go to Layouts -> Layout Setup. Change it to 'Show records from' Patient ID Identifier.

In the Dupe Find script, after the IsEmpty test, Go to Layout ["Sleep Center Patient Database"], so that the new record is created in the correct layout. The Patient ID Identifier table needs no records, so delete them all.

[cdr-dev]

Awesome! It works perfect!!! Thanks -Queue-, you really know your scripting!