6 Replies Latest reply on Feb 14, 2011 3:54 PM by kSpieler

    Calculation in Portals

    kSpieler

      Title

      Calculation in Portals

      Post

      I have a table that is a list of Members. It has a keyfield "MemberID," there is a field in the same table named "sponsor."  For each record, the "sponsor" field contains the "MemberID" of the person who is the Sponsor of that member.

      I have a layout that shows all of the information for each member - broken into tabs - with a portal on one tab that I want to show a list of all of the members the Record Member sponsors. But I can't seem to get it to work and I cannot figure out what I am doing wrong.

      I do have another tab in that layout with a portal where I was able to show all the Groups a specific Member sponsored ... but the groups are in a different table, and it was fairly easy to have it work.

      I am totally teaching myself as I go here ... so I'm sure I can't see the forest through the trees!  Any help would be appreciated.

        • 1. Re: Calculation in Portals
          philmodjunk

          If I understand your description, you'll need a "self join" relationship that links your membership record to itself. Open Manage | Database | Relationships. Select the table occurrence for Members by clicking it. (A table occurrence is one of the "boxes" in this window.) Click the button with two green plus signs on bottom left to make a second table occurrence of your Members table. Rename it "SponsoredMembers" and set up this relationship:

          Members::MemberID = SponsoredMembers::Sponsor

          Now you can place a portal to SponsoredMembers on a tab of your Members layout to list all members that member has sponsored.

           

           

          • 2. Re: Calculation in Portals
            kSpieler

            Thanks for such a quick response!

            ... but it didn't work! ... I added the table occurance, linked the MemberID field in the first TO with the Sponsor field in the new TO (renamed to SponsoredMembers) ... and still got nothing.

            Ok, went and turned off the filter on the portal ... now I get a replicated listing of the Record Member, listed the same number of times as there are records with the same sponsor number.

            • 3. Re: Calculation in Portals
              philmodjunk

              Hmmm, never mentioned a portal filter as it isn't needed here.

              I'm assuming that for MemberID =1 a table of the two members sponsored by that member would look like this:

              Name        | MemberID | Sponsor
              John Smith | 23            |1
              Jeff Bridges | 38           |1

              If that's the case, then a portal to SponsoredMembers on a layout based on Members will list John Smith and Jeff Bridges when the current record for the layout is for the record where MemberID = 1.

              Is this how you've structured your data?

              • 4. Re: Calculation in Portals
                kSpieler

                Heh, sorry ... forgot that the filter method was one of the bazillion things I tired before I threw my hands up and surrendered to asking those much wiser for help : )

                The table looks more like this:

                MemberID       Name                  Sponsor
                #23                  John Smith           #15
                #38                  Jeff Bridges          #15
                #15                  Jane Doe              #10

                So that on Jane Doe's record the layout should show:

                    MemberID: #15
                    Member Name: Jane Doe

                With a portal showing:

                    Members Sponsored:
                        #38  Jeff Bridges
                        #23  John Smith

                But what I'm getting is:

                    MemberID: #15
                    Member Name: Jane Doe

                With a portal showing:

                    Members Sponsored:
                        #15  Jane Doe
                        #15  Jane Doe

                Does that make better sense?

                • 5. Re: Calculation in Portals
                  philmodjunk

                  That's exactly the structure I listed, but with 15 instead of 1.

                  Enter layout mode and double click the MemberID, and name fields in this portal. In the specify fields dialog that appears, select SponsoredMembers from the drop down at the top, then make sure that the correct field is highlighted for the data that you expect. Also double click the portal background to bring up Portal Setup... and make sure that SponsoredMembers is selected in "Show Records From" in this dialog box.

                  • 6. Re: Calculation in Portals
                    kSpieler

                    Apologies, I realized that after I had posted reply and re-read everything...

                    But, yippee! It works now, thank you very much! (you were correct, I still had the portal fields pulling from the main TO )

                    Thank you again for your help!