Finding out if a record has any related records

[laker_42]

I have a server database and a server change log database. I have created a button in the server database that users can click on to take them to the related log entries in the server change log database. Simple, no problem. What I would like to do is if there are no related records present in the log database for a server, I want a message to show saying there are no entries for that server and ask if they would like to create a log entry for it. I can't seem to capture whether there are related records present or not. I tried using the status function: status(RecordCount) or Status(CurrentFoundCount) but neither would do what I wanted. Is there another status function that I should be using? Is this even possible? If anyone could give me an idea, I would appreciate it!

Thanks!

John

[John Caballero]

Hi John,

What you're looking for is a count of related records. Put this in your script before you transfer control to the related file:

If

Count(relationship::relatedID)=0

Show Message (No records found, continue? buttons=cancel, yes)

If

Status(CurrentMessageChoice)=1

Halt Script

End IF

Call script to create new related record

Halt Script

End IF

Your scripts follow here...

[laker_42]

That did the trick. Thanks John!

[bruceR]

IsEmpty() has been optimized for this purpose and is faster, according to FileMaker reps.

If isEmpty(relationship::relatedID)

Show Message (No records found, continue? buttons=cancel, yes)

etc.

[Jim McKee]

Bruce ...

Is the 'IsValid' function optimized as well? It's the one I use most frequently, as in:

IsValid(relationship::constant)

[Fenton]

I believe I can answer for Bruce, as the FileMaker rep was undoubtedly Jimmy Jones, who has stated this more than once; and, if memory serves, I was the one who was first reprimanded for using "IsValid."

IsValid() is not intended to determine if a related record is present, and has been stated as such by Dave McKee, FMI development engineer.

This is my understanding of the purpose of the IsValid function:

IsValid is not meant to replace the usage of "Count( relation::field) > 0" to determine if related records exist.

It's main purpose is to return 0 when it:

- detects that a field referenced has subsequently been deleted (ie the field def doesn't exist at all),

- or is generally missing because of some reason ( ie file was not found and user cancelled finding the file).

- Or, there is nothing but text in a date field. (Ie a particular record-instance of the date field has an invalid value). [almost forgot this case, found it in the online help]

In _any_ other case, it is supposed to return 1.

This means, technically, if there is no related record, it could return 1, because the criteria above was not met.

It just so happens that it returns 0 when there is no related record, because the field referenced has no value (ie NULL) or alternatively it is as if the field is missing. This is only a side effect of the relational behavior of FileMaker, and this side effect could change at any time.

For this reason alone, I would never use IsValid to detect the presence of related records. Not because it doesn't work (it appears to do so, at least most of the time as this thread suggests) but because this is not the intended purpose of this function, and there is a different function whose purpose DOES include detecting related records.

The fact that IsValid may inconsistently return 1 or 0 for the presence of related records is just an added reason not to use it; so technically it is not a bug.

He later refined the step to "not IsEmpty(Relationship::Constant)," saying it was optimized for this purpose and the fastest way to check for related records, as Bruce said.

[Fenton]

Jim, I didn't mean to sound alarming. I remember being a bit taken aback, as were a few others, when Jimmy said this. At least one experienced developer said that he'd been using IsValid for checking relationships for years, and never had a failure.

I believe Jimmy then admitted that it appeared to work, but cautioned that it was not designed to do that, so could not be guaranteed to continue to do so in future versions.

And no, I didn't go back and retrofit all my files. But I changed it whenever I saw it in an old file, and have always use IsEmpty since.

[Jim McKee]

Fenton ...

Thanks man. I think I had heard that once before, but, like most people, I've never had a failure, so I kept using it. This probably blows my cover as a deeply committed non-alarmist.

It'll be a pain to retrofit a bunch-o-apps if FMI decides to change the way this works in v.7. I can only keep my fingers crossed, as no doubt a lot of other developers are.

I like the sound of "optimized" in reference to the IsEmpty function, so I'll be using that from now on.

[Lee Smith]

For anyone interested in reading the whole thread, do a search of Blueworld for the subject of:

Automated portal record updates??? and the date of 3/22/02.

The archive for Blueworld is:

http://www.listsearch.com/fmprotalk.lasso

Lee

BTW: You might have to remove the : before doing the search