Validdating field by calculation to match field in another file

[Miss A!]

I need help creating a validation for a text field to ensure user enters data that matches an existing record's field in another file.

Am failing to create a validation calculation or do i need a script?

 

Many thanx.

 

[BrentHedden]

If you've got a properly setup relationship from the one file (table) to the other, then this should be a fairly strait-forward thing to setup.  In the Validation tab of the field's definition, use the 'Validated by calculation' and just use the case statement, something like this - 

Case(

   Self <> Relationship::datafield;

      0;

   1

)

 

The field validation is expecting a boolean value, so that's why the statement above returns 0 or 1.  The only other thing is to setup the message what shows up when the validation doesn't match.  

[eos]

Case(

   Self <> Relationship::datafield;

      0;

   1

)

​Self = Relationship::datafield  ?

[comment]

 to ensure user enters data that matches an existing record's field in another file.

​Your request is not clear. It can be read in (at least) three ways: (1) require user to enter a value that already exists in some (any) record of another file, or (2) require user to enter a value that already exists in one of the (several) related records in another file, or (3) require user to enter a value equal to a value in a field of a specific (single) related record.

 

The suggestion above assumes (3), which IMHO makes no sense: why would users need to enter the same data that already exists in the parent record?

Edited April 25, 2015 by comment

[Miss A!]

Thank you you Brenthedden ur solution worked easy thanx a lot - am a big zero when it comes to calculations, many at times i can't even try - Thank you.

 

Hi Comment, the second way it is - 

The suggestion above assumes (3), which IMHO makes no sense: why would users need to enter the same data that already exists in the parent record?

Unfortunately for my level i do not have adequate understanding on this u question and my FM environment doesn't help my understanding much either - am taking the calculation provided and just chucking it into my mess and it works am happy and will have myself a reading on *Self* function so that i can help myself on.​ Your analysis is great - and i thank you.

Edited April 27, 2015 by Miss A!

[comment]

Comment, the second way it is -

​I am not sure what you mean by that.

 

P.S. It's 2015. We have the bandwidth. Please use "you" and "your" instead of "u" and "ur".

 

[Miss A!]

You pointed that my request is not clear, that it can be read in(at least) three ways - i meant of the three ways you have specified, the second one is right for my situation.

 

 

I got it working from the solution provided(thank you Brenthedden) but i still welcome your suggestions/points, if any - Thank you.

 

[comment]

You pointed that my request is not clear, that it can be read in(at least) three ways - i meant of the three ways you have specified, the second one is right for my situation.

​Yes, but I also said that the suggestion provided by Brent Hedden assumes that the correct way to read your question is the third one. It will not work correctly in your situation, because it compares the user's entry only to the first related record.

In order to verify that the value entered by the user exists in at least one of the records related to the current record, you need to use:

not IsEmpty ( FilterValues ( Self ; List ( RelatedTO::Somefield ) ) )

 

Another option is to define a value list using values from RelatedTO::Somefield, including only related values. Then you can validate the field by using the 'Member of value list:' option, instead of by calculation.

[Miss A!]

Actually to compare even on first record existence is ok - As long as the data the user is entering exists in the other file, don't really matter how many records match, so long there is existence, this is just so the user don't input values that are non-existent. Thank you for the verification solution suggested - i am going to need it elsewhere, sure thing.....i will attempt it for now on backed up copy, Thanks.

 

Another option is to define a value list using values from RelatedTO::Somefield, including only related values. Then you can validate the field by using the 'Member of value list:' option, instead of by calculation.

This is a great one i have used before but can't in this situation because it will be a very long value list of more than 10 000 values - i think it would be absurd for the users.....

 

 

This is nutritious input - Thank you so much!

 

[comment]

Actually to compare even on first record existence is ok

​I am afraid you are still missing my point. Suppose the related records in the other table are:

• Adam
• Betty
• Adam
• Cecil
• Betty
• Cecil
• Adam
• David

Now, when user enters "Adam" into a field in this table, it will pass validation because:

RelatedTO::Somefield

evaluates to "Adam" (the data in the first related record) and therefore:

Self = RelatedTO::Somefield

returns true.

But when they enter "Betty" or "Cecil" or "David", the validation above will reject it the same as if they had entered "Green Apples", because the expression does not look beyond the first related record.

 

  it will be a very long value list of more than 10 000 values - i think it would be absurd for the users...

​I did not suggest that users use it.

[Miss A!]

Sorry but now i am getting confused, - the info above, i can not digest.......I tested all expressions both of you and Brenthedden, they worked alike - i felt satisfied.

 

 

​I did not suggest that users use it.

but you suggested it as another option - for who's/what use?

[comment]

I tested all expressions both of you and Brenthedden, they worked alike - i felt satisfied.

​Sorry, I don't know how to make this any clearer.

 

but you suggested it as another option - for who's/what use?

​For the purpose of validation.

[comment]

In the attached file, try to enter "Betty" into the Value1 field.

demo.fp7

[Miss A!]

But Comment, the expression in your calculation is not on the field used in the relationship between the tables, you are relating by id field but your expression is on the Value field - In my setup, the two fields relating the tables are the ones i am using in my expression and it works well - beyond my understanding still, but will come back to it.

In the Thumbnails attached, i am validating TourNumber field in PackSafari Remittance Table on TourNumber field in Resservati as follows;

Case(
Self<>Reservati::Tour_Number;
0;
1)

In my setup, I have tested with your expression as well, works too but using the above expression in the file you provided also don't work

Also(and how) is the expression above equivalent to this yours expression below: 

Self = Related::Value

What do i need to understand?

 

Many thanks - for your good efforts and willingness.

[Miss A!]

Now, just i changed the relationship setup in your file such that;

Local::value1=Related::Values

Using expressions earlier suggested(both yours & BrentHedden), working great - What do i need to understand?

 

Many Thanks.

 

[comment]

Now, just i changed the relationship setup in your file such that;

Local::value1=Related::Values

​Then you are in scenario #1, not #2.

[BrentHedden]

Now that I review this further, Comment is right <as usual> - my solution will only work in specific circumstance.  Which is probably not what was originally setup.