solutions wanted Festival Cash Room Shared Database

[cooldjmac]

Our church has a monster festival a few times a year as a fund raiser for multiple charites. Lately it has been going to the homeless/jobless here in Michigan.

My database goals are:

web based so 8 people can enter deposits and withdrawls from the cash room

Here's the flow... Brinks drops off the opening cash

(I would like to record this deposit for an end of day report)

From that cash.. money is taken from that opening cash and creates booth opening cash. (10 booths in each section and there are 9 sections).

During the day a runner will collect some of the money from the booths into there marked envelopes and bring them to the cash room where we have 8 people with money counters they run the bills through.

They enter how many hundred dollar bills, fifties, twenties, tens, fives, two's and one's currently into an excel spreadsheet. Also coins, visa and checks. I would rather do this into a database for reporting purposes.

Also during the day withdrawls are made to pay maintenace or sidejobs that need to be tracked somehow. Probably Petty Cash with the ability to add a note to each withdrawl transaction.

The very hard part about this all so far is this part... the reporting.

End of the day cash summary sheet.

Opening Cash

Closing Cash

List of Petty Cash transactions plus total Petty Cash used

How much did each booth make that day in list format

How much did each section make that day in list format

How much did the festival make that day

Each booth has a number and a name of the product they sell. So for example. In section 100, booth 101 sells smoothies, may also have smoothies in section 200, booth 211. So how can I run a daily report for how much did smoothies bring in.

Also, can I run this daily report based on a date or date range that I pick.

Any help is MOST appreciated. We have another festival August 11-12 and i would love to have a solution in place. Thank You.

Edited July 21, 2007 by Guest

[cooldjmac]

To start off I created two tables

1. labled booths (Contains Fields booth_number, booth_name, section_number, fkey_trans)

2. transactions (Contains Fields pkey_trans, fkey_boothnumber, onehundreds,total_onehundreds,fifties,total_fifties,twenties,total_twenties,tens,total_tens,fives,total_fives,twos,total_twos,ones,coins,coupons,total_visa,total_checks,total_cash,total_coupons,total_transaction,date_create,time_create,account_create)

Seeing how I am very new to Filemaker pro (actually databases in general) I am not sure if I am proceeding in the right direction.

I have created a relationship (Booths:BoothNumber --> Transactions:fkey_boothnumber) This way I create a drop down list of booths while entering transactions. I can update the booth information as a new vendor comes in or changes what they are selling.

I am having issues with the reporting side. How do I run a report that shows a list view of all booths and how much each one made individually for the day, then how much did all the booths in one section make and then how much did all booths make. ANY IDEA'S?: I've been trying to figure this out for awhile now and not getting anywhere.

[cooldjmac]

Could someone recommend a book that would help a new person getting involved with FileMaker Pro 9?

I would like the book to show a lot of examples on how to do the multiple tasks that I can not seem to find the answers to.

I can't seem to find a way to create summaries (no idea on how to do this). I am hoping it is possible to create a daily cash summary sheet by section, by booth, by booth name.

I am willing to invest some of my money in learning this product. I have checked out VCT but not sure if there is a book I could skip to the chapter I need.

Any recommendations?

[Fenton]

There doesn't seem to be any FileMaker 9 books out yet. I believe the Missing Manual books are supposed to be good, as well as the Using FileMaker Pro, by Que publishing (I have the FileMaker 7 one, and it has a chapter on summarized reports).

You're doing very well for a beginner. You just haven't learned about Summary fields and Subsummary parts yet. A Summary field is a type of field. Create a field, choose Summary as the type (as opposed to Number or Calculation). I'll call it Money_SUM (I like to use the suffix _SUM for these, but that's just me).

A dialog will pop up and ask you to pick the type of summary. You'd pick (•) Total; of what field? you'd pick your money total field (which is all the money added up, in a calculation field; I assume you have this already).

Then create a layout. It will be based on the transactions Table Occurrence (TO) where you record the money (but read at the bottom about booths and products). In Layout mode, drag (or define) a Part, from the Status area onto the layout, above the Body part. The type of Part you want is a Subsummary, when sorted by Section# (this is a required choice). Then drag another, below that one, a Subsummary part sorted by Booth#.

Drag another, below the Body. It would also be Subsummary sorted by Booth#, but it would be "Trailing." Then a Subsummary part sorted by Section#. It's kind of like a sandwich :-]

At the bottom add another part, a Trailing Subsummary.

Put the Section# field in the top "section" Subsummary, and the Booth# field in the top "booth" Subsummary. This will show the number above its entries. Label with "Section#" and "Booth#" (so you know what it is).

Put your Money_SUM field in the Body AND in the trailing Section# subsummary part AND in the trailing Booth# subsummary part AND in the Trailing Subsummary part; the same summary field in the parts.

Now Sort by Section#, then Booth# (1 Sort, with 2 fields). Then go into Preveiw mode. You'll see your report. The Money_SUM field will add up the subtotals correctly for the parts that it is in, with a grand total at the bottom.

All of this is for the found set. So Find your records first.

If you have trouble, post a sample of your file. It is not real difficult, once you get the hang of it.

As far as reporting on what each sold, that begs a very important question, which is: Does each booth sell 1 and only 1 product? If not then you'll need to record the sales by product for each booth. Otherwise include the Product# in each transaction.

You could then have a report, similar to the previous, but with a Subsummary part(s) sorted by Product#. It could even be the same layout, when sorted only by Product#. Because Susummary parts ONLY appear (in Preview mode) when the current Sort contains their "break" field. Otherwise it's as if they were not even there.

Edited July 21, 2007 by Guest
Products report

[Fenton]

Actually I didn't read your post sufficiently. You would want another Subsummary part, under the Booth# one (leading) for Date, sorted by date, then similar (trailing) right under the Body.

This will give you a report by Section, Booth, Date.

If you put the Date subsummary part above the Section, and sort by Date first, then you'll get a report by Date, Section, Booth.

Also, if you do not want to see all the actual transaction entries, you can delete the Body part. Move the Money_SUM field up into the leading subsummary parts, then delete all the trailing subsummaries. Then your report will have only the name of its part, Section#, Booth#, Date and its Money_SUM amount.