6 Replies Latest reply on May 14, 2009 2:31 PM by Shari

    incorporating years



      incorporating years


      I have a portals with information regarding dues and publications ordered on a member layout.  How can I have a history for each year on the row?  Every way I've tried the year either stays the same on each portal row or will not populate for each member record.  I hope this makes sense to someone.

        • 1. Re: incorporating years

          The devil is in the details.


          Please provide more detail about your tables, fields, relationships so that we can understand what you are trying to do.


          What do you mean by "history for each year on the row"? Can you post an example?

          • 2. Re: incorporating years
               I have a memberhship database so my initial table is "Member Info"  with contact information and the member ID.  I have created additional tables of "Publications" and "Dues" and linked them through the Member ID.  I am creating a layout with Member Info as a consistent view and have tabs with Pubs and Dues.  On each tab I have a portal.  In the Dues portal I have created columns to view "Date Paid" "Amount Paid" and a Yes/No column for an additional bit of information.  I would like each row to represent a year to be able to access their history of dues but I can't get the year field to be represented in each member record or if it does that then all the years end up changing to the last entered.  If I make the year a text box then when my portal rows are added I have to redo all the boxes.  Does that help at all?  Is this to detailed a question?
            • 3. Re: incorporating years

              "Is this too detailed a question?"

              Not at all. Here's how I understand your question, let me know if I get it wrong.


              In your dues portal you have multiple entries all dated by the date the dues were paid or received. There may be several records with different dates but with the same year. You want the total dues paid for all the dues entries of the same year. In the portal you might see:


              Date:    Dues   Year total

              1/1/08   $20    $40

              6/1/08   $20    $40

              1/1/09   $30    $60

              6/1/09   $30    $60


              If I am interpreting your request correctly, you can do this with an additional relationship and a calculation field that makes use of the sum() function.


              In Dues, add another field, make it a calculation field that returns a number:  Year(Date Paid), let's call it YearPaid.

              Click the relationships tab and make our new relationship. This type of relationship is called a "self join".

              Find your dues table and drag from YearPaid out a ways from the Table Occurrence box and then back to this same field. A dialog box will appear to ask you to name the new Table Occurrence (also called a Table Instance in this dialog). Name it DuesSameYear. Now drag from Dues::Member ID to DuesSameYear::Member ID to add this pair of of fields to the relationship. This relationship will match all records for the same member with the same year as that recorded in the Date Paid field.


              Click the Fields tab to return to the field definitions section and add a new calculation field: Sum(DuesSameYear::Amount Paid), set it to return number and call it DuesYTD or whatever you'd like.


              Return to your layout's dues portal and add the Dues:: DuesYTD field to your portal.

              • 4. Re: incorporating years

                I want my dues portal to look like this:

                Year       ?       Date Pd       Amount

                2009     Yes     01/08/09     $40.00

                2008      No     06/13/08     $40.00

                2007     Yes    10/01/07      $60.00


                I've created the ?, Date Paid and Amount columns as repeating fields, but that won't work with the years as I want this to exist in each record not just the one I'm entering.


                Thanks so much for your help.

                • 5. Re: incorporating years

                  "I've created the ?, Date Paid and Amount columns as repeating fields"

                  That confuses me. Repeating fields aren't normally used in a portal as the portal removes the need for the repetitions. Perhaps there is some confusion in terminology here.


                  Did you use the portal tool to create a portal on the tab, were each row is a record in the Dues table?


                  Assuming that's the case, forget all the relationship instructions and either put a copy of your Date paid field in column one, but format it to show only the year, or define a calculation field as Year(Date Paid) in your Dues table and place it in column 1.

                  • 6. Re: incorporating years
                       I'm an idiot.  Thank you for helping me.  I didn't need the repeating fields at all!  You are a big help. I'll probably be back, if you see my name, please help me!  :smileyhappy: