Writing IF Statement for Date

[maiton.vang]

I need help writing a calculation on how to get the summary of

"CURRENT", "OVER 30 DAYS", "OVER 60 DAYS", "OVER 90 DAYS", OVER 120 DAYS", and "TOTAL DUE". This is actually for a Statement i want to mail out to my customer every month.

This is what I came up with but it does not work:

If (Statement Today Date - Invoice Date ≤ 30; "Current"; If (Statement Today Date - Invoice Date ≤ 60; "Over 30 Days";If (Statement Today Date - Invoice Date ≤ 90; "Over 60 Days";If (Statement Today Date - Invoice Date ≤ 120; "Over 90 Days"; "Over 120 Days"))))

[mr_vodka]

Take a look here.

Thread on payment status description

[maiton.vang]

i figure out a formula that works but now the only thing is i need to break down the summary amount by current, over 30 days.....

this is the equation i got to work to show which invoice is current, over 30 days, over 60 days...

Case(Statement Today Date <= (Invoice Date + 30); "Current"; Statement Today Date <= (Invoice Date + 60); "Over 30 days"; Statement Today Date <= (Invoice Date + 90); "Over 60 Days";"Over 90 Days")

please help i need to get the invoice amount on the right column

[mr_vodka]

I apologize, but I do not follow what you are trying to achieve. Are you trying to create a report displays all the records that have balances grouped by their status categories?

[maiton.vang]

Yes...for example there are 5 total invoices.

1. invoice A - $5.00 - current

2. Invoice B - $3.00 - current

3. Invoice C - $5.00 - over 30 days

4. Invoice D - $5.00 - over 30 days

5. Invoice E - $2.00 - over 60 days

my statement should indicate

current - $8.00

over 30 days - $10

over 60 days - $2.00

TOTAL DUE - $20.00

Edited April 14, 2007 by Guest

[mr_vodka]

What you want is a summary report grouped by the status field in conjunction with a summary field.

You want to take a quick look at this thread

summary report thread

[maiton.vang]

how do you write

Get (CurrentDate) in Filemaker 6.0

[Genx]

Status(CurrentDate) or possibly Today

[maiton.vang]

thank you it work

another quick question

instead of using today date equation

Get (CurrentDate)

I would like to use a different date which i manually will add to the field what equation can i write to indicate to get the date i put in rather than todays date?

[Genx]

Just put the field name in instead...

[maiton.vang]

I have this equation in FM8 and would like to also have it in FM6 but it is not working:

FM8

Case(Statement Today Date <= (Invoice Date + 60);Sum ( Invoice Total )) - Case(Statement Today Date <= (Invoice Date + 30);Sum ( Invoice Total ))

FM6

Case(Statement Date ≤ Date Invoiced + 60, Sum( Grand Total 2007 )) - Case(Statement Date ≤ Date Invoiced + 30, Sum( Grand Total 2007 ))

Any suggestion?

[Genx]

Well... why not just use this:

Case( Statement Date <= DateInvoiced + 60 and Statement Date > Date Invoiced + 30 , Sum (Grand Total 2007) )

[maiton.vang]

Thank you for the reply...my Statement Date type field is a Date and the option is Indexed. why is the equation not working unless i change the field type to calculation which is Get (CurrentDate) ? sometime i can't get to the statement and i need the statement to be calculated from the date 04/15/07 instead of today date which is 04/24/07 because that will change my "over 30 days" "oer 60 days" etc. this is for FM8 and FM6

Edited April 25, 2007 by Guest

[David Jondreau]

1) A Date field is a field you enter dates into. Filemaker has a convenient option to automatically enter information based on other fields. That is how you've defined your field. It won't update correctly because it has no way of knowing that Today has changed. Today (or Get(CurrentDate)) won't change a Date (or Text, Number, etc) field with an auto-enter because FM doesn't 'check in' to see if the date has changed. You need a calculation, set to Unstored which will update upon many conditions.

2) Instead of using the expression Today, you can define an additional date field and set it to Global Storage. Then replace "Today" with the name of the field in the calculation. You can enter any date you want into that global field.

DJ

Edited April 25, 2007 by Guest

[maiton.vang]

help my caculation is not working

I have 3 fields to calcucate the total current or past due balance.

1. Field Name: Statement Current Type:Calculation

Case(Statement Today Date ≥ (Invoice Date + 30); "Current")

2. Field Name: Statement Current Total Invoice Type: Calculation

Case(Statement Today Date ≥ (Invoice Date + 30); Average ( Invoice Total ))

3. Field Name: Statement Current Summary Type: Summary

=Total of Statement Current Total Invoice

4. Filed Name: Statement over 30 days Type: Calculation

Case(Statement Today Date ≥ (Invoice Date + 30); "Current"; Statement Today Date ≥ (Invoice Date + 60); "Over 30 days"; Statement Today Date ≥ (Invoice Date + 90); "Over 60 Days";"Over 90 Days")

5. Filed Name: Statement over 30 days Total InvoiceType: Calculation

Case(Statement Today Date ≥ (Invoice Date + 60);Sum ( Invoice Total )) - Case(Statement Today Date ≥ (Invoice Date + 30);Sum ( Invoice Total ))

6. 3. Field Name: Statement Over 30 days Summary Type: Summary

=Total of Statement over 30 days Total Invoice

any suggestion why its not working and why all my total is in the current field when some open invoices should be over 30 days late

[maiton.vang]

I have this equation in FM8 and would like to also have it in FM6 but it is not working:

FM8

Case(Statement Today Date <= (Invoice Date + 60);Sum ( Invoice Total )) - Case(Statement Today Date <= (Invoice Date + 30);Sum ( Invoice Total ))

FM6

Case( Statement Date <= DateInvoiced + 60 and Statement Date > Date Invoiced + 30 , Sum (Grand Total 2007) )

any suggestion???

Should I try "IF Statement??"

HELP.....

Edited April 25, 2007 by Guest