5 Replies Latest reply on Aug 9, 2015 2:54 PM by AndyPullen

    Cartesian portal including a related field

    AndyPullen

      Title

      Cartesian portal including a related field

      Post

      I'm stumped, any help would be hugely appreciated! I have a database for tracking a monthly budget, with tables for month and related tables for cashflow (income, budgeted, spent) and for categories. The idea is to add a budget line item and assign it a category. And when money is spent, that would be recorded and also assigned a category.

      What I'd like to do is on the month details layout, display a portal showing all categories, but include fields on each category row that show related cashflow sums (budgeted, spent, remaining). I'm not sure how to setup the relationship to show all records in the portal but the cashflow fields with related data for the given month.

      Screen_Shot_2015-08-08_at_9.22.35_AM.png

        • 1. Re: Cartesian portal including a related field
          philmodjunk

          To show them in the portal to All Categories, you'd link in table occurrences of those tables (Spent? and Income?) to the All Categories table occurrence. Then you can include fields from those added table occurrences in your portal row. Summary fields in the Spent/Income tables or calculation fields using Sum in the Categories table can calculate the needed totals.

          You might also consider recording income and expenses in the same table with one field for income and one for expenses. This enables you to add a calculation field and a summary field to show a running balance for either all transactions or just the transaction for a given category.

          If interested, see this thread: https://dl.dropboxusercontent.com/u/78737945/AccountingLedger.fmp12

          • 2. Re: Cartesian portal including a related field
            AndyPullen

            Very helpful, and it makes sense to me that this would work, but I must be doing something wrong. I've added these additional TOs off the All Categories TO but for some reason, the fields from those table occurrences are displaying only data from the earliest month, and not the related data for that month. In other words, I have August with a $100 budget for Utilities, and a September record with $200 in the same category. Both months show the category has a $100 budget (since August was the first record I created).

            • 3. Re: Cartesian portal including a related field
              AndyPullen

              Also, the idea of running both expenses and income together through the same table is a good one... thanks for the example file!

              • 4. Re: Cartesian portal including a related field
                philmodjunk

                There is nothing in the relationship set up to match records by month.

                To repeat from my last post:

                Summary fields in the Spent/Income tables or calculation fields using Sum in the Categories table can calculate the needed totals.

                But these will give you totals for each category over all records for that category. To narrow the number of months down to those for a given month, you'd need another match field in the relationship such as:

                All Categories::gMonthID = Spent Total::Month ID Match AND
                All Categories::Category ID = Spent Total::Category ID Match

                gMonthID would be defined as a global field in All Categories and you'd use the OnRecordLoad Script trigger to perform a script that set's gMonthID to Months::Month ID. (This works if you are using a Form view layout to Months.

                An alternative layout design that you might consider is a list view layout based on the combined table of transactions sorted by month, category, transaction date to provide a "ledger like" view of your transactions grouped by month and category.

                • 5. Re: Cartesian portal including a related field
                  AndyPullen

                  Thanks so much. Hugely helpful, as always.