Report scripting help

[JimmyLewis]

Hi,

I am running a db of my finances with three tables (Banking Transactions, Receipt Details, and Categories).

Now that i have the data categorized, i am trying to produce reports that will show info such as all my expenses in a certain category (sometimes more than one), summed by month and category in a variety of different ranges of dates/months.

The only way i currently know how to do this is by creating buttons for each category (and all mixtures of certain categories together as well), each with a script to a new layout which will have a search and show only those records-- and then sort them by subcategory and month. Then, to choose specific ranges of dates, such as by month or by quarter year, i would have a drop down box with these values and a script on commit with a whole bunch of "if(field="January", ).

I am new to filemaker, but it seems to me that all those buttons on the banking transactions layout is cumbersome, that there are too many steps involved, and that with a bit of help there is a much simpler way to do this.

Can anyone offer any guidance/advice? Your help is appreciated.

Regards,

jimmy

[David McQueen]

Just off the top,it seems that category should probably be just a field and then a standard summary report would work.

As I do not know all that you are doing, I could be right off in left field.It just seems more complicated tyhan it has to be.

HTH

[JimmyLewis]

Just off the top,it seems that category should probably be just a field and then a standard summary report would work.

As I do not know all that you are doing, I could be right off in left field.It just seems more complicated tyhan it has to be.

HTH

Hi that is not the problem, Category is a field and the report will be a standard sub-summary report sorted by month/category.

the problem is the mechanism of creating such reports:

Do i make a button or drop-down for with category based on a value list, with each member on the list having a specific script, i.e. if(category="education", perform script (go to layout/perform find/sort records)? And then how do i then qualify it further by month or quarter year? another drop down box? And since I would be using a drop down box for dates, is there any way for the date ranges to actually be in date format, readable by a script, and not just a value list based on the current months i can think of at the moment?

You are right, there must be a simpler way to do this. but each report needs to be omitting many records and qualifying them by dates, and it seems to me this process is length (a script for each category, a script for each month range per each category), and just very lengthy and probably heavy on the file itself.

Have any ideas on how to make this simpler?

[bruceR]

I think you're going to need to upload your file or a simplified example. You seem to be doing things the hard way. There is no reason that has been made apparent yet that a very standard find by date range, sort by category report would not work.

[comment]

Perhaps this can get you started:

TxReport.zip

[JimmyLewis]

Hi,

Thank you both for your help. I have tried to simplify the file quite a bit (it was quite the mess after implementing different tests).

the two visible layouts:

banking transactions:

info imported from bank csv or program, with "bank_category" filled as well.

Receipts:

contains receipt layout (eventually for printing). No receipt is entered in from Receipt layout, but rather the "new receipt" button in the banking transactions layout (which creates a related record).

At the footer of "banking transactions" are two report method attempts, one generating reports by category without date range, the other with a date range (based on your example).

The problem I have is that I would like to have the ability to search for a collection of several categories together, such as medical, education, rent, and auto. I do not see a simple way to be able to specify both a variety of categories and a date range.

How can I perform such searches with so many variables?

Thanks,

jimmy

FMforums.fp7.zip

[comment]

To find records in several categories, you need to create a request for each category:

TxReport2.zip

[bruceR]

Besides the fact that Comment's report script does what you need, note also that it use explicit scripted finds. These are MUCH easier to read and understand than Perform Find [ Restore ].

Enter find mode

set field [some field; some value ]

Perform Find

[JimmyLewis]

thank you so much, both of you. This clarified things to me and all is working. I appreciate the help.