maiton.vang Posted April 14, 2007 Share Posted April 14, 2007 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")))) Link to comment Share on other sites More sharing options...
mr_vodka Posted April 14, 2007 Share Posted April 14, 2007 Take a look here. Thread on payment status description Link to comment Share on other sites More sharing options...
maiton.vang Posted April 14, 2007 Author Share Posted April 14, 2007 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 Link to comment Share on other sites More sharing options...
mr_vodka Posted April 14, 2007 Share Posted April 14, 2007 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? Link to comment Share on other sites More sharing options...
maiton.vang Posted April 14, 2007 Author Share Posted April 14, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
mr_vodka Posted April 14, 2007 Share Posted April 14, 2007 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 Link to comment Share on other sites More sharing options...
maiton.vang Posted April 25, 2007 Author Share Posted April 25, 2007 how do you write Get (CurrentDate) in Filemaker 6.0 Link to comment Share on other sites More sharing options...
Genx Posted April 25, 2007 Share Posted April 25, 2007 Status(CurrentDate) or possibly Today Link to comment Share on other sites More sharing options...
maiton.vang Posted April 25, 2007 Author Share Posted April 25, 2007 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? Link to comment Share on other sites More sharing options...
Genx Posted April 25, 2007 Share Posted April 25, 2007 Just put the field name in instead... Link to comment Share on other sites More sharing options...
maiton.vang Posted April 25, 2007 Author Share Posted April 25, 2007 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? Link to comment Share on other sites More sharing options...
Genx Posted April 25, 2007 Share Posted April 25, 2007 Well... why not just use this: Case( Statement Date <= DateInvoiced + 60 and Statement Date > Date Invoiced + 30 , Sum (Grand Total 2007) ) Link to comment Share on other sites More sharing options...
maiton.vang Posted April 25, 2007 Author Share Posted April 25, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
David Jondreau Posted April 25, 2007 Share Posted April 25, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
maiton.vang Posted April 25, 2007 Author Share Posted April 25, 2007 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 Link to comment Share on other sites More sharing options...
maiton.vang Posted April 25, 2007 Author Share Posted April 25, 2007 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6203 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 accountSign in
Already have an account? Sign in here.
Sign In Now