Portal to Portal new record

[margita]

I have a tabbed layout based on Contacts table.

One of the tabs shows two portals. The top portal is based on the relationship between Contacts and Donations (Contacts is the foreign key in Donations). The bottom (second) portal is based on the join table Project Details between Donations and Projects - the join table has Project ID related to table Projects and the Donation ID related to table Donations.

The relationship between table Donations and the join table Project details allows for creation of records in that portal.

What I would like to do is the following:

when I want to create a record in the second portal, I want to be able to click a button in a row in the first portal which will populate a new row in the second portal. Or I could click on a new row in the second portal which will automatically populate the Donation ID field that has been entered in the first portal.

I have seen something similar in the forum here which mentions a script, but it does not say what the script is: http://www.fmforums.com/forum/showtopic.php?tid/96920/post/96920/hl/fundraising/#96920

I hope you can help.

Thanks.

[margita]

As I have not received a reply so far, does it mean that nobody can help me or is it not possible to do what I want?

[Oldfogey]

1. Don't be impatient!

2. It is possible to do what you want to do. The script you asked about does what you want - it "will populate a new row in the second portal." Ditto the second portal button. Presumably you know what steps you need to take to create those new rows/records?

[margita]

I apologise for my impatience. Thank you for replying.

I have been experimenting with this issue for some time and I am afraid I am not succeeding - hence my frustration.

No, I don't know how to create the script - I tried various scripts and it didn't work. Would you be so kind and tell me what the script should be? But I think I also have to make sure that I have the correct relationship.

Hope to hear from you.

[comment]

Can a single Donation be made towards multiple Projects? If so, how do you split it up between them?

[margita]

Thanks for your reply.

No, a single donation is for a single project.

I just need to show that this particular donation is for a certain project. This information is entered into the join table Project Details (between Donation and Projects).

[comment]

If a single donation is for a single project, then why do you need a join table between Donation and Projects? I would think entering a new Donation and assigning it to a Project (by filling in ProjectID in the Donation record) should be quite enough.

[margita]

Yes, this was my first approach. And I am using it at the moment - but I am not sure if it is correct. Each project needs to specify what the amount is used for. So I have a table called Projects - it has several records for project types. This is related to the Donation table by Project ID (foreign key in Donation table). I also have another field in Donation table called Project details which will have more information about the project. Apart from that I also have a field for the donation amount itself which has some details about the amount. On the tabbed layout I have the portal showing all these fields - unfortunately not all the fields fit on one line in the portal, so I've had to create two portals based on the same relationship in order to fit all these fields. This is not exactly satisfactory, but if you think this is all I need, then I will not use the join table. I am just not sure if this is the correct structure for the database. That's mainly because the field called Project Details is not exactly part of the Donation entity.

All the same, I would really like to know how one creates a record in the second portal. I tried to create the following: I created a second table occurrence for ProjectDetails called createProject details. I created a global field in table Donations called gDonationID and related it to the DonationID in createProjectDetails table. And then I got stuck.

If you think that I do not need the join table and that my first approach is correct, then I'll stay with it. But for the future I would really like to know about creating records in the second portal if necessary.

Thank you.

Edited June 11, 2006 by Guest

[comment]

I am afraid you have lost me somewhere along the way. It's hard to tell where data belongs just by the name you have given it. But it seems that 'Project details' is an attribute of a project - so I would expect to find it in the Projects table.

Each project needs to specify what the amount is used for.

That, I think, is a separate issue. Suppose 2 people donated to one project. Is the money kept separate when spending it? IOW, do we buy a computer from Adam's donation, and furniture from Betty's? Or is the project's budget one lump sum?

In the first case, you need to link each expense to a particular Donation record.

In the second case, the expense is related to Project only. And the report to Adam includes ALL expenses - which might even exceed his donation.

[margita]

Thank you for your reply. I will have to clarify this issue:

Donations are related to Contacts by ContactID - ContactID is the foreign key in the Donations table. Every time a Contact makes a donation, it creates a record in the Donation table.

There should be another table called Projects (for the sake of clarification, the word Projects could be substituted by Category). Projects are categorised into categories such as Training, Education etc. When a donation is made, it should specify for which Project category it is for. However, each project category is broken down into

more details which would be different for each donation. Therefore I do need a join table between Donations and Projects. The join table would have DonationID, ProjectID and ProjectDetails that would be specific for that donation amount.

[comment]

I am afraid it's not much clearer. Specifically, this sentence is rather confusing:

each project category is broken down into

more details which would be different for each donation.

I don't know what that means, or why it would imply a join table is needed. I think that details about the donation should be in the Donations table - but again, I don't really know what you mean. Perhaps a specific example would be in order.

[margita]

The Donation table does have Details about the donation itself - it would be about the amount only - for example if it would be paid over two years or something like that. The Project table has the categories as I wrote to you before. But that information about the project is not enough. There is also specific information under the Project type for example Project: Training; Project area: Resources, Travel; Project details: resources for one year, travel to training centres.

Project area and project details would be in the join table and would be specific for that project and donation.

If I were to have all this in the Project table, I would be duplicating records for Project categories (Training, Education etc.).

I was really hoping you would help me with creating a record in the second portal. Will we be coming back to it?

[comment]

How can I - when I don't understand your relationships? You still haven't told us what information would go into the join table.

A join table between Donations and Projects means either that a single Donation can be linked to multiple Projects (which you've ruled out), or that a single Donation can be linked to the same Project by more than one link (which doesn't seem to make much sense here). Can you give a REALLY specific example?

[Oldfogey]

I second that, Comment. I must say I didn't elaborate on my original suggestion because I couldn't see what the structure was doing or why.

A small suggestion, Margita. I think you might be confusing yourself. Forget the phrase 'new record in the other portal'; you don't create records in a portal, you create records in a table. The portal simply(?) displays some of the records in the table.

[margita]

Thank you both for your comments.

The donation issue with regard to projects is not straightforward. One single donation may or may not involve many projects.

In any case I have worked out what I needed to do on the basis of the post I referred to earlier.

Thank you both for your time.