Hi,

What is the best way to validate a relationship will be valid before
creating records.

e.g. I have a function that creates a new record in a table taking as
a parameter an id of another record in another table. I want to
validate that the value in the parameter really is a valid id.

A couple options might be:

To do script a find in the other table and check the result, but in
that case I'd need to add a utility layout.

To do create globals, and base relationships off of that, then assign
the global value, and see if there is a record through the
relationship. obviously that will require global fields,
relationships, and table instances.

Neither is ideal... but either can work... is there something else
people do for this? Of the two options above which one is preferable
to the other? (and why?)

-cheers,
Dave

In article <1176339929.626062.247470 (AT) p77g2000hsh (DOT) googlegroups.com>,
"d-42" <db.porsche (AT) gmail (DOT) com> wrote:

The second, Global fields, method is probably the best and easiest. I
don't really know what "table instances" are, but once the Relationship
is defined you don't actually need any related fields on the current
layout to accessing them - just a script command along the lines of:

Set Field [GlobalField, LinkData]
If [Count(Relationship::KeyField) = 0]
{do whatever for no related records}
Else
{related records do already exist}
End If




Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Pardon me "Table Occurrences" (on the Relationships Graph)

Interesting.

Yeah, I can see the simplicity advantage in script coding, but the
expense is in design complexity, as the validation check can't be
contained by a script but instead has to spill-over into the data
design, adding fields, table occurrences, and relationships.

In order to avoid obfuscating the data model with global validation
relationships I find myself creating a whole separate tree in the
relationships graph just to support the validation.

A side effect of splitting it into a separate tree is that I have to
create a dedicated layout to do the validation testing, because the
'global relationships' are connected to a different table occurrence
than the real data model. This also means the script has to flip
layouts.

And at that point, since I already need a separate table occurrence I
think its best to put the globals in a separate dedicated utility
table so the fields don't pollute the data table.

The script approach would save me the whole tree; its perhaps not
quite as efficient, but it keeps the database design easier to
understand, which in a complicated solution might be worth something.
I'm dealing with a solution with over 40 core data tables plus many
more utility tables, work tables, and so on, adding 30+ validation
trees to the graph is an unwelcome prospect.

I know I -could- just attach everything to the data-model; and in fact
that's what I'm coping with right now -- monstrous "spiders" graphs
from the v6 to v7 conversion. And as people started dangling 'deeper'
relationships off the spider to add functionality the result is an
incomprehensible mess.

