Duplicate Data Entry Validation (Multiple Fields)

[Iainmck]

I'm attempting to use the article below from the FileMaker Knowledgebase to ensure uniqueness of records based on two (or more) fields. However although this works fine when tested directly in FMPRO the validation seems to be ignored when entering records via the web. Has anyone else came across this and discovered a workaround or alternative or should this work OK via the web?

http://www.filemaker.com/ti/103792.html

Many Thanks

Iain

[Anatoli]

Validation from web, Instant or Custom web publishing?

Because it is in CDML I expect it is custom.

Validation from web doesn't really works, because as soon as dialog box appears all the fun stops!

You can test after submitting the data into new record, e.g. with selfrelated portal if there is more than one record with the same key.

HTH

[Garry Claridge]

Is the "-error" Format page returned when a duplicate entry is attempted?

Garry

[Anatoli]

As far as I remember, some validations just pop window in FM. Maybe I did something wrong, but since that time (4 years I guess) I am avoiding validation for web fields.

The -error page will be served if FM reports that into WebCompanion and FM must refuse the data. Maybe current FM is doing just that, I honestly don't know.

[Vaughan]

I remember something about this a long time ago. Back in FMP 4, to get field level validation to work you had to allow web companion privileges to delete records. What happens is Web Companion creates the record, validates it, then needs to delete it a post the error. If it cannot delete the record then it cannot post the error. At least that's how I remember it.

[Anatoli]

Thanks. That is logical! Since I've never allowed deletion of records, maybe I am stupidly programing everything around that Funny!

[jfmcel]

It may be logical but it still does not seem to work on the web.

[Anatoli]

OK, so do not use validation on Web fields, like me

Actually, I was once in my previous job forced to use FM field validation.

So I've used calculated validation from 2 parts like:

FM normal validation OR GlobalField = 1

Before the web processing I put value 1 in the global (do not forget specify record number) with inline, then I did the stuff with update without validation triggering and then I've removed the 1 with another inline. For web entry I've validated the data other way.

It is not taking into consideration multi-user situation, where normal user will not trigger validation when someone just created record from Web, but that wasn't serious issue, because blah, blah, blah...

HTH

[Keith M. Davie]

The solution which I published on the web for several months used FMPro 4.0. I validated a name and password using ScriptMaker. Because I worked out a protected solution, I never had any problems with successfully running the ScriptMaker events in the multi-user environment of the web, so the validations worked fine. So if what you are attempting is really necessary, I recommend using a protected ScriptMaker script for this. I figured out how to do it. So can you.

[Anatoli]

Miss the point, right now we are discussing the field validation mechanism...

[Keith M. Davie]

"Miss the point, right now we are discussing the field validation mechanism..."

Oh, I'm so sorry. Please accept my apology. You are right. I was not discussing the mechanism.

I was discussing the solution. My mistake. How silly of me.

Blame the tool! Blame the tool! Blame the tool!

Goodness, gracious, gollygosh!! I feel so much better now that I've discussed the mechanism.

Thanks Anatoli.

[Iainmck]

Thanks for your input guys but afraid I'm not much further forward.

1. Anatoli - Yes it's a custom solution I'm attempting.

2. Garry - No error page is generated as the record is accepted as if passing validation.

3. Vaughan - How do you go about giving web companion delete privileges? I've so far avoided using the web security DB would it involve this?

IMHO I'd have thought this would have been quite a common requirement for say validating a db which holds line items supplied by suppliers where you want to prevent more than one occurrence of supplier id and product id being associated. Is this another area where FMP falls down big time?

Iain

[Anatoli]

Keith M. Davie said:

"Miss the point, right now we are discussing the field validation mechanism..."

Oh, I'm so sorry. Please accept my apology. You are right. I was not discussing the mechanism.

I was discussing the solution. My mistake. How silly of me.

Blame the tool! Blame the tool! Blame the tool!

Goodness, gracious, gollygosh!! I feel so much better now that I've discussed the mechanism.

Thanks Anatoli.

I don't believe this.

We are validating fields in general and you are starting about user name and password. So you can (again) patronizing people for not using your wonderful script-on-web technique...

Oh well, I've seen that before

[Keith M. Davie]

"We are validating fields in general and you are starting about user name and password."

Gee Anatoli, user name and password are two fields. They could be any two fields. The reader can generalize. Point of fact: several years ago I did not know about the Field Contents Match. I was searching high and low for an answer involving "two fields". I finally (serendipitously) found an article about (guess what) User Name and Password - not two fields.

Get the point, Anatoli, whatever their names, two fields are two fields, nothing more, nothing less.

"So you can (again) patronizing people for not using your wonderful script-on-web technique..."

Actually in my original posting here I did two things. I mentioned a successful solution to the problem and I qualified what I said with, "... if what you are attempting is really necessary...". And yes, my solution is wonderful (for me) whenever I want to run a ScriptMaker Script safely and successfully on the www.

I'm realistic. In no way do I expect Iain to try to develop a solution to running ScriptMaker successfully on the web, even if that would solve his problem. But rather than rag on the mechanism (blame the tool) I suggested a method which is successful, although that method appears to upset you for some reason.

"We are validating fields in general ..."

I don't think so. "Thanks for your input guys but afraid I'm not much further forward.", Iain. In other words, Anatoli, he has not resolved the issue of being able "...to ensure uniqueness of records based on two (or more) fields." That means that you should have written "We are NOT validating fields...".

Since Iain wrote, "... this works fine when tested directly in FMPRO ...", I get the feeling that he is unclear on the concept of the stateless condition of CWP.

I am not clear on what Iain has actually tried.

I infer that he wants to create a new record if and only if it is unique. I also infer that he is trying to design a single step such that if a record currently exists it will be displayed, else, a new record will be created. But I could be well off the track on his design desires.

Patronizing? I hope you make big commissions selling your wonderful Lasso here.