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:
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.
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?
I'm looking at your file now.
- 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
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. :)
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.
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.
This is what it looks like when I make it a little more like my example:
You can add a lot of functionality like dropdowns for the regions, buttons to go to the sponsord lists, creation of new sponsors, etc.
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!
If you have any more questions, you know where to find us :)