Data entry validation - expression/calculation ??

[Hillary Knox]

I'm pretty new to FileMaker and very new to building expressions...

I'm trying to build an expression that will compare 2 fields and not let me create 2 records where field A contains a value and field B contains a value. For example, I want to make it impossible for the following to happen:

Record 1: Field A=1; Field B=100

Record 2: Field A=1; Field B=100

In this table, it is possible for Field A to have duplicate values amongst many records, and for Field B to have duplicate values amongst many records, but there cannot be any duplicates as I specified above. I just don't know which expression parameters will allow me to achieve this.

For example, the following would be allowable:

Record 1: Field A=1; Field B=100

Record 2: Field A=1; Field B=101

Record 3: Field A=2; Field B=100

Record 4: Field A=2; Field B=101

but what would not be allowable:

Record 1: Field A=1; Field B=100

Record 2: Field A=1; Field B=100

Record 3: Field A=2; Field B=101

Record 4: Field A=2; Field B=101

Any thoughts about an expression that would allow me to achieve this ?

[QuinTech]

Hi, Hillary, and welcome to FM Forums!

Make a relationship from fieldA to fieldA and fieldB to fieldB (between the same two table occurrences). Then put the following validation on both fields:

IsEmpty ( TO2::fieldA )

I hope my FM7 grammar is ok. As i've said before, i'm still getting the hang of it... The general idea is, create a relationship to find find records where both fields match, then make sure that relationship is empty.

HTH,

Jerry

[transpower]

Concatenate the two fields (fieldA & fieldB) as an auto-enter calculation; then have a validation check for uniqueness.

[QuinTech]

Transpower, i don't think this will work; isn't it the case that the concatenated field will only be auto-entered upon editing the first field? That is, if i enter 1 into fieldA, then concatField is auto-entered to 1; if i then continue to fieldB and enter 100, concatField is not changed because it already has a value. Or am i mistaken?

J

[Hillary Knox]

Thanks!...that did the trick - the relationship, that is...I would try to concatenate the fields, but since I don't know what concatenate means, or how to do it, I'll probably just go with what I have...

Thanks for the help.

[transpower]

QuinTech: I tested my method and it works fine.

Hillary: Concatentate means to join. So if you have fieldA and fieldB, concatenating the two makes fieldAfieldB.

[Lee Smith]

Hi Hillary, and Welcome to the Forum.

Thanks!...that did the trick - the relationship, that is...I would try to concatenate the fields, but since I don't know what concatenate means, or how to do it, I'll probably just go with what I have...

Thanks for the help.

The word concatenating is used often in FileMaker, you can look up the meaning and how it works in your Online Help, when FileMaker is open, by typing or pasting concatenating text into the Index Window.

transpower has suggested that you use FieldAFieldB as the calculation, this will not work, you need to use the & (and sign) in the calculation FieldA & FieldB

HTH

Lee

[-Queue-]

Hmm. I would use FieldA & "_" & FieldB unless you want 111 (1 11) and 111 (11 1) to be seen as invalid.

[QuinTech]

Transpower, i've just tried it and couldn't get it to work. Could you post an example so i can see how it should be done? Thanks.

J

[QuinTech]

Oops... just figured out that i needed to uncheck "Do not replace existing value for field".

I've said it before... still getting used to v7.

[Hillary Knox]

Could someone post an example of how this concatenating expression should look ? I thought I had it figured out, but the relationship & validation expression I was using somehow screwed up a couple of other relationships that I need.

[QuinTech]

If it screwed up other relationships, i'm guessing you added this relationship to another existing one. What you should do is create an entirely new table occurrence and make the relationship from your main TO to this new TO.

J

[Hillary Knox]

Well, I know that creating a new table occurrence and making that relationship happen was the one thing I did right...I suspect the thing I did wrong was to mess up the validation expression.

When I say "screwed up a couple of other relationships", I'm probably giving you the wrong idea. The problem I'm now having is that when I have 2 records where FieldA has the same value, all the information related to FieldA (like name fields and description fields which are established as records in another table) does not get filled in automatically for any records after the first record.

In my layout, the name and description fields are from their "home" table instead of being related fields in their own table...does that matter ?

[QuinTech]

I don't think there should be any connection, unless i'm missing something. Can you post your files?

J

[Hillary Knox]

I think I got it figured out...I needed to turn on Validate Always instead of Validate Only On Data Entry - even still, sometimes it takes a while for the name & description fields to update - anything I can do to make this faster ?

other than that, hopefully I'm good for now...thanks for the help.