1 2 Previous Next 17 Replies Latest reply on Jan 19, 2010 1:10 PM by comment_1

# Need some help with how to set this up.

### Title

Need some help with how to set this up.

### Post

Hi,

My supervior ask me to set up a template to do some financial recording for our school. There is only one frield that I cant figure how to make it work.

I need a field that can calculate the perivous month's usage and add to this month's total.

Lets say on December 2009, we got \$2100 for the catergory , Senior Trip,  and this month we got \$3200 for Senior Trip. I will need to add up both months and show a tota.

December 2009 Senior Trip will be one record, and January 2010 Senior Trip will be another record.

The thing I am confuse about is, how to make a field that will find the related Catergory and get the total or the difference between the two month.

Also, for the report layout, it is possible for one column to show December 2009 and the Next showing January 2010?

• ###### 1. Re: Need some help with how to set this up.

Hi,

A bit difficult to understand but ...you say:

"December 2009 Senior Trip will be one record, and January 2010 Senior Trip will be another record.

The thing I am confuse about is, how to make a field that will find the related Catergory and get the total or the difference between the two month."

In Summary is option "Running total". Place this Summary field on each record and find: Difference = Running total - Amount on record.

Or I haven't understand question correctly?

• ###### 2. Re: Need some help with how to set this up.

Okay, I think if I understand this correctly, The "Difference" will give me a Grand calculation. This part I understand what to do but not what I want to show on the print out report or the regular layout.

Lets say December 2009 I got some money for Senior Trip. So the amount for Senior trip is \$2100.

So on January 2010 I got some money for Senior Trip again and the amount for January 2010, is \$3100.

So on this layout, I have a field for Month/Date (January 2010), the Catergory (Senior Trip), the money I collect for this month (\$3100) and I also need to do a caculation using the pervious month's money, Which will be \$2100 + \$3100.

The reason why I only need the pervious month and not just the grand total is, lets say I want to check November 2009, I want to see  the money I collected on the same Catergory on October 2009 and the total of October 2009 and November 2009. and that is the part I am confuse of.

Is there anyway to get the field on a record and calculate? Can I use a find script to do that?

• ###### 3. Re: Need some help with how to set this up.

The easy way to do this (if I understand correctly) is to find the records in this and previous month, and produce a report summarized by Category, then Month:

Category: Senior Trip

• December 2009: \$2,100

• January 2010: \$3,100

Total in Category: \$5,200

No calculations are required for this - only one summary field (total of amount).

• ###### 4. Re: Need some help with how to set this up.

As you work with this report, you may find it useful to explore the various options for creating summary reports.

Here's a link to a simple tutorial on setting up summary reports that you may find useful:

Creating Filemaker Pro summary reports--Tutorial

• ###### 5. Re: Need some help with how to set this up.

I will use self-join relationship.

So calculated (Month - 1) (table1) is relationship field to Month (related table 2). Return values from table 2 (witch is the same table 1 but "delayed" for 1 month) to table1 layout.

• ###### 6. Re: Need some help with how to set this up.
so you mean i have to create a relation within the table? How can you do that? Sorry for this many question. I am user of filemaker but first time have to built this kind of data base
• ###### 7. Re: Need some help with how to set this up.
also, lets say my date format is 1/11/2010,   how can I search it by the month? Thanks alot
• ###### 8. Re: Need some help with how to set this up.
Found how to find just the month.. lol
• ###### 9. Re: Need some help with how to set this up.

Okay, I got the summary part, but here is another problem.

If I want to generate a report that calculate the total of the amount with in same category and month how can I do that?

Lets say on January 2010, all the Senior Trip money collected, and all the Graduation money collected?

Can I do the summary by category? I need to put the Senior trip and the graduation on the same report layout.

As far as I understand summary field  sum up all the field and it does not care about the category right?

• ###### 10. Re: Need some help with how to set this up.

dyang wrote:

As far as I understand summary field  sum up all the field and it does not care about the category right?

No. If you put a summary field in a subsummary part, it will return the corresponding subsummary value.

• ###### 11. Re: Need some help with how to set this up.

I think I am getting there with all this help...

but I still need a little help here,

I know that if I create a new layout/report using the coulmner list/report,

I will show all the record.

Which will show in a format as follow:

Month:January

Date                Type                         Money Collected

1/12/2010      Seinor Trip                        150

1/13/2010      Seinor Trip                        260

but this is what I really want to show

Month: January

Type                           Total

Senior Trip                   410

• ###### 12. Re: Need some help with how to set this up.

Your layout needs to have two subsummary parts and no body part:

Month: January subsummary by month, leading

Senior Trip                   410

• ###### 13. Re: Need some help with how to set this up.

I did what you told but some how it is showing only one record.

Is there any option that I can turn on to show all the Record or all the category?

Thanks

• ###### 14. Re: Need some help with how to set this up.

So here - in this forum  - cannot be added sample files.

So here cannot be shown any links.

How to help to fellows?!!!

Thats why I use fmforums com in general - normal place, normal forum or post on my portal www dot hierarchy dot lv.

Take a look there.

1 2 Previous Next