Report of sales by employees during defined time perion

[AndyCan]

Hi everyone.

First i would like to say thanks to those who have helped me previously in this forum.

This should be a pretty simple question, but i need someone to point me in the right direction.

I am building a database to manage sales, inventory, employee and customer records amongst other things. A part of this includes a record of every sale made with the foreign key ID of the salesperson, date of sale and item sold.

the details are based in two tables labelled Sales and Sales Lines. Sales containing the date of the sale and the fk Employee ID and Sales Lines containing the quantity and item sold and is related to the table Sales via a Sales ID foreign key.

What i would like to do is to generate a report of sales by employee between a start and end date that the user defines on the layout. The important thing is that i don't want to show all the individual sales for each employee during the defined time period, just the totals shown next to a list of all the employees.

Something like this:

Start Date = 1/1/2008

End Date = 1/7/2008

SALEPERSON TOTAL

Tom $100

Dick $354

Harry $45

----------------------

Basically i just need someone to point me in the right direction and then i can study up on the implementation. Should i create extra calculation fields, use summary fields, a list layout, a portal, extra table occurences?

Please feel free to ask me more questions if my explanation was lacking.

Kind regards,

Andy

[AndyCan]

If it helps at all, here is an image of my relationship graph as it stands at the moment...

http://www.chacal.co.uk/Relationships.png

As you can see, i'm a little confused at to where i should place the startDate and endDate globals.

Andy.

[mr_vodka]

You can do this with a subsummary report grouped by Sales Person. Just delete out the body part and put the summary fields on the subsummary part.

As for the date range, you can find the sales records within that range first as the report will be based on the found set of records.

[AndyCan]

How do i create the found set? is that done via an additional table occurence and a save query or via some other method?

If it is via table occurences, could you be more specific, ie. where do i place the global fields startDate and endDate and which table should i duplicate for the table occurence?

The relationship graph in my second post should help make my question easier to answer.

Many thanks

[AndyCan]

By the way John, thanks for the first tip. I now have it displaying the way i want, but unconstrained by date ranges until i figure that part out.

Should i assume that this type of report is only possible if i use the Preview mode?

Thanks.

Andy

[mr_vodka]

Well you can script a find to do it or you can use relationships with a Go to Related Records.

Go to layout ["sales"]

Enter Find Mode []

Set Field [ YourDate; gStartDate & ".." & gEndDate ]

Perform Find []

[AndyCan]

Thanks for your replies John.

I've got the layout working as i wanted in preview mode (without date constraints as yet) although for some reason updates to the underlying records do not display correctly until i choose to sort records from the records menu.

I'm not quite sure where i should put the start and end date fields both in the sense of which table and which layout. I notice that the preview mode is not interactive in any way and there's no way to place drop down calendar fields there.

Is there some way to do what i want to do in an interactive layout where the results are updated dynamically as the user chooses dates for the startDate and endDate fields? And if not completely dynamically, then perhaps with a scripted 'update' button on the same page once the dates are chosen.

The way i see things working at the moment is that i would have to have a separate layout where i choose the desired dates, click on a 'generate report' button and am taken to the report on another layout viewed in preview mode.

Thanks again for your time,

Andy

[comment]

If you define a relationship between Employees and a new occurrence of Sales as:

Employees::EmployeeID = Sales 2::SalesPersonID

AND

Employees::gStartDate ≤ Sales 2::SaleDate

AND

Employees::gEndDate ≥ Sales 2::SaleDate

you can then see your summary "live" from a list layout of Employees.

[AndyCan]

Hahaaaaaaaa! Fantastic.

Thanks for making it so simple Comment. Worked like a dream first time and helped me to understand more about how to use table occurrences.

Thanks again!!

[AndyCan]

Hi Comment,

I wonder if you could help me again with a related but slightly different problem.

This time i want to display a list of menu items and the quantities sold between specific dates.

Have a look at the table occurrences and you'll see what i'm talking about:

http://www.chacal.co.uk/salesbyitem.png

The tables are:

SALES-----SALES LINES-----MENU ITEMS

Menu Items is the base for my layout and is linked to the Sales Lines TO via the Menu Item key. The problem is that i want to filter the records via date sold, but the date record is in the Sales table and not in the Sales Lines table.

How then do i 'reach' the Sales TO from the Menu Items TO? I can edit the relationship between Menu Items and Sales Lines, but that doesn't give me access to the date field in the Sales table with which to use the greater than, etc operators with my global startDate and endDate records.

To be honest i'm still having a bit of trouble getting my head around the Filemaker way of doing things. I've had some SQL experience in the past and at the moment, a basic SQL statement where i could reference multiple tables seems like it would be easier.

Oh well, i'm sure i'll catch on soon enough with a bit more help from the forum!

Thanks for your time.

Andy

[comment]

Well, Filemaker's reporting power is mainly in producing printed reports (or on-screen reports in Preview mode). It would be very easy to find the Sales in the given range, then go to the related SaleLines and summarize them by MenuItem.

To show the same thing in Browse mode is a bit more difficult (and very likely slower), but possible. A simple way would be to have each SaleLines lookup the date from its parent Sale record*. Then you could place the global range fields in MenuItems, and filter a new occurrence of SaleLines by MenutemID AND date.

A more sophisticated method (a.k.a "the Ugo method") would place the global dates in the SaleLines table, along with a calculation field cConditionalID =

Case ( gStartDate ≤ Sales::SaleDate and Sales::SaleDate ≤ gEndDate ; LineID )

Now, define a relationship from SaleLines to a new occurrence of SaleLines as:

SaleLines::cConditionalID = SaleLines 2::LineID

From the point-of-view of MenuItems, the relationship to SaleLines 2 is filtered by both the MenutemID and the date range.

---

(*) A caveat applies here: a child record will not lookup from an uncommitted parent, so this requires committing the sale record after the date is filled and before creating any sale lines.

[AndyCan]

Thanks for your reply. I've spent the last 3 or 4 hours playing around with the tables, relationships and layout in everyway possible and it looks like i was trying to do something that's not possible purely with the relationship graph.

Would you recommend that rather than a lookup i just redesign my DB to store the date field in the sales lines as opposed to the sales table or would i be taking a step backwards as far as clean design is concerned?

Or would your personal preference be for the Ugo method you described just now?

Thanks Comment!

Andy

[comment]

I cannot really answer your question without seeing the entire solution and, more importantly, the way it will be used. I can only add two more points for your consideration:

In practical terms, if you add an auto-entered CreationDate field to SalesLines, I doubt it will be ever different from the one in Sales (unless there's a sale in progress around midnight). It also seems very unlikely that a sale date, once entered, will be ever changed (which would require a cascading change in the sale's lines). So here you could solve both problems, the relationship and the lookup, quite simply - with a small expense in the form of a redundant date field in sale lines.

OTOH, implementing the Ugo method would be very cheap in your specific case : the number of required TO's is the same, the globals are needed anyway - so it's just one more unstored calculation field to add, instead of a stored one.