calculation

On Dec 23, 12:26 pm, Hans Besjes wrote:
> On 12/23/2008 11:52:10 "Ursus" wrote:
>
> > FMP 9adv winXP
> > I have create the following calculation to count the amount of related
> > records.  But when there are none it returns "0 records" (being the last
> > CASE ) instead of "No records" (Being the first CASE) .  Why?
> > Case ( IsEmpty ( Count ( Table2::RelatedID ) ) ; "No records" ; Count (
> > Table2::RelatedID) = 1 ; "1 record" ; GetAsText ( Count (
> > Table2::RelatedID ) ) & " records" )
>
> When there are no related records, there is no record/field to test for IsEmpty, so this will indeed not work.
>

This is not how IsEmpty() works.
 >> Stay informed about: calculation 

On 12/23/2008 11:52:10 "Ursus" wrote:

> FMP 9adv winXP

> I have create the following calculation to count the amount of related
> records. But when there are none it returns "0 records" (being the last
> CASE ) instead of "No records" (Being the first CASE) . Why?

> Case ( IsEmpty ( Count ( Table2::RelatedID ) ) ; "No records" ; Count (
> Table2::RelatedID) = 1 ; "1 record" ; GetAsText ( Count (
> Table2::RelatedID ) ) & " records" )

When there are no related records, there is no record/field to test for IsEmpty, so this will indeed not work.

> Oh yeah, I tried

> Case ( Count ( Table2::RelatedID ) = 0 ; "No records" ; Count (
> Table2::RelatedID) = 1 ; "1 record" ; GetAsText ( Count (
> Table2::RelatedID ) ) & " records" )

> Which doesn't work at all.

You may want to try a little more structured approach...

First create a calculated field

Counter = Count ( Table2::RelatedID )

and make sure you uncheck the option "Do not evaluate if all referenced fields are empty". This will ensure that the calculation will evaluate (to 0) even if there are no related records.

Then created a second calculation field

CounterText = Case (
Counter = 0 ; "No records" ;
Counter = 1 ; "1 record" ;
GetAsText ( Counter ) & " records" )

This shouls get you what you want...

Hans Besjes
 >> Stay informed about: calculation 

On 2008-12-23 02:52:10 -0800, "Ursus" said:

> FMP 9adv winXP
>
> I have create the following calculation to count the amount of related
> records. But when there are none it returns "0 records" (being the last
> CASE ) instead of "No records" (Being the first CASE) . Why?
>
> Case (
> IsEmpty ( Count ( Table2::RelatedID ) ) ; "No records" ;
> Count ( Table2::RelatedID) = 1 ; "1 record" ;
> GetAsText ( Count ( Table2::RelatedID ) ) & " records"
> )
>
>
> Oh yeah, I tried
>
>
> Case (
> Count ( Table2::RelatedID ) = 0 ; "No records" ;
> Count ( Table2::RelatedID) = 1 ; "1 record" ;
> GetAsText ( Count ( Table2::RelatedID ) ) & " records"
> )
>
> Which doesn't work at all.

Try this