Thanks very much for your insight; as you can see I was leaning
towards the script (and hoping for something even better). But if, as
you say, standard practice is via globals though; then I should
reconsider. After all adding 30 validation trees won't make the
database much harder to understand as the individual trees themselves
are simple and separate from the main data model, but it will make
some of the already 'stuffed' value-lists (like choosing a 'table
occurence' in set-field) even *more* stuffed.

In article <1176347935.042623.298080 (AT) e65g2000hsc (DOT) googlegroups.com>,
"d-42" <db.porsche (AT) gmail (DOT) com> wrote:


You can use a similar method in a field's Validation by Calculation
options.
eg.
Count(Relationship::KeyField) <> 0

where "<>" can be either that or the "not equals" symbol, it makes no
difference since they both mean the same thing.

BUT,
the problem is that you can't set the GlobalField value first unless
the user has done that manually or you do it via a script anyway, so
the current Relationship may not be the one you want.


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Apr 13, 4:15 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Yeah, I don't see that really helping; if anything it just makes it
worse, because now the functionality is being defined in the field
definitions, the table occurences, and the script. And in this case I
have a script function that takes 3 fkey parameters, wants to verify
they are valid, and if so create a new record and return its pkey as a
script result. If they aren't valid, it needs to return -1 as the
pkey, and set a global with an error message indicating WHY it didnt'
create a record so the calling script knows what happened and can
handle the situation. Its really not suited to filemaker's field built
in field validations which are really more for simple interactive
users.

One other note about your suggestion of:
count(table:key) <> 0

Why not just validate >0 ? It simply can't be <0 and it dodges the
whole "<>" vs "not equal to symbol" discussion entirely.

Personally, I just count(table:key) and leave it at that. Its
ultimately a boolean evaluation so if the count is 0 its false, and if
its nonzero its true.

I can see the sense of explicitly comparing it to 0 in terms of
writing self-documenting code; I can see how you might argue the
explicit comparison to 0 might be a little better at communicating
your intent.

In any case, its a matter of personal style.

In article <1176588953.598556.257420 (AT) n76g2000hsh (DOT) googlegroups.com>,
"d-42" <db.porsche (AT) gmail (DOT) com> wrote:
It makes no difference - FileMaker sees "<>" and the "not equal" symbol
as the same thing. As a programmer for *cough**cough* years I've simply
become used to typing "<>" and usually don't think of the "not equals",
even though it is easy enough to type on the Mac (Option =). I VERY
rarely bother to use the buttons.

You could just as easily use "> 0". I simply inverted the previous
example's "= 0" test. )




True. You could go with the Boolean version too ... although I've never
tested in FileMaker whether a Count of 5 (for example) does qualify as
"True" or not.

With lots of "beginners" around here who don't understand Boolean
tests, I tend to specify the tests out fully to make them (hopefully)
more easily understood.

There's also other tests you could perform to achieve the same thing.
eg.
IsEmpty(Relationship::KeyField)

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Or Isvalid(Relationship::KeyField)

The cCount has the disadvantage it does not return 0 when there are no
related records, but returns empty. (at least in my v8.5 adv). You have to
build a wrapper to return a 1 or 0. I observed this some time ago and even
built a testfile to recreate this.

If ( isempty (count ( relationship::KeyField ) ) ; 0 ; 1 )

or shortened to

isempty (count ( relationship::KeyField ) )

But then it would return a 1 when the relation is not valid, which might be
confusing.

Keep well, Ursus

On Apr 15, 4:45 am, "Ursus" <ursus.k... (AT) wanadoo (DOT) nl> wrote:
Good point, Harry.

You know Ursus, I haven't ever used isvalid.

I tried using it to validate the relationship was active way back when
I was an FM newbie and it didn't work out at all. It always came back
valid. Then I read the help. And the help seemed to indicate that it
would only return FALSE if there was a data type mismatch - text in a
date field, or if there was a database design flaw -- like referencing
a field that had been deleted which was in a related file that
filemaker couldn't find.

Can it actually be used to test for related records? And if so what
are the constraints?

Are you sure, in my experience count returns 0 when there are no
related records with only one exception: that its defined in a calc
field with "do not evaluate if all related fields are empty" (in which
case its blank)

And in that one exception, building a wrapper doesn't work, because
if(count(xx)="";0;1) is STILL going to be blank, because all the
related fields are still empty, so it still won't be evaluated.

Even so a wrapper shouldn't be necessary, because FM treats both 0 and
"" as false.

To prove it - define a calc = (0 or "") - it returns 0 meaning both
sides are false.

best regards,
Dave

Well according to the helpfile:
IsValid function
Format IsValid(field)
Parameter field - any field name
Data type returned number

Description Returns False (0) when:

x A record contains an invalid value because of a data type mismatch (text
in a date field, for example)


x FileMaker Pro cannot locate (temporarily or permanently) the related table
in which the referenced field is defined


x A field has been deleted from a related table, and therefore the
references to that field in the parent table are invalid


Otherwise it returns 1 (the data is valid).

Examples

IsValid(Datefield) returns 0 if there is non-date data in Datefield, for
example if text was imported into it.

IsValid(Amount) returns 0 if there is only text in the number field Amount.

IsValid(table::field) returns 0 if the related table was renamed and the
relationship isn't updated with the new filename.


Yes I am very sure. I have built a testfile. Create two tables with a
relation. Two related records and one not related.

For the related record
cCount < Number ; Count ( Related::rel) > returns 2
cIsempty < Number ; IsEmpty ( Related::rel) > returns 0 (False)
cIsvalid < Number ; IsValid ( Related::rel) > returns 1 (True)

For the not related record
cCount returns nothing (Empty)
cIsempty returns 1 (True)
cIsvalid returns 0 (False)

If you want (and have at least filemaker 7 ) i can send you the file,
although it would be easy enough to recreate.

I would not evaluate empty as "" but use the function IsEmpty

if ( IsEmpty ( count ( Related::rel ) ) ; 0 ; 1 )
which would return a 0 (False) when the relation returnd an empty count. And
although filemaker does evaluate an empty as false, I would prefer to see
something that can be evaluated. a 0 or False. Partly because I have people
who use my databases and sometimes want to change minor things. 0 or 1 I can
explain, but I fear an empty is a step to far. I already hear me explaining:
No an empty is not nothing, zero is nothing. But in this case the zero is a
false and not nothing...... No thanks.

Keep well, Ursus

I would NOT use IsValid() for testing the validity of the relationship.
It does seem to work, but I have heard enough times from FileMaker's
own engineers that this was not its intended purpose and that it should
not be used for this purpose. It's purpose is to test for a valid data
type. I used to always use this method for testing relationships, but I
long ago abandoned it at the urging of the engineers at FMI.

Count() CAN be used but is also NOT recommended. The problem with
Count() is that it has to literally count all related records when all
you really want to know is whether or not there is at least one related
record. If you have a situation where there are, say, 500 related
records, you might get a slight delay if using the count() function
while it counts all 500. With 5000 related records, you can imagine
where your speed might disappear to.

If your goal is to test whether or not there is at least one related
record, then why not just test for one record. I do this by testing
whether there is a value in one related field that I always know has
data in it. Since I always include a serial number (primary key) field
in each of my tables, this is the field I always test on:

case(related::PrimaryKey > 0, "Related record exists")




Ursus wrote:

--
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg (818) 883-2846
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Associate Member, FileMaker Solutions Alliance