Aging Report

This topic is 6744 days old. Please don't post here. Open a new topic instead.

Recommended Posts

• Newbies

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

Share on other sites

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)

Share on other sites

• Newbies

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.

rogerfire

Share on other sites

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...

Share on other sites

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.

Share on other sites

• Newbies

LaRetta

Adding (and Bill Date) did the trick.

Thanks

Share on other sites

Hmmmmm,

Does:

PAYEDACC < STOTAL and BILLDATE

mean:

PAYEDACC < STOTAL

and

PAYEDACC < BILLDATE

?

Cheers,

~Genx

Share on other sites

• Newbies

Genx

This is how the formula ended up.

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

rogerfire

Edited by Guest
Share on other sites

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 ...

Share on other sites

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

Share on other sites

"...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.

Share on other sites

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.

Share on other sites

... 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

Share on other sites

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

Share on other sites

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.

Cheers comment.

~Genx

Share on other sites

This topic is 6744 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account