1 2 Previous Next 18 Replies Latest reply on Jan 31, 2012 4:20 PM by philmodjunk

    Date range filtering results

    JoshHarrison

      Title

      Date range filtering results

      Post

      Howdy,

      I'm a relative newbie, I've found some other posts on date range filtering but they don't quite seem to do what I need and I'm not sure how to tweak them.

      Here's the situation, I'm creating invoices for distribution partners based on royalty agreements. I'm using the distribution partner ID to pull entries from "books" (table on MySQL). Books is then pulling data from "sales" (table on MySQL), which logs individual sales transactions of each book (with date). I need to be able to filter a date range of "sales" to only be looking at the set of data which is between my start and end dates (defined as global variables in a local table).

      I've got this setup working with a portal, and portal filtering, just fine, but we need to be able to print these to hard copy and it doesn't seem like portals are the best choice for something you need to print. I've got "go to related records" successfully pulling up the records I need from "books" and displaying most of what I need in a new layout suitable for printing, but the total results aren't filtered by date range at all. As in, if we sold five copies of a book in a month, and I filter by 12/1/11-12/31/11, I need to only get the records associated with transactions occuring within that date range. Instead, on the GTRR, I'm getting all 90 some sale for the entire data set associated with that ISBN.

      How do I shoehorn date filtering into this setup?

        • 1. Re: Date range filtering results
          philmodjunk

          First we need to be clear on the tables and relationships invovled. Is this what you have?

          Partners-----<Books------<BookSales

          Partners::PartnerID = Books::partnerID

          Books::BookID = BooksSales::BookID

          And the dates in question are dates in book sales.

          If this is correct, the trick is to base your layout on the BookSales table and then add fields as need from books and partners.

          These fields from books and partners can be placed in header, footer, grand summary and sub summary layout parts as needed to produce the desired report.

          To pull up the correct set of BookSales records for a given partner and specified date range, you can use one of two methods:

          1) Perform a find on booksales specifiying the date range and the desired partner ID value in Books::PartnerID.

          2) Use Go To Related Records from Partners to pull up all BookSales records for that partner or found set of partners on the BookSales based report layout, then enter find mode, specify the date range and do a constrain found set to drop out all book sales records not in the specified date range.

          If you need a script example of one or the other methods or if your tables and relationships differ in more than just their names, let me know...

          • 2. Re: Date range filtering results
            JoshHarrison

            Partner to BookAgreement via bookID (one to many)

            BookAgreement to BookDetail via ISBN (one to one)

            BookDetail to BookSales via ISBN (one to many)

             

            Date in question is in book sales.

             

            "Distribution contract invoice" layout with portal is based on Partner. Portal consists of two summary fields from BookSales, and the rest from BookDetail. This is the one working as desired for a data display view, but isn't printer friendly. Portal is limited with:

            BookSales::Date Posted ≥ localtable::startDate and BookSales::Date Posted ≤ localtable::finishDate

            Looks like I could take BookAgreement out of the middle of the process but I'm not sure it is hindering anything at the moment and I'm hesitant to start rearranging stuff just yet before I know how it is working. If I need to, I need to, though!

            The GTRR I have set up goes to a layout based on BookDetail, since we don't care about providing details on individual sales to the partners, just sales per ISBN that belongs to them per defined date range. Header consists of partner detail, footer has a grand total. Header and footer on the GTRR friendly layout are the same as on the portal friendly layout.

            I was trying to build a script to emulate the portal limit filter I have above, but the date field doesn't seem to want to take that in any number of different ways I could think of to limit it.

            The thing I was trying to do this morning was find a set of BookSales based on the date range. I thought I could then either mirror those records in another table and GTRR on that table since it only contains the set of dates I need, or find some way to GTRR only on the found set of records within the date range in BookSales?

            • 3. Re: Date range filtering results
              philmodjunk

              The one to one relationship between bookAgreement and BookDetail should not cause a problem here as long as the needed record exists in both tables. (A BookAgreement record without a matching BookDetail record breaks the needed chain of relationships...) If that's a potential problem, you can add a new occurrence of BookSales to use for your report and link it directly to BookAgreement by ISBN. (Depending on the design and function of the rest of your database, that might even be a more logical structure for your relationships anyway.)

              we don't care about providing details on individual sales to the partners, just sales per ISBN that belongs to them per defined date range.

              You can set up this report on a layout based on BookSales. Either method I described earlier can do this.

              The trick is to remove the body layout part from your report layout since you don't want to see individual records from booksales, only a sub total for each book.

              You can then set up a sub summary part (when sorted by your ISBN field) and put fields in this part from both the other related tables (such as BookAgreement or BookDetail) plus a summary field that totals up values from BookSales if you need any such subtotals.

              • 4. Re: Date range filtering results
                JoshHarrison

                Thank you for your help, unfortunately I haven't grokked FM's way of doing things just yet! Sorry, bear with me if you will :)

                So I've made a new layout based on SalesDetail. Using the method you described, I can be browsing around in the partner based (portal) layout, run script (based on the second method you suggested, to follow) and see one of the many partners on a given item. For example:
                Book A is written by person X with research from Y and Z. {X, Y, Z} all get royalties. When I run the script while on {Y}'s entry, I only ever see the header for {X}, never {Y, Z}.

                I additionally am having trouble understanding how the subsummary works - putting the info I had on a given portal line in there results in one displayed entry in form mode (expected), but nothing in preview or list modes

                The script:

                Go to Related Record[Show only related records; From table: "BookSales"; Using layout: "The new layout" (BookSales)]
                Enter Find Mode[]
                Set Field [BookSales::Date Posted; local::DateRange]
                Constrain found set[]
                • 5. Re: Date range filtering results
                  philmodjunk

                  Book A is written by person X with research from Y and Z. {X, Y, Z} all get royalties. When I run the script while on {Y}'s entry, I only ever see the header for {X}, never {Y, Z}.

                  That is correct. You have specified a report based on data for partner X, not the partners, Y and Z so you do not see any data about them on your report. You'd need to add additional features to your database to support listing data for related partners. One method is to put a portal to Partners on your layout in order to list data from the other partners.

                  I additionally am having trouble understanding...

                  The report should be viewed in list view--not form view or table view. The found set of records must be sorted correctly before any sub summary parts will be visible. Note that your script pulls up records and constrains them, but it does not sort them so the found set you get is unsorted and thus any sub summary parts will not be visible.

                  What do you do if one 'partner' contributes to more than one book? Sounds like you have a many to many relationship here instead of one to many as a given person can contribute to more than one book and a given book can have more than one partner.

                  • 6. Re: Date range filtering results
                    JoshHarrison

                    Arg, yes, I think I messed up my logic flow, let me describe the solution I need to get to fully (that works perfectly with the portal layout which of course isn't print friendly).

                    What I ultimately want to be able to provide, given the following data

                    Books {B1, B2, B3}

                    Book sales {BS1, BS2, BS3}

                    Partners {P1, P2, P3}

                    Agreements {A1, A2, A3, A4, A5}

                     

                    The invoices iterate through partners.

                    {P1} -> selects via partner id {A1} -> selects via ISBN {B1} -> selects via ISBN and date filter {% of BS1 defined in A1}

                    {P2} -> selects via partner id {A2} -> selects via ISBN {B2} -> selects via ISBN and date filter {% of BS2 defined in A2}

                    {P3} -> selects via partner id {A3, A4, A5} -> selects via ISBN {B1, B2, B3} -> selects via ISBN and date filter {% of BS1 defined in A3, % of BS2 defined in A4, % of BS3 defined in A5}

                     

                    So partner P1 has agreement A1 referencing book B1. B1 has $500 of total sales over the defined time period, of which P1 gets a total of 10%. Balance for P1 is $50.

                    Similarly for P2.

                    P3 has agreements A3, A4 and A5, referencing B1, B2 and B3 respectively. P3 gets 5% of B1's sales of $500 for the defined time period, 5% of B2's sales of $1000 for the defined time period, and 20% of B3's sales of $5000 for the defined time period. For B1, he gets $25, for B2 he gets $50, for B3 he gets $1000, net total we owe him is $1075.

                    • 7. Re: Date range filtering results
                      JoshHarrison

                      Each P# gets their own invoice - with agreements creating line items based on ISBN resulting from the percentage defined in the individual agreement * the total sales for that ISBN in a given period.

                      • 8. Re: Date range filtering results
                        philmodjunk

                        The danger to posting a simplified example of what you are looking for is that the solution to the simple example may not be a good solution for the real problem. The need to refer to data in book agreements to compute amounts for each partner does complicate the set up here.

                        I'm not sure that I have a clear picture of what that report (invoice) will look like: On this report, do you want to see:

                        a) Just the data for one partner and one book

                        b) The data for all partners for one book

                        c) some larger combination?

                        • 9. Re: Date range filtering results
                          JoshHarrison

                          Each partner gets one invoice - all agreements for that partner are listed on that one invoice. So Dr. Seuss would have line items for "Hop on Pop", "Green Eggs and Ham", and "Oh the Places you'll go", as he has agreements on them. I've attached a picture of a sample invoice.

                          • 10. Re: Date range filtering results
                            philmodjunk

                            One question about your example. What does "Distribution partner ASM" mean? Where does that data come from here?

                            Except for that detail, my original suggestion fits and may still fit depending on what you tell me about that one detail listed here.

                            • 11. Re: Date range filtering results
                              JoshHarrison

                              Distribution Partner is the handling institution for the invoices - they're not a key anywhere though. In theory an author could have distribution through multiple distribution partners and they would then get multiple distribution invoices.

                              • 12. Re: Date range filtering results
                                philmodjunk

                                And in what table is this info stored? Does that mean that all your invoices will only list one such distribution partner? If so, then why list it with each book intead of once for the entire invoice?

                                • 13. Re: Date range filtering results
                                  JoshHarrison

                                  It's in the BookAgreement table, IIRC. It was on the old invoices that I'm attempting to recreate, only reason it's there.

                                  It isn't a pretty solution but I've just gone for the "portal per page", and a couple different layouts to handle the range of line items per partner code. If I could get something more elegant going that would of course be preferable, but we'll see what comes up over the next couple days - it can get pretty busy!

                                  • 14. Re: Date range filtering results
                                    philmodjunk

                                    Just needed to be sure that I knew what that data was and where it came from so that it didn't turn out to be the one detail that kept a suggestion by me from working.

                                    Recapping the relationships established here:

                                    Partner>-----BookAgreement----BookDetail------<BookSales via ISBN (one to many)

                                    As stated before you can pull up all book sales records for a given partner and date range, then sort the records by ISBN, Book Title or such to group all sales records by book.

                                    In a list view layout, you can remove the body layout part and replace it with a sub summary layout part with a "when sorted by" field that you used to sort the sales records to group them by book.

                                    The layout objects from To to Tax ID 123 would be fields from Partner. The two dates can be global date fields used in the find to constrain your found set to just this date range.

                                    The author, ISBN, format etc fields can be added to the sub summary part from book details. The "distribution partner" data can be added from BookAgreement.

                                    Qty Sold and Net proceeds can be summary fields totaling fields in the BookSales table. That leave one field not described that is shown on your report: Your Share, at first glance, you can add a calculation field that refers to a percentage from BookAgreement like this: BookAgreement::PCT * Net Proceeds and then a summary field can be added to compute the total of this calculation field and it can be place on the sub summary part. grand totals of all these summary fields may be added to a trailing grand summary to compute totals for the entire invoice.

                                    But I detect a potential problem. You may have several partners, each with a different fraction of the net proceeds specified in book agreement. How do you document the "split" set up for each book partner? I'd create a different relationship for book agreements that you have here and I don't see how the relationships you've described would work here and we need to be able to access different agreement details depending on which partner is specified for the invoice. I know of ways to get this to work but need to understand your current setup.

                                    BTW, do you have FileMaker 11? (May need a filtered portal here.)

                                    1 2 Previous Next