Newbies ridddder Posted October 12, 2007 Newbies Share Posted October 12, 2007 I am making a tool, for our sales manager to track sales, and contacts. I can't find my filemaker reference guide, and have searched the forum, and can't find what I need. Basically, I want a calculation that will add up a total of all records based on the month the record was added. For example, each record tracks the total number of contacts each salesperson has daily. Each record has an auto-stamp of what date the record was input. In the managers' layout, I want a field to show a running total based on the month. So the total contacts for each record of May are added together, but not the total contacts for June, and so on. I can't figure out how to do it, thanks in advance. Am making the database on a Mac, then porting it to Windows. Have both versions, Mac, & Windows of Filemaker Pro 5.5 Link to comment Share on other sites More sharing options...
Newbies ridddder Posted October 15, 2007 Author Newbies Share Posted October 15, 2007 Wow, 41 views no replies....guess I stumped everyone on this one, I have seen much more advanced uses of filemaker from invoicing, to pos systems. Hmmmm, makes me wonder. I will check back later, am having issues with one of my websites, and php nuke. I hope someone can help me, and thanks in advance if you can ~Doug Link to comment Share on other sites More sharing options...
Genx Posted October 15, 2007 Share Posted October 15, 2007 1) Create a stored calculation field called yearMonth 2) Calc = Month(CreationTSField) & "/" & Year(CreationTSField) 3) Create a new table occurance based on this same table. 4) Relate your primary TO with the new one via our yearMonth field. 5) Create a new calculation field called totalForMonth. 6) Calc = Count(OurNewTO::CreationTSField) 7) To be evaluated based on the initial TO. If i misinterpreted anything (which i may have) please clarify. Hope that helps. Link to comment Share on other sites More sharing options...
Vaughan Posted October 15, 2007 Share Posted October 15, 2007 "2) Calc = Month(CreationTSField) & "/" & Year(CreationTSField)" If you reverse the calc and make it year/month it'll sort better. ;) Link to comment Share on other sites More sharing options...
Genx Posted October 16, 2007 Share Posted October 16, 2007 So it would. Probably be truer (that word just looks wrong) to its name as well ;) Link to comment Share on other sites More sharing options...
Chris C Posted October 16, 2007 Share Posted October 16, 2007 I was actually just about to post a similar question... how would you add up a the total of a specific field in a certain record for a certain month? Say every record has a number and a date... how would i get the sum of all the numbers for the month? I believe count only counts the number of records (right?)... Thanks Link to comment Share on other sites More sharing options...
LaRetta Posted October 16, 2007 Share Posted October 16, 2007 Truly, I believe it works better to keep it a true date as referenced here Then all you need to do is create a new layout - type, Columnar List with grouped data including subtotals (based upon this new date field). It will become a regular workhorse throughout your solutions. You would perform a find for the period of time you wish. You would have a report with this new date calculation in leading part. You would have summary field which is Total of 'that number' and place it in the same part as the date calculation. You would sort by this new date calc and go to previow mode. Link to comment Share on other sites More sharing options...
Genx Posted October 16, 2007 Share Posted October 16, 2007 Assuming you wanted to produce a report... But on the other hand I will agree that an actual date field would come in handy in general, in which case change the calc in step 2 to: Date( Month(CreationTSField) ;1; Year(CreationTSField)) ...and set the result type to date. I believe count only counts the number of records (right?)... ... right, but we are counting related records. In this case the related records are those with the same month / and year. Link to comment Share on other sites More sharing options...
LaRetta Posted October 16, 2007 Share Posted October 16, 2007 Say every record has a number and a date... how would [color:blue]i get the sum of all the numbers for the month? Count wouldn't work here - you are right, Alex, it would count the related records or count those with a value in that number field. It appears to be a request to total the number in all those related records. But I could be wrong, I frequently am. Link to comment Share on other sites More sharing options...
Genx Posted October 16, 2007 Share Posted October 16, 2007 Well, I'm right for the first guy (i think) Basically, I want a calculation that will add up a total of all records based on the month the record was added. ... but not for the second guy... (sorry) how would you add up a the total of a specific field in a certain record for a certain month? Instead of counting, we use Sum(OurNewTO::SalesTotals) Link to comment Share on other sites More sharing options...
LaRetta Posted October 16, 2007 Share Posted October 16, 2007 Yeah, it's difficult when someone else jumps onto a thread; it's easy to get confused on what kind of solution we're providing to which one. BTW, just for additional clarification, your suggestion of: "... we use Sum(OurNewTO::SalesTotals )" would apply if we are using a relationship. One would use a summary field, Total Of "that number" if creating a report. Both approaches have their great points! Link to comment Share on other sites More sharing options...
Genx Posted October 16, 2007 Share Posted October 16, 2007 Yeh, we should probably clarify that we are in fact discussing two different approaches as it may not be clear to anyone who jumps in. The first uses relationships to produce "live" totals visible in browse mode. The second uses summary fields to produce totals using sorting and preview mode (useful for reporting). Link to comment Share on other sites More sharing options...
Ugo DI LUCA Posted October 16, 2007 Share Posted October 16, 2007 The problem still is that the first guy was having php issues while running a fm5 version, so Table Occurrence would not have really solved his issue Just kidding, true I was confused too about the two requests : Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 6128 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