Total Months To Payments

[Charles Hoens]

I have a table of payments, showing the date and amount of each payment. I then created a new table called Recap where I need to total the payments from the payments table month by month. In other words, I need the total of payments for each month displayed and/or calculated into the Recap table for display. A side question to this is whether this calculation should be done inside the Recap table with a calculated field or as a calculated field on the browse itself.

Many thanks!

Charlie

[madtowng]

I'm going to assume you're using dbf tables, which suggests you should use a summary operation to group the payments by month.
Your task would be much easier if you were using some form of sql.

[Ted Giles]

Unless you are developing for users on a Web network, I'd give SQL a miss, Charlie.
It's great at what it does but more complicated to distribute .
If you are going Web, it's really the best option. Otherwise too many moving parts.

[Charles Hoens]

Well, I'm doing this desktop for now and I'm not using SQL. Any coding ideas, such as using DBSUM with a filter?

[Ted Giles]

Try this Charlie.
DBSUM is not as flexable as Summarise. The 3 dots to the right of the field you want to use can set the value to just the MONTH part of the Date field.

Open the table or the form, add a value and date, then go to Operations and run MySumm.
Unpick the operation to see how it works - just Edit it.

Summary.zip

[madtowng]

dbsumm() would be great if you didn't want to group the results by what I am guessing is month and year instead of just month(there's a difference)
After you create your summarize operation, it is easy enough to run programmatically.
I would suggest you give it a try. If it fails to work as desired, show what you're doing, then let us try to get you the right answer.

[Charles Hoens]

Thanks Ted. Late last night I was working on the same type of solution. I was also trying Tablesum(), but I was having trouble with the syntax. The advantage of using that would be to allow the table to update immediately upon a change, however I could put the recap of the records into a different form and then run the summarize on the loading of that form. Thanks as always for going "above and beyond" with your zip file.

[Ted Giles]

The structure of the TableSum is a bit convoluted.
tablesum("funds","Id=1.AND.Reconciled=.T.","Amount")

This is an example, where;

funds is the table
Id is the PK I am interested in
Reconciled is a flag on the table
Amount is the numeric value of the expense.

You can do it this way, but each line or item of interest needs to be coded as a Calculated Field to make any sense of it I found.
In your case you would probably need months 1 through 12.

In my Q&D example, I didn't look at crossing over a year boundry, so if yoy need that, the operation might be different.

[Charles Hoens]

Thanks Ted. It's that damn filter that gets me every time. I always have to look up the syntax from Tom Cone's old post. Just never made sense to me!