Anuviel Posted October 7, 2008 Posted October 7, 2008 (edited) 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, 2008 by Guest Typo in date range
comment Posted October 7, 2008 Posted October 7, 2008 Your report needs a sub-summary part (when sorted by SKU) and no body part.
Anuviel Posted October 7, 2008 Author Posted October 7, 2008 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
comment Posted October 7, 2008 Posted October 7, 2008 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).
Anuviel Posted October 7, 2008 Author Posted October 7, 2008 (edited) 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, 2008 by Guest Date range typo
Anuviel Posted October 7, 2008 Author Posted October 7, 2008 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.
Anuviel Posted October 7, 2008 Author Posted October 7, 2008 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))
Anuviel Posted October 7, 2008 Author Posted October 7, 2008 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?
comment Posted October 8, 2008 Posted October 8, 2008 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.
Anuviel Posted October 8, 2008 Author Posted October 8, 2008 Thanks, figured out the part for search >0, I removed the If part from calculation and it all filters nicely. Thanks a lot.,
Recommended Posts
This topic is 5890 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now