1 Reply Latest reply on Jan 11, 2012 1:30 PM by philmodjunk

    Portal to Portal Relationships



      Portal to Portal Relationships


      I have created a layout (Layout A), which tracks expenses per month using a portal built into the layout.  Every time a new expense is added to the portal, an expense category is selected via drop down menu.  Each new record in the layout is a new month of the year.

      I would like to create a 2nd layout (Layout B) which would track expenses (by category) for the year.  I have 12 different expense categories.  On the left hand side of the page i have a label and field for each expense category.  I'm planning on setting up 12 different portals for each expense category to show the all expenses per category for the year.

      Is there a way to set this up so that when i enter a new expense into layout A, it is automatically entered into the correct expense category portal of layout B?

      I understand that I could create just 1 portal in layout B, and enter a category for each expense and then have the fields on the left side of the screen show the totals per category, but i like to see the categories sub-summarized in Layout B.

        • 1. Re: Portal to Portal Relationships

          I gather that you have this relationship:

          Months----<ExpenseDetails   (LayoutTable----<PortalTable )

          Note that layouts do not store data, tables do. Layouts provide a means, via a table occurrence (box in manage | database | Relationships) to access the data in one primary table plus any number of related tables.

          First the specific question:

          The trick is to keep the expense details in the ExpenseDetails table, but use your new layout to access the data in the new ways that you want.

          You need a field in MonthlyExpenseDetails that reports the year. If you have a date field that records the expense date, this calculation field, cYear, can return the year: Year ( ExpenseDate ). Then you can add a Years table and related it like this:

          Years::Year = ExpenseDetails::Year

          YOu can then add 12 portals to expenseDetails on a layout that specifieds "Years" in Layout Setup | Show Records From. Each portal can be given a different portal filter expression to filter out all related records except those of a specified category.

          Now to suggest an alternative.

          If you build a report layout based on expense details, you can produce a summary report that looks like this:

          Year: 2011
                 category 1
                       Expense Details
                 Category 1 total

                 Category 2
                       Expense Details
                 Category 2 total

                 and so forth for each category
                 Category 1
                 and so one for each category for each month

          2011 totals:

          This report can report data for any range of dates for any number of existing categories. Adding or removing categories can be done without having to redesign the report layout. This type of report is done by basing the report layout on ExpenseDetails and using summary fields inside of sub summary parts to group and sub total the information.