10 Replies Latest reply on Sep 16, 2011 10:55 AM by AdamReed

    portals within portals

    AdamReed

      Title

      portals within portals

      Post

      I would like to list mulitple expenses for multiple clients (related to the same project) on the same layout.

       

      I have the following structure:

      table:expenses

      table:author

      table:book

      table: contract

      table:payment

      (All tables have a primary id field.)

       

      I've established the following relationships through their primary ids:

      expenses and author

      author and book

      book and contract

      contract and payment

       

      I am displaying multiple authors on the payment layout through a portal. This works fine.  The difficulty is also displaying the related expenses in another portal or portals (in the case of more than one author).  So I'd like it to look like:

       

      Tom

      - $50 International Postage

      - $35 IRS fee

       

      Beth

      - $36.82 Dining

      - $14.00 Transportation

       

      That sort of thing.  I'd then like to be able to total the respective clients' expenses and add them up on that layout in calculated fields.

      I am a relative beginning with filemaker, and would be grateful to be pointed in the right direction.  Please let me know if more information is necessary, but I expect my question is fairly general?

       

      Thanks very much,

      Adam

        • 1. Re: portals within portals
          philmodjunk

          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?

          • 2. Re: portals within portals
            AdamReed
            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?
            • 3. Re: portals within portals
              philmodjunk

              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).

              • 4. Re: portals within portals
                AdamReed

                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.)

                • 5. Re: portals within portals
                  philmodjunk

                  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.

                  • 6. Re: portals within portals
                    AdamReed

                    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,

                    Adam

                    • 7. Re: portals within portals
                      philmodjunk

                      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.

                      • 8. Re: portals within portals
                        AdamReed

                        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...)

                        • 9. Re: portals within portals
                          philmodjunk

                          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.

                          • 10. Re: portals within portals
                            AdamReed

                            Ok, thanks, I'll investigate.