Summary by date

[cooldjmac]

Help pretty please....

I am very new at Filemaker but very determined. I could really use some help with this stumbling block I am stuck on.

I have three tables: AREA, BOOTH, INVOICES

INVOICES are created by a booth by booth basis.

each BOOTH is found in an AREA.

I have two hurtles I have not been able to accomplish.

1.) insert daily totals by AREA in the header of my form

2.) view a list of all booths and there daily totals

Helping me solve this would allow me to leave my computer for awhile.

Any help or direction is most appreciated.

[cooldjmac]

I was trying to figure out a calculation of sorts...

something that would say..

If AREA::area="100" then Get Sum for INVOICES::subtotal if date = today

If AREA::area="200" then Get Sum for INVOICES::subtotal if date = today

How do I do a calculation like that. I believe that would product a summary of total invoices per area. I could follow the same formula/calculation for Booths.

[bcooney]

Here's a quick sample. I've entered invoices for today and yesterday. This was created in FM8.5A.

AreaBoothSample.fp7.zip

[cooldjmac]

What a fantastic response! Thank You very much bcooney. I have been struggling with this for awhile. I am now going to give this a whirl on my existing database.

Wild how fast you created that. Thank You.

I'll respond with my progress....

I'm including my db so far and I hope you don't laugh too hard at a newbies first filemaker database. You can probably tell what I am trying to do but not quite there.

all comments are welcome. I do want to learn.

RenFestival.fp7.zip

Edited July 23, 2007 by Guest

[bcooney]

I'll give it a look. (Don't be too impressed, I've been using FM for 20 years, yikes!)

[cooldjmac]

Do you have any recommendations for books or training on learning Filemaker? I am really enjoying building the database and would love to follow "a path" to understanding it better.

Of course my friends tell me you can't teach an old dog new tricks but I am determined to learn Filemaker. I can see the many uses once I understand it better.

Any recommendations would be most apprecaiated in helping me learn Filemaker. Like a reference book or something.

[mr_vodka]

JMO has some training videos if you like video training.

http://www.databasepros.com/videos.html

There are also lots of books out there. If you can get a hold of an old Special Edition Using FM7 book, it is a good book to start with the concepts. There are also updated ones for 8-8.5.

[bcooney]

I've taken a quick look at your file, and you're doing great so far. The most important part of a system is getting the data model correct, and you have!

I suggest adding "double-underscores" before primary keys and "single-underscores" before foreign keys because that makes them come to the top of the list when fields are sorted alphabetically. Since the key fields are the ones you're always grabbing, having them sort down in the "k's" means scrolling, and that uses up billable hours! This document FM Dev Conventions may be beyond your current level, but try to get your mind around the Anchor-Buoy technique for the relationship graph. It'll save you much heart-ache as your system grows.

Also, most key fields should be numbers, not text.

I have this book and think it's great, but it is hard to put myself in your shoes--so much is now second nature.

Stay on the forums and trust your instincts. I find that with FM, if it's getting too convoluted then there's a better way.

[cooldjmac]

Thanks John. I did look at the very well prepared video's on vtc's website that were put together by John Mark Osbourne. Unfortunatly they do not have a lot on Filemaker Pro 9 yet but what I looked at was on 8 and they aren't that much different then each other from what I can see.

Wish they accepted paypal accounts. Will have to see if the books answer my questions. So far the video's already covered what I know. Need some major help with calculations, summaries, parts basically.

Thank you for your response... I'm not giving up on the video idea.

[cooldjmac]

I really do appreciate you looking at my first db attempt. I have been trying to implement your approach to my database and for some reason I can not seem to get the portals to work properly. Also, is there a trick to get the summary part to show Grand Total? I am trying to recreate your report which I am hoping I will learn by doing. Some reason when I had each part it never shows Grand Summary as an option.

I have ordered the book you recommended from amazon.com Some good reviews on the book also it gives me something to read when I want to take a break from the computer.

