3 Replies Latest reply on May 14, 2015 4:39 PM by philmodjunk

    Pulling records in a date range in a filtered portal

    TarraFarnham

      Title

      Pulling records in a date range in a filtered portal

      Post

      Hello everyone! I am creating a database to do my expenses for our business. I have a table, Transactions, where I enter in my receipts. I have a portal to another table, Itemized Expenses, where I list each individual item on that receipt, category, and cost.

      I tried to do a report with subsummary calculations, but it didn't work. So, I created another report and used filtered portals by category, adding in a summary field to calculate the total by category, which works great. For instance, I have a portal for the category, Office Supplies, and I have a field that totals the cost of all the purchases I've ever made that match the category of Office Supplies.

      My problem is that now I would like to be able to do a date range for these portals. I know you can't have more than one filter on a portal, so I'm not sure where to go with it. Please let me know if you need screen shots or other information.

      Thank you!

      Tarra

        • 1. Re: Pulling records in a date range in a filtered portal
          philmodjunk

          A summary report based on the portal's table with sub summary parts (and possibly correctly defined calculations using GetSummary) would see a far simpler approach. There are many problems using a portal for printing that would thus be avoided as this method doesn't use portals.

          But to limit the records in a portal to a date range can be done via a number of different methods.

          One method is to include the date range in your match fields:

          LayoutTable::CurrentMatchField = PortalTable::CurrentMatchField AND
          LayoutTable::gDateMin < PortalTable::Date AND
          LayoutTable::gDateMax > PortalTable::Date

          or you can modify your portal filter to include

          LayoutTable::gDateMin < PortalTable::Date AND
          LayoutTable::gDateMax > PortalTable::Date

          as part of the portal filter expression to get:

          CurrentFilterHere and
          LayoutTable::gDateMin < PortalTable::Date AND
          LayoutTable::gDateMax > PortalTable::Date

          the gDateMin and gDateMax fields would be defined as global fields to make it easier to enter these values once for all the records used in your report.

          • 2. Re: Pulling records in a date range in a filtered portal
            TarraFarnham

            Thanks Phil! I had a few issues with the subsummary report. I've outlined the steps I've taken below as well as the result:

                   
            • New Report, Printer -> checked "Include subtotals"
            •      
            • Using table "Itemized Purchases", I moved all my available fields
            •      
            • Organized records by Itemized Purchases::Category
            •      
            • Did not sort the records
            •      
            • Specified the field Itemized Purchases::Total Expense as my summary field, summarized by Itemized Purchases::Category and clicked "Add Subtotal"
            •      
            • The Itemized Purchases::Total Expense field is a summary field that has the Total of Itemized Purchases::Cost sorted by Itemized Purchases::Category. I have "running total," "restart summary for each sorted group," and "subtotaled" checked and it will summarize repetitions "Individually"
            •      
            • Result: In form view, I see one individual item and the total (which is the same as the cost because it's only showing that one item). There are 12 separate records, one for each item I purchased. If I use list view, I see all the individual items regardless of the category, and there is no total.

            I've tried different alterations by unchecking "restart summary for each sorted group" and "running total" seperately, but I still don't get what I would like to see. Thoughts on that?