Case ( Count ( Table2::RelatedID ) = 1 ; "1 Record" ; Count (
Table2::RelatedID ) > 1 ; GetAsText ( Count ( Table2::RelatedID ) ) & "
records" ; "No Records" )

best
Lou
 >> Stay informed about: calculation 

On 2008-12-23 02:52:10 -0800, "Ursus" said:

> Case (
> IsEmpty ( Count ( Table2::RelatedID ) ) ; "No records" ;
> Count ( Table2::RelatedID) = 1 ; "1 record" ;
> GetAsText ( Count ( Table2::RelatedID ) ) & " records"
> )

As a matter of policy, don't use Count to test for existing related records.

If the condition is True, if there *are* existing records, FM will go
ahead and actually count all the records. Makes sense, eh? If you have
100,000 records, that could take a bit. If you're creating a record
with this calc in one of its fields, you're going to get a LOOOONG
delay to wait for it to resolve during record creation.

Instead, use Isempty(Relationship::KeyField) to test for no existing records.

This merely tests that ONE related record exists. Very fast.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA
 >> Stay informed about: calculation 

Simple, Thank you grip

Keep well, Ursus

"Grip" schreef in bericht

On Dec 23, 3:52 am, "Ursus" wrote:
> FMP 9adv winXP
>
> I have create the following calculation to count the amount of related
> records. But when there are none it returns "0 records" (being the last
> CASE ) instead of "No records" (Being the first CASE) . Why?
>
> Case (
> IsEmpty ( Count ( Table2::RelatedID ) ) ; "No records" ;
> Count ( Table2::RelatedID) = 1 ; "1 record" ;
> GetAsText ( Count ( Table2::RelatedID ) ) & " records"
> )
>

>
> Ursus

Because IsEmpty(0) returns false. 0 and null are evaluated
differently. So try,

Case( IsEmpty( Table2::RelatedID ); "No records";

as your first condition.


G
 >> Stay informed about: calculation 

On 12/24/2008 21:04:38 Lynn Allen wrote:

> Case ( IsEmpty ( Count ( Table2::RelatedID ) ) ; "No records" ; Count (

>> Table2::RelatedID) = 1 ; "1 record" ; GetAsText ( Count (
>> Table2::RelatedID ) ) & " records" )

> As a matter of policy, don't use Count to test for existing related
> records.

> If the condition is True, if there *are* existing records, FM will go
> ahead and actually count all the records. Makes sense, eh? If you have
> 100,000 records, that could take a bit. If you're creating a record with
> this calc in one of its fields, you're going to get a LOOOONG delay to
> wait for it to resolve during record creation.

> Instead, use Isempty(Relationship::KeyField) to test for no existing
> records.

> This merely tests that ONE related record exists. Very fast.

Hi Lynn,

Nice tip I'll be sure to keep in mind

But since the original question was about reporting the number of related records, I don't think this approach gets the needed answers...

Hans Besjes
 >> Stay informed about: calculation 

On Dec 24, 3:35 pm, Hans Besjes wrote:
> On 12/24/2008 21:04:38 Lynn Allen wrote:
>
> > Case ( IsEmpty ( Count ( Table2::RelatedID ) ) ; "No records" ; Count (
> >> Table2::RelatedID) = 1 ; "1 record" ; GetAsText ( Count (
> >> Table2::RelatedID ) ) & " records" )
> > As a matter of policy, don't use Count to test for existing related
> > records.
> > If the condition is True, if there *are* existing records, FM will go
> > ahead and actually count all the records.  Makes sense, eh?  If you have
> > 100,000 records, that could take a bit.  If you're creating a record with
> > this calc in one of its fields, you're going to get a LOOOONG delay to
> > wait for it to resolve during record creation.
> > Instead, use Isempty(Relationship::KeyField) to test for no existing
> > records.
> > This merely tests that ONE related record exists.  Very fast.
>
> Hi Lynn,
>
> Nice tip I'll be sure to keep in mind  
>
> But since the original question was about reporting the number of related records, I don't think this approach gets the needed answers...
>
> Hans Besjes

This leads to an interesting question...Does a Case() statement that
references the same Count() of related records three times actually
count those records three times or just once and then store that
result in cache?

If FMP counts multiple times and if there are 100,000 records then the
above statements will be pretty inefficient and a Let() statement is
worthwhile.


Let([

exists = not IsEmpty(Table2::RelatedID);

num = If(exists ; Count(Table2::RelatedID); 0);

s = If( num = 1; ""; "s");

result = num & " Record" & s

];

result

)
 >> Stay informed about: calculation 

On 2008-12-24 19:05:29 -0800, Grip said:

> This leads to an interesting question...Does a Case() statement that
> references the same Count() of related records three times actually
> count those records three times or just once and then store that
> result in cache?

According to FMI engineers, although a Case statement returns the first
true result, it DOES go ahead and evaluate all arguments right through
to the end. It does not stop processing.

I don't know about caching results of a test for later use. But given
the above, I suspect not.

It might be fruitful to do performance testing on the Let statement vs.
the Case.
--
Lynn Allen
--
www.semiotics.com
Member Filemaker Business Alliance
Long Beach, CA
 >> Stay informed about: calculation 

On Dec 24, 10:21 pm, Lynn Allen wrote:
> On 2008-12-24 19:05:29 -0800, Grip said:
>
> > This leads to an interesting question...Does a Case() statement that
> > references the same Count() of related records three times actually
> > count those records three times or just once and then store that
> > result in cache?
>
> According to FMI engineers, although a Case statement returns the first
> true result, it DOES go ahead and evaluate all arguments right through
> to the end.  It does not stop processing.
>
> I don't know about caching results of a test for later use. But given
> the above, I suspect not.
>
> It might be fruitful to do performance testing on the Let statement vs.
> the Case.
> -

After a little testing I will say:

1) Case() statements stop evaluating after the first true condition.
That was easy enough, I built a recursive custom function with no exit
condition. Case(1;1;Looping()) returns 1 immediately. Case
(0;1;Looping()) processes for a few seconds and then returns an out of
error message.

2) Identical expressions in a calc do not cache.
I made a very long Case() statement using ValueCount(List
(relatedfield)) on a large set of related records and another Case()
statement using a local variable in that same expression instead of
the List(). The first took a lot longer to evaluate.
 >> Stay informed about: calculation 

On Dec 25, 6:50 am, Hans Besjes wrote:
> On 12/25/2008 07:31:51 Grip wrote:
>
>
>
> > On Dec 24, 10:21 pm, Lynn Allen wrote:
> >> On 2008-12-24 19:05:29 -0800, Grip said:
> >>> This leads to an interesting question...Does a Case() statement that
> >>> references the same Count() of related records three times actually
> >>> count those records three times or just once and then store that result
> >>> in cache?
> >> According to FMI engineers, although a Case statement returns the first
> >> true result, it DOES go ahead and evaluate all arguments right through to
> >> the end.   It does not stop processing.
> >> I don't know about caching results of a test for later use.  But given
> >> the above, I suspect not.
> >> It might be fruitful to do performance testing on the Let statement vs..
> >> the Case. -
> > After a little testing I will say:
> > 1) Case() statements stop evaluating after the first true condition. That
> > was easy enough, I built a recursive custom function with no exit
> > condition.  Case(1;1;Looping()) returns 1 immediately.  Case
> > (0;1;Looping()) processes for a few seconds and then returns an out of
> > error message.
> > 2) Identical expressions in a calc do not cache. I made a very long Case()
> > statement using ValueCount(List (relatedfield)) on a large set of related
> > records and another Case() statement using a local variable in that same
> > expression instead of the List().  The first took a lot longer to
> > evaluate.
>
> That is why I, based both on structure and performance considerations, proposed my posted approach.
>
> First do the count, and then use that result for further calculations. That will guarantee that the count function will only be evaluated once...
>
Even better would to not do the count at all if there's no related
records.
 >> Stay informed about: calculation