Thank you again for all your help. I have implemented the suggested changes (changes primary keys/serial numbers to numbers, and of course the foreign keys to numbers as well)

It does make sense to use the underscore for housekeeping fields. I was curious as to why you were using that in your db sample. Now I understand.

I will keep plugging away at it and see if I figure it out.

[bcooney]

A summary field put in a Grand Summary part will show the total for all found records. It's just magic! In a sub-summary it displays the total for that break field.

[John Mark Osborne]

I am currently putting together FileMaker 9 videos. The beginner video is done and for sale at the http://www.vtc.com/ web site. The intermediate and advanced will follow soon.

[cooldjmac]

I figured out why I could not add a Grand Summary Total. I had to delete the Body of the layout and it then became available as a part.

Still not getting the calculations or the portals to work but I am still digging.

[comment]

That doesn't sound right. You can have a grand summary part, with or without any other part.

[bcooney]

Hello, again. Found some time to look more closely at the file that you posted. Sorry, I know that you've probably added a lot since this post. However, here's what I found.

Your "arealist" value list did not include the ID field, it was just the name. I've changed that, because you want the areaID in the booth record. So when the user chooses from this value list, FM puts the area ID in the field. It may show the area name, because we've told it to only show the second value in the value list, but we're really getting the ID.

Booth names must be unique. Because, if you use them in a value list, and don't show the ID, it will only show unique values.

I created a daily invoice report. To do so, I added a summary field to Invoices, and then used this field on the report in subsummary and grandsummary parts.

I didn't change your relationship graph, although I was tempted. Read all about the anchor-buoy technique.

As for the grand summary part mystery. Sometimes it's easier to add parts to a layout by using the Layout>Part Setup menu choice, rather than dragging a part from the tools palette.

RenFestival__B.zip

[cooldjmac]

WOW, thank you for taking the time. I have learned a lot by following your example that you provided earlier and things are making more sense.

I am attaching my updated file (its the 10th time I re-created it) just so you can see my progress.

