As you've probably discovered by now, you can't put a portal inside your portal.
I think you have these relationships:
author::authorID = Expenses::authorID
author::authorID = book::authorID
book::BookID = contract::BookID
contract::ContractID = payment::ContractID
Fields that have the same name as the table are Primary Keys defined as auto-entered serial numbers and their matching fields of same name in another field are foreign keys defined as simple number fields.
Seems like you need a join table between author and book if you ever have more than one author for the same book--which would appear to match your example where Tom and Beth are both authors of the same book?
I should have mentioned I do have a join table, yes. (There are actually quite a few other tables, but I thought these were the relevant ones.)Yes, I do know I can't put a portal in a portal -- can related portals be displayed on the same layout? Am I barking up the wrong tree?
It's vitally important to trace the relationships between the table(s) represented in your example. That would include at least one join table, probably more--if a single payment needs to be split amongst different authors of the same book...
I'm not fully sure what your example represents, Two authors of the same book with different expense records or something else?
What is the purpose for showing these expense records on a layout based on payments? (big picture really helps).
I've posted an image of the database layout here: http://www.joyharrisliterary.com/db.png
The big picture is that expenses are incurred by individual authors, but payments are made by contract (contracts are for books, but a book may have multiple contracts (English, French, audio, etc.). When payments come in they come in because of a date or event listed in the contract -- for example when the authors sign the contract they get the first part of their money. So two authors might sign, get that money (which we track as one payment because legally it is), and then we pay them separately, but list it on the same form. Thus, we'd like to deduct their expenses at that time -- in the example above, yes, Tom and Beth were working on the same book, received their payment, and we deducted their respective expenses from their share of the payment.
I have, incidentally, thought that an improved system might begin with one payment, but then generate multiple payment sheets for multiple authors. So we would track payment 1, and then generate two forms, one for Tom and one for Beth, listing the single payment on both but just dividing it between them. I can see that resolving this expenses issue, too -- does that make more sense? (Either way is ok for us, I think.)
I think that makes better sense. Perhaps you are an agent for the authors here? If so the contract is paying you and then you pay the authors after expenses are deducted?
In that case you have two different tables as you describe, one for the contractually mandated payments to you and then the divided up payments with expenses deducted payments to the individual authors.
another option would be to use a single portal with your example date arranged like this:
Tom $50 International Postage Total: $70
Tom $35 IRS fee Total: $70
Beth $36.82 Dining Total: $50.82
Beth $14.00 Transportation Total: $50.82
Yet another option is to not use any portals at all but to arrange the report into a summary report with sub summary parts and sub totals.
After thinking about this I've decided to generate one payment record for each client involved in a payment (rather than one record per payment, listing multiple coauthors in the same record).
I am a scripting novice, and had been using a button with "Go to related record", which worked fine. Now I assume I will need to use a script of essentially the following parts:
1 look at the first portal row
2 go to the related record
3 look at the second portal row
4 if there isn't one, end
5 if there is one, go to the related record
6 loop to step 3
Does this sound essentially right? I'd appreciate any help with the actual lines.
Thanks very much,
Rather than have my script interact with portal rows, I'd use one of two alternative approaches to loop through the related records:
Use a Go To Related Records step to pull up all the records shown in the portal on a layout based on that portal's table occurrence and loop through them there. (be careful to either check for the absence of related records or the error produced when a GTRR step executes when there are no related records or your script might do some drastically wrong things to your records in the parent table.)
Or use a counter variable and GetNthRecord in the loop to refer to the related records directly without referring to portal rows. In an unfiltered portal,
GetNthRecord [PortalTableOccurrence::Field ; 3 ]
will refer to the 3rd record listed in the portal, when evaluated on the layout where you have the portal.
These methods avoid issues that can occur at a later date when you might decide to modify the design of your layout, adding a second portal or changing a portal's object name--both of which are changes that will break a script that interacts with the portal while these alternate approaches will not be as vulnerable.
As I have a number of other pieces of information I'm pulling (the portal only handles two of about 8 fields), is GetNthRecord is the better approach? (My instincts tell me that, but of course...)
It makes no difference in that regard as either method would work the same when the need arises to access data in fields not listed in the portal. The alternate approaches are just a little bit less "brittle" as they don't rely as much on specific design details in your layout.
Ok, thanks, I'll investigate.