I started a project and got it almost done however I am simply missing the understanding of report layouts (preview mode) vs the same in list view and possibly relationships between tables or so I believe.

For the description of project you can see this thread:

http://fmforums.com/forum/showtopic.php?tid/198469/

Basically I have two main tables. Orders and OrderItems. It is a one to many relationship. What I need is to get a set of reports. I got some of the reports working but not all.

I added a third table named Items. I related that to OrderItems via SKU number so that will give me a report of total quantities for each SKU number.

What I need to do is to have that report filtered by date so that it will show the totals between certain date only - this is where I am stuck. I tried sorting the OrderItems table in a found set however I still get totals for all of the data regardless of date or I can get totals for certain date but the SKU keeps repeating.

You can take a look at my test file to see.

Basically I want to do these reports in List View.

Example:

Date Range: 4/1/08 - 4/2/08

Item1 - 3pcs

Item2 - 4pcs

Item3 - 2pcs

Total - 9pcs

What I am currently capable of doing and which I do not want and which is wrong is this:

Date Range: 4/1/08 - 4/2/08

Item1 - 3pcs

Item1 - 3pcs

Item1 - 3pcs

Item2 - 4pcs

Item2 - 4pcs

Item2 - 4pcs

Item2 - 4pcs

Item3 - 2pcs

Item3 - 2pcs

Total - 9pcs

Wrong use of summary field I believe

Or

Date Range: 4/1/08 - 4/2/08

Item1 - 1pcs

Item1 - 1pcs

Item1 - 1pcs

Item2 - 1pcs

Item2 - 1pcs

Item2 - 1pcs

Item2 - 1pcs

Item3 - 1pcs

Item3 - 1pcs

Total - 9pcs

So basically this is where i am stuck.

Thank you.,

Sales.zip

Edited October 7, 200817 yr by Guest
Typo in date range

Your report needs a sub-summary part (when sorted by SKU) and no body part.

Thanks, I got that report working in preview mode but the moment I switch from preview mode to browse mode and choose List view all disappears. I am still missing something.

Thanks

That is correct - sub-summary parts work only in Preview/Print. If you want something similar in Browse mode, you will have to this from the point-of-view of Items, with a new relationship to another occurence of OrderItems, filtered by date range (2 global date fields in Items). Summ the related OrderItems (i.e. those having the same SKU AND a date within the date range).

Thanks, got it to work now in a browse mode up to a point.

It sums the quantities within given date range correctly and the SKU's show only one time. How can I however filter out the SKU's which are 0 in the given date range or should they have been filtered out automatically, I might have made a mistake following your example.

Now in browse mode I get this:

Date Range: 4/1/08 - 4/2/08

Item1 2pcs

Item2

Item3 5pcs

If I change date range to 4/1/08 - 4/3/08 I get

Item1 2pcs

Item2 9pcs

Item3 5pcs

How can I filter out (remove) Item2 from showing in the first example as I have not sold any in the defined date range?

If I have it show as Item2 0pcs it will make the list long as certain SKU's runs up to 3000k (Ie. Item2999)

Thanks a lot

Edited October 7, 200817 yr by Guest
Date range typo

Do a find.

Hmm, that is strange. When I go to find mode I cannot select the date fields.

They are global and behavior is set to allow selection in browse and find mode.

No idea why I would not be able to select them.

This is the calculation I am using, not sure if that has anything to do with being able to select date fields in find mode or not

If (Orders_Items_2::Date ≥ Date1 and Orders_Items_2::Date ≤ Date2 ; Sum (Orders_Items_2::Item_Qty))

I got around not being able to select the date fields in find mode by writing a find script and using a custom dialog to display the date fields in and then preforming a find. Is that the good way to go?

1. You don't need the If() part - the relationship is supposed to do the filtering. The calc is just:

Sum ( Orders_Items_2::Item_Qty )

2. You need to find Items where the calc returns > 0.

Thanks, figured out the part for search >0, I removed the If part from calculation and it all filters nicely.

Thanks a lot.,