Aging Report

[rogerfire]

In filemaker 4.0 this is the formula I used for tracking Days Past Due and it works great, but in 7.0 it works fine after you put in a billed date. But before you do it makes a huge past due number.

If(PAYED ACC< S TOTAL, Today- BILL DATE,0)

In 7.0 it changes the comma's to simi colans and won't let you change them back to coma's

Any help would be great

[Genx]

It changes from , to ; past 6 because thats the new syntax. As for your problem could you make yourself clearer please?

If you didnt wan't a past you due number you could just do:

Case(IsEmpty(BILL DATE) ; "Not Billed" ;

PAYED ACC < S TOTAL ; get(CurrentDate) - getAsDate(BILL DATE); 0)

[rogerfire]

I want to know how many days a bill is past due from the billing date to what ever the curent day is (today) With the orginal formula it would't do anything when the billed date was empty but now when the the billed date is empty it shows the past due days as a huge number.

Thanks for your reply

rogerfire

[LaRetta]

Try:

If ( PAYEDACC < STOTAL and BILLDATE ; Get ( CurrentDate ) - BILLDATE )

And the number calc must be unstored. Dates are numbers so the math will always produce a logical result. By eliminating (short-circuiting) the calculation, it won't continue to the math portion at all...

[LaRetta]

As to why you are getting that huge number ...

Since dates are simply numbers to FM, today's date is 732392; meaning the total number of days since 1/1/0001.

The math would look like: 732392 - (blank if not billed) = 732392 (days past due)

Behavior change on If() ... if no default result is specified, it now assumes NULL - the default result doesn't need to be specified. And If(), Case(), AND etc. all short-circuit (stop evaluating when they hit the first true) so the calc came to your math and performed it and then stopped. It's a wonderful behavior change on calculations, making them much quicker.

By adding the test in the first portion of 'and BILLDATE' it will trap for empty dates. This is a boolean test. If BILLDATE contains ANY date, it's value (to FM, being it's a number) is greater than 0; thus producing true. An empty BILLDATE will fail the boolean test and not evaluate at all.

[rogerfire]

LaRetta

Adding (and Bill Date) did the trick.

Thanks

[Genx]

Hmmmmm,

Does:

PAYEDACC < STOTAL and BILLDATE

mean:

PAYEDACC < STOTAL

and

PAYEDACC < BILLDATE

?

Cheers,

~Genx

[rogerfire]

Genx

This is how the formula ended up.

If(GetAsNumber(PAYED ACC)< GetAsNumber(S TOTAL)and BILL DATE; Today- BILL DATE;0)

rogerfire

Edited March 25, 2006 by Guest

[LaRetta]

mean:

PAYEDACC < STOTAL

and

PAYEDACC < BILLDATE

No. It means PAYED ACC is less than S TOTAL and there is a date (number) in BILL DATE. It is a YES/NO test so always add a question mark [color:blue]not BillDate? or [color:blue]and BillDate? when you read the logic. Create a date field called BillDate and put a date in it. Create a calculation (number). For this test, uncheck 'Do Not Evaluate...' and enter the formula: not BillDate

If blank, the answer is yes (true or 1) then there isn't a date (number). Otherwise there is a date (false or 0). Boolean is the most efficient evaluation possible and understanding the logic is VERY POWERFUL!! The calc (exactly as I wrote it) would have worked perfectly for rogerfire. GetAsNumber() isn't required because FM will evaluate a number (integer) in a text field also. And the ending 0 (the If() default result) isn't needed because the calc STOPS (short-circuits) before it ever gets to the math because it fails the boolean test of 'and BillDate'. Today() should not be used at all (and isn't even in vs. 7/8). Compare the calcs as they evaluate:

[color:blue]If(GetAsNumber(PAYED ACC)< GetAsNumber(S TOTAL)and BILL DATE; Today - BILL DATE;0)

1. Interpret PAYED ACC as number

2. Interpret S TOTAL as number

3. Evaluate if PAYED ACC is less than S TOTAL

4. Evaluate if there is a BILL DATE

... If true, subtract BILL DATE from Today

... If false, produce 0

[color:blue]If ( PAYED ACC < S TOTAL and BILL DATE ; Get(CurrentDate) - BILL DATE )

1. Evaluate if PAYED ACC is less than S TOTAL

2. Evaluate if there is a BILL DATE

... if true, subtract BILL DATE from Today

As your record-number grows, these evaluations will make a HUGE difference in speed of your solution. As you can probably tell, boolean, short-circuiting, and branch prediction are my passions because I've seen how much of a difference this optimization can make. I should clarify that rogerfire's fields should be numbers and date but math and boolean logic will also work in text fields. This is a huge subject so I hope others will forgive me if I didn't cover every possible scenario here ...

[Genx]

Ok, then here is my question isnt the:

..."and BILL DATE" equivilant to the expression "and not IsEmpty(BILL DATE)"?

Or does "and BILL DATE" also check against the field type definition for validity?

Cheers,

~Genx

[LaRetta]

"...and BILL DATE" equivilant to the expression "and not IsEmpty(BILL DATE)?"

It is the same, Genx, except more efficient, quicker to write and easier to read within calculations. As you are finding out, there are many 'roads to Kansas' in FM. Boolean is simply the freeway.

And whenever someone writes Case(IsEmpty(BillDate) ; 1 ; 0 ) , I would like to explain that not BillDate would provide the exact same results. Or Case(Get( CurrentDate ) = CreationDate ; 1 ; 0 ) could be written as Get(CurrentDate) = CreationDate.

[comment]

Well, it is not EXACTLY the same, but with a date field it BEHAVES the same.

Case ( number ; result )

is the same as:

Case ( number ≠ 0 ; result )

The condition evaluates as True when the number is anything except 0 or empty. It is the same with a date, but since Filemaker will not let you enter a date that evaluates as 0, the condition in Case ( date ; result ) can only evaluate as False when the field is empty.

[Genx]

... Then can someone please explain to me the point of the IsEmpty function if simply stating the field name will result in true or false result...?

Hehe, you can answer that if you really want, but thanks for all the explanations... learning is fun! ... sort of

Cheers guys,

~Genx

[Inky Phil]

Well I never.

That explains why I found it difficult to follow some examples in here.

Big ups to comment and LaRetta for explaining that one

Phil

[comment]

the point of the IsEmpty function if simply stating the field name will result in true or false result...?

This trick only works with numbers, or data that can be evaluated as number.

Case ( textfield ; result)

is the same as:

Case ( GetAsNumber ( textfield ) ; result)

If textfield contains "abcd5" the condition will be true.

If textfield contains "abcd" or "abcd0" or is empty - the condition will be false.

[Genx]

Cheers comment.

~Genx