The biggest issue I am having is I can not get the report working correctly (it won't show the area number and booth number).

My second issue is the invoices layout won't show them in the portal, it puts the transactions/invoices in seperate records instead of in the portal. I'm sure it's probably a mix up on my side for sure.

See what you think. It's a lot cleaner then my original one (thanks to you).

FESTIVAL.fp7.zip

[bcooney]

Looking very good. Made comments on layouts. You're such a quick learner! It's interesting how you do the invoice line items. I guess that's just how the business works. So each invoice really only gets one invoice line item. I'm wondering if you need line items! Maybe all of its fields should go in the invoice table, since you don't seem to have a one to many relationship here.

FESTIVAL_B.zip

[cooldjmac]

Thank You for the compliments. Actually with your comments and suggestions and examples, I am learning a ton. You are very good at what you do and I really do appreciate all your help. You can't imagine how much I appreciate your help.

I still have a lot to learn. I hope the books I ordered will help and when John's done with the video training I may just get that as well. I would like to focus on being a database admin. I really do enjoy the logical side of it as well as being able to create and organize.

Are you a FM admin or a db admin in general? Curious if it is a worthwhile job path to take. I'm tired of being a network admin, need a change of pace.

I am still confused a bit about the line items piece (whether to use it or not).

In my mind Line Items deal with product and in this case the product is Cash. They run there stacks of money through a machine and it says how many they have at which time they will enter it in the database via the web interface.

Also I have to keep booth numbers and names because in all areas they have smoothies and turkey so I can tell which area is bringing in more money in turkey sales by booth number.

I would eventually like to run a report on just the product (like smoothies) and maybe graph it based on time and how much money came in. That would be pretty cool.

Edited July 26, 2007 by Guest

[bcooney]

Presently, I work part-time as a FM developer (home in time for the kids). I mostly do sub-contract work, that is, I don't do the interface with the client, just the development of the system. Definitely, people make a living doing this.

[cooldjmac]

Hello bcooney... Figured I would give you an update on my progress with my first Filemaker Pro database.

I'm really liking it so far and did my first import of all the booths into the table.

Only fighting a couple of issues that I put in the db on the layouts

invoices_day, invoice, cash summary

I wonder if my biggest issue in learning Filemaker is the understanding of relationships. I believe I understand it then I run into these type of issues which I am not sure if it is a relationship issue or a field related issue or something else that I haven't even thought of.

Any idea's is (as usual) greatly appreciated.

Festival_v1.2.fp7.zip

[bcooney]

Does each booth sell more than one product? If not, I think that you should have another table, Products, related to booth by ProductID.

That is the only way you'll be able to report sales by product. Right now, the product is part of the booth name. Not good.

So, when a user selects a booth, they are also selecting the product. I've marked other issues in the file.

Your areas weren't coming in bcs you were putting in the booth number not the _kf_boothID in the invoice. Watch those value lists.

Festival_v1_3.fp7.zip

[bcooney]

I know, I need a life, but I find this very relaxing, lol. So, here's another version without invoice line items. In fact, I've changed Invoices to Transactions and added the relationship from Booth to Product.

If Boothes sell more than one product, we're wrong and will need to add another table btw booth and product.

Have at it.

Festival_v1_4.fp7.zip

[cooldjmac]

Wow... it's looking so good now. I love your idea of just making the primary keys the area and booth numbers. They have to be unique anyways. I also liked the idea of booth names being seperate and listed as a product.

The report is getting closer but I am unable to get it to work right. The goal is to display

Grand Total by Date

sub-total by Area

sub-total by Booth

Looking at the parts, it looks right, but with some transactions in there, it doesn't seem to be working.

Festival_v1.5.fp7.zip

[cooldjmac]

I forgot to mention... I got the book in the mail today (Special Edition Using FileMaker 8). Going to start reading it today to see if I can gain some more know how.

[bcooney]

Hi, I fixed the report. Somehow the summary fields were gone? I redefined them and put them back on the layout.

So, each booth is one product? Therefore the difference btw cashout and cashin is the sales for that product. You can summarize sales by product by using the product ID. In fact, you could compare daily sales for all booths with the same product.

The export to excel as summary data is giving me trouble. I think you can accomplish this with the Export Records command (not Save as Excel). If the data is sorted the same as the report, you have options to summarize. However, once it's in Excel, it needs a lot of formatting to be comprehensible.

Do they do any analysis with the data once it's in Excel? Something you can give them in FM? Sounds like they want the new system, but want the old way too. That's usually trouble.

Festival_v1_6.zip

[bcooney]

You might want to read this, by the brilliant M. Edoshin: crosstab

and this technique may come in handy... summarize in portal

[cooldjmac]

Thanks for the updated form... It works like a charm. Sorry its taken so long to respond. Swamped with work unfortunatly. I actually am having fun learning Filemaker with all your help.

I heard something about scriptmaker for filemaker. have you heard of this and do you think it provides anything useful? Can you do everything scriptmaker does? Just curious.

Enjoy reading the book... it is very well written but I do wish they provided more examples. I haven't looked at the included CD too much so maybe something there.

Playing with the idea of running a stock market ticker of sorts in the Admin panel for each AREA so the admin/manager or who ever has admin right can see the increasing amount made in each area. That would be pretty cool. I would assume it would be something like a summary but for each day by area. Something like that.

Thank so much bcooney for all your help and wisdom. I truely do enjoy learning from you. Thanks!

Edited July 31, 2007 by Guest

[bcooney]

I heard something about scriptmaker for filemaker. have you heard of this and do you think it provides anything useful? Can you do everything scriptmaker does? Just curious.

Umm...scriptmaker is where all the cool stuff is done in FileMaker. It's the tool that lets you put multistep "procedures" into your system. Keep reading!

Your stock market ticker idea is fine, but summary totals are easiest to create in reports that are viewed in Preview mode or printed. Not in browse mode.

For more how-to's and examples, check out:

FM Advisor Basics or

ISO Magazine Matt's movies are a terrific way to learn.

Glad to help.