Script step for related records

[dvalley]

I need to find a way to look for related records or skip the step if none exist. What I really need is: If Status(CurrentRelatedRecordCount)=0

Go to Record (Next) exit after last... or something like that.

Is there a way around this without performing a find in the related file?

[Razumovsky]

try:

if (is empty(relationship::relatedrecordID))

go to record request next

else

do something else

end if

[dvalley]

Sounds good.

I will let you know.

Thanks

[Ugo DI LUCA]

I had this problem in a script I never found out why it wasn't working.

But I now use the is valid(your relationship) function and it works like a charm.

[daltwin]

Alternatively, use:

If ["Count(TheRelationship::ForeignKey) = 0"]

This is closest to your original request, as it works off a count of related records.

--------------------

Michael

[CobaltSky]

Yes, Like daltwin, I routinely use the formulation:

not Count(Relationship::Key)

to test for an absence of related records.

There are various other methods, including IsEmpty and IsValid, both of which will work for most cases - but each of which can produce unexpected results under certain conditions.

IsValid in particular can produce some unexpected results if it targets any field other than the key field, since it is actually testing for more than just the presence of a record (eg a field may return as invalid even though the related record exists, depending on the data in it - and conversely, a null field will return as valid, though this may not alwways be what is expected or desired...).

[Ugo DI LUCA]

IsValid in particular can produce some unexpected results if it targets any field other than the key field, since it is actually testing for more than just the presence of a record

Thank you for pointing out. I'm really surprised and would like to understand exactly whay you mean.

I use this for almost all my scripts "create a record if none exists"...I'm kind of...scared now that you pointed this.

With all these solutions leading to the "quite same result, is it that there are different use according to the type of relationships one to one, one to many, many to many ?

Thank you.

[CobaltSky]

Hi Ugo,

I didn't mean to alarm you. If you are working with text keys and text targets for IsValid, then there should be no problem - though if you are not targetting the key field with your IsValid( ) formula, then you need to bear in mind that you are testing for the presence of the record, rather than for a value in the field - whereas the reverse is true for IsEmpty( ).

It is primarily where you may be working with other field types (eg date or time) that IsValid can produce a negative result for more than one reason, and the risk becomes greater if it is not targetting the key field for the relationship it is testing.

If the field type is text and you are targetting the key field, then there is no reason for concern. However when suggesting a method on the forum I figure that the safest and/or most universally applicable is best. Especially when it is not clear exactly how the idea will be applied by the recipient or others.

[Razumovsky]

I am not quite sure I agree. The initial request was to

look for related records or skip the step if none exist.

Is it more accurate to count the related records and see if they are above 0, or to look for the presence of any relationship? It would seem to me that in theory, the count and evaluate involves 2 steps, while the is empty(foriegn key) involves one and is closer to the original request (not looking for how many, but the presence of).

I realize that this is splitting hairs, and do appreciate the potential flaws in the IsValid method, but I cannot think of any scenario where the IsEmpty(ForiegnKey) will return a different result than Count(ForiegnKey)=0.

I use IsEmpty for all such calcs (it is just slightly easier to program for me), and would be very interested to learn if I have been failing to consider something.

And again, IMHO IsEmpty seems conceptually closer to the desired request.

-Raz

[CobaltSky]

Hi Raz,

We aren't in disagreement - my only reservation about IsEmpty( ) relates to cases I have seen where it has been used on a field other than the foreign key - and that can lead to problems, as you'd appreciate.

In fact, Count( ) must also be used on the Foreign Key in order to reliably test the relationship so, as you say, they are close to functional equivalence.

If efficiency were at issue, there may be an argument for using If ["Count(Relationship::ForeignKey)"] as a positive test (ie to confirm that there are related records) and If ["IsEmpty(Relationship::ForeignKey)"] as the negative equivalent. They are certainly both compact.

[Razumovsky]

Thanks Ray,

I spent so much time learning FM in a vacuum (before I discovered this board) that I am not always sure IsValid(MyConvictions) returns true (much better than IsEmpty(MyConvictions)) . Nice to know I dont have to worry about going back and updating all my calcs of this nature.

-Raz