Creating a Report

[ConorH16]

Hi again,

 

First of all take a quick look at the screenshot.

 

Each of the tabs on this layout has it's own portal and therefore it's own table. These are all related together with Horse ID.

 

As this system is for a Livery yard, they need to send out Invoices..

 

Is there a way I can take all the costs from this month from all of these tabs and put them into one report to print as an invoice?

 

I have tried several ways but just don't know how it would be done.

 

Thanks so much,

Conor

[bcooney]

"therefore it's[sic] own table"

 

Just because it's different portals, it does not mean it's different tables. The portals may all be based on the same TO, and yet have different filters applied. In fact, that may be what you should be doing in your system.

 

May I suggest, as I've read others have, that you take a step back and let us help you with your data model. That is, what are your tables, what are their relationships? Just looking at the screen shot, it seems to me that you might need one services table, but that an attribute of the service (a field in the table) is service type. If you had logged all services in one table, you could easily build an invoice from a found set of service records.

[eos]

Hi again,

 

First of all take a quick look at the screenshot.

Each of the tabs on this layout has it's own portal and therefore it's own table.

 

May I take my time while looking at that pretty picture …?   (pretty please! … what, no colours!?   )

 

Back from toyland … Please be clear: table or table occurrence (TO)? Also be aware that a tab doesn't “have a table”; a tab is an object on a layout that's based on a TO that's based on a table.

 

This may sound like nitpicking to you, but grasping the concept of context is paramount to being “fluent” in FileMaker (or at least get a somewhat proper database up and going without too many hiccups …) 

 

From the legend, I gather that the portals in the other tabs also display kinds of charges (EDIT: charges …services … something like that).

 

it would make sense to use a single table for all charges/services and distinguish them by a category field; then on Horses either use portals into filtered relationships, or filtered portals.

 

(Think of an invoicing solution; you put all the line items into one LineItems table, regardless of the product it's based on.) 

 

Given such a structure, all you need is to do is create a layout based on charges/services and search for all Charges/Services records with a specific horseID foreign key that fall into a given date range – that's your report.

 

If you actually have a different table for each type of Charge/service … that would be inconvenient, and I would recommend that you restructure your solution (“while there is still time …”   )

[bcooney]

Hey, Oliver, jinx!

[eos]

Hey, Oliver, jinx!

 

Barbara, what's a girl like you doing in a nice place like this? 

 

OK, JK: nice to meet you, too!   And equally nice to see how concisely you express what I need six rambling paragraphs for! (Will flattery get me anywhere …?) 

 

(and how thoughtful of you to [sic] the quote …  )

[bcooney]

I have been swamped lately, but do check in when I can. Good to see you, too.

[ConorH16]

Right, I tried putting it all into one table with a charge category but am kinda confused on how to do it...

 

I will try again.

 

And, no, no colours yet. I want to get the darn thing finished first

[bcooney]

What part has you confused?

[ConorH16]

Hi, I have created one rather large table to incorporate all of the fields I have for charges.. 

 

I am trying to figure out how to automatically set the category..? I tried putting it into a script attached to the button but that doesn't work. Is there another way?

[bcooney]

Conor, "one rather large table" really raises a red flag for me! In many cases, this is a clue that you are using fields rather than related records to store data. 

 

Automatically set the category? When? Off the top of my head, you could have an Add button that creates a record and uses a script parameter to set the Category (the parameter would be the category_id).

[ConorH16]

I don't quite get what you mean by this "you are using fields rather than related records to store data.". I am completely new to Filemaker so really don't have any knowledge whatsoever..

 

Also, the people above suggested I use a single table and then distinguish them by category... Are they wrong? I really don't know...

 

As for the button script, I suggested that in my previous post I had tried it but am still unsure about what function to use. I used the SetField() function but that didn't work entirely...

[bruceR]

Have you considered just hiring an experienced developer?

(I'm not volunteering)

[eos]

Also, the people above suggested I use a single table and then distinguish them by category... Are they wrong? I really don't know...

 

Who exactly are you talking to? We are the people who suggested this … 

 

Anyway, as a very quick primer:

 

If you have 

 

Animals --< Services >-- ServiceCategories

 

you can store all your Services rendered into one table Services, where each record has fields like date, amount, client(ID), and of course, Animal and ServiceType foreign keys/IDs (for category, a category text field would do, too; as long as you it tells you what type of service it is).

 

Since you can search/filter/summarize the records in the Service table by animal, service type and/or date, it is the basis for your reports (e.g. services per month) and invoices (e.g. uncharged services for client Susan, or horse Harry, or both …).

 

And because you create new records (instead of filling / using up predefined fields in a table), you will always have exactly as many Services records as needed. (Besides the fact that a field-based approach makes reporting very difficult to impossible.)

 

I recommend you either study up on (the basic principles of) relational database design, or, as Bruce suggested, hire a developer (I might volunteer if the price is right  )

[bcooney]

"Also, the people above suggested I use a single table and then distinguish them by category... Are they wrong? I really don't know..."

 

Since I'm one of the people above, no they're not wrong.

 

+1 to Bruce's suggestion. You're lost, and you're also not listening to any advice. That adds up to a mess, imho.

[eos]

Jinxed, again! 

[ConorH16]

Thanks eos, I'll keep trying.

 

As for the rest of you, never mind. I came on here for help. I have tried everything that has been suggested and now I'm being told i'm not listening. I have told you that I am completely new to Filemaker and am just looking for some advice... Clearly not gonna get any helpful advice from most of you.

 

Thanks for nothing.

[bruceR]

You are getting useful and relevant advice.

 

You admit to being new; and you do not understand the advice you are being given.

 

You have an important business need and it is not something you are able to deliver.

 

You will learn a lot by working with a developer; who should most likely to be able to straighten this out in short order.