8 Replies Latest reply on May 13, 2012 7:56 AM by GuyStevens

    Portal to Show all related Sponsors and allow New Donations

    NickUlrich

      Title

      Portal to Show all related Sponsors and allow New Donations

      Post

      This is something I haven't been able to figure out for the life of me.

      I have four tables:

      "Regions" which feeds "Events" and "Sponsors" 

      "Events" and "Sponsors" feed into a join table called "Donations"

      What I'd like to do is have a portal on my Events layout that lists all of the Sponsors (this part is easy) but then also have access to the "donation amount" and "donation date" field from the "Donations" table.

      The way this would be used is somebody would create a new Event, pick the region it belongs to, and then have a portal that lists all of the sponsors/phone numbers/etc assigned to that region. They can call the sponsors, if they donate, they can enter the donation date/amount and it creates a new record in the "Donations" table. If they don't donate, nothing is recorded, and no new record is created. Is there a way to do this without scripting it?

      From there, I can create a relation from the donations table to the events table and sum all donations for that particular event.

      I've attached a simplified screenshot of what's going on.

      If you have any questions, let me know, otherwise, thanks in advance.

      Screen_shot_2012-05-13_at_12.28.33_AM.png

        • 1. Re: Portal to Show all related Sponsors and allow New Donations
          GuyStevens

          This is pretty much your standard case of having a portal, then selecting a record in that portal and then using a second portal to enter data. (Donation Amount, Date)

          I made you a little example file:

          http://dl.dropbox.com/u/18099008/Demo_Files/Donations.fp7

          The idea is really simple:

          You create a relationship between your Events table and your Sponsord table so it only shows sponsors for the selected region.

          Then you select a sponsor. When you do that the sponsors ID gets set into a field in the "Events" table.

          Then you have a new portal that is related to the "Event Id" and to the "Selected Sponsor Id"

          In this new portal you enter donation details.

          This example is really simple but you can take this a lot furter.

          • 2. Re: Portal to Show all related Sponsors and allow New Donations
            NickUlrich

            Hey DaSaint,

             

            Thanks for the reply. I really like how it's done in your sample file... to be honest, I don't think I'd mind doing it that way and it may be a bit cleaner in the long run than what I had in mind.

            Here is a link to a file of what I had in mind, but doesn't work. Any thoughts a way to actually do it?

            https://docs.google.com/open?id=0B54FdfVCzRqfWEUwdnJGdWQzeGc

             

            Thanks again

            • 3. Re: Portal to Show all related Sponsors and allow New Donations
              GuyStevens

              Hey Nick,

              I'm looking at your file now.

              First tips:

              - Make your ID fields number fields in stead of text fields.
              - Give all of your fields their own ID fields. Also tables like your "join" table.

              Your "Join" table is not really a join table. It's more like a line items table.

              But I'm looking right now, will let you know more in a minute.

              Also: For your ID fields. If you are afraid someone might edit or change them you
              - don't have to put them on your layout.
              - You can prevent people from being able to go into table view
              - You could put the ID field on your layout but make it so that it cannot be edited in browse mode. You do this in the inspector under "behavior" - "Field Entry" you deselect browse mode.

              Anyway, just a few pointers

              • 4. Re: Portal to Show all related Sponsors and allow New Donations
                GuyStevens

                One problem is that your portal shows data from the table of sponsors. That's not correct.

                An explanation of your tables:

                - Sponsors: In your table of sponsors you only have sponsor data like their name, adress, info, region, etc. Every sponsor is one record.

                - Events: This is a list of your events. Every event has a certain date and a certain region. Every event is 1 record.

                - Regions: Your regions is a list of all the possible regions. That's all.

                - Your "JoinDonationsEvents" table is actually a "Line Items" table like you would have in an invoice.

                It could also be called an "Events Details" table.

                What is does is create a record for every donation. It notes: The event, The Sponsor, the amount, the date.

                In this way every sponsor can make multiple donations. On different dates, maybe even in different regions if you would want that.

                Every record in your "Join" table is one donation.

                Therefore donations are not stored in the "Sponsors" table, because that would be a problem, what if one sponsor makes multiple donations? You only have one record for every sponsor? You would have to make "DonationAmount1" and "DonationDate1" as fields in the sponsor table, but then you would have to add fields for the other donations, that's not practical.

                Therefore we make another table to list all that. (Your "Join" Table)

                Ok, going back to your file. :)

                • 5. Re: Portal to Show all related Sponsors and allow New Donations
                  GuyStevens

                  I've also changed your "Amount" field into a "number" field and made your date field a "Date" field. They were all set to text.

                  http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/Nick%20Ulrich/TestDB.fmp12

                  This is your file back. I fixed the problems.

                  I also made it so that the sponsord in the dropdown are only the sponsord from the region you specified in the event table.

                  But as you can see, my file has a little more functionality here you can see all sponsors from the region in one portal.

                  If you want I can quickly add that to your file as well.

                  • 6. Re: Portal to Show all related Sponsors and allow New Donations
                    GuyStevens

                    This is what it looks like when I make it a little more like my example:

                    http://dl.dropbox.com/u/18099008/Demo_Files_FMP12/Nick%20Ulrich/TestDB_V2.fmp12

                    You can add a lot of functionality like dropdowns for the regions, buttons to go to the sponsord lists, creation of new sponsors, etc.

                    • 7. Re: Portal to Show all related Sponsors and allow New Donations
                      NickUlrich

                      I would argue that every "Line Item" table is a "Join" table... but not every "Join" table is a "Line Item" table... just the terminology I was taught ;)

                      I sketeched that DB up in a bit of a hurry and missed changing the field types, but for examples sake, it seems like you got the gist of what I was looking for. I'm familiar with using Join/Line Item portals to list multiple donations/line sales/etc, but this was unique for me in the sense that I wanted the portal to list all of the sponsors for the event, and still allow the entering of a single donation for each sponsor for each event (which is why I had the portal based off of the "Sponsors" table - that was the only way I could have it list all of the sponsors assigned to the region).

                      I already implemented your solution into the actual database... and to be honest, I really like having the full list of sponsors available. Next to it I placed a second portal based off of the Line/Join table that shows the full details for the sponsor and donation information (1 Line/No Scrolling so users can't make multiple donation entries for a single sponsor for a single event). 

                      Thanks again for your help!

                      • 8. Re: Portal to Show all related Sponsors and allow New Donations
                        GuyStevens

                        No problem,

                        If you have any more questions, you know where to find us :)