Payment Amounts for Given Timeframe

[798]

I am a recently new user of Filemaker Pro version 9, although I have started to pick up on some of the nuances of the database that was set up before I began working for this company. I am trying to retrieve all payments received by a patron given a timeframe. There are fields that have been set up to find payments recieved from YTD, 2 years back, 3 years back, etc., however these are set up for fiscal years, not for a range that I specify. How would I go about setting up a field that would allow me to look at a variety of timeframes? As a side note, the company is using a preset layout design called eBase, if anyone is familiar with that.

[LaRetta]

Hi groggle19, welcome to FM Forums! I assume that there is a PaymentDate field which is type date and that you are using vs. 9 (unlike eBase which uses version prior to 7)? If so, you can search that date field for a range. If you performed the find manually, it would look like:

3/15/09..6/30/09 (searching in the PaymentDate field)

If you wish to script it so it is easier for users, create two global date fields gFrom and gTo. Then place these fields on a layout next to your button to fire the script. Script would be (pseudo-script):

If [ not ( gFrom ) or not ( gTo ) ]

Show Custom Dialog [ OK ; "One of your date range fields are empty. Try again." ]

Halt Script

End If

Go to Layout [ layout based upon Payments table ]

Enter Find Mode [ uncheck pause ]

Set Field [ Payments::PaymentDate ; gFrom & ".." & gTo ]

Set Error Capture [ On ]

Perform Find [ ]

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ OK ; "No records found." ]

Go to Layout [ Original Layout ]

Exit Script

Else

... do whatever you wish with your found set

End If

If this doesn't fit the bill, let us know. There are many wonderful people here to assist!

LaRetta :wink2:

Edited October 13, 2009 by Guest
Added error capture

[798]

Okay, I tried those options you gave me they did not do what I am looking for. I will try to be more specific as to the nature of my issue. As I mentioned before there are field that are set up to check fiscals years up to 5 years back. There is another field, under the table "ALL PAYMENTS FOR NAME", called "Amount" When I have set up a layout in table format, I am able to find what patron has donated what and when, based on the time frame I give it. However it shows the amounts by date separately, not added up. I would like to add up these amount from each individual patron. I hope this adds clarity to what I am trying to accomplish.

[LaRetta]

However it shows the amounts by date separately, not added up. I would like to add up these amount from each individual patron.

Create a summary field which totals that amount. Then create a new layout (list layout, sub-summary). Let the wizard step you through creating the report. When you are done creating the report, let FM create the script and add the find criteria to the beginning of it.

If you don't want the body (each individual date record), you can simply delete it; be sure your summary is up in the leading part next to Patron's name. :wink2: