1 2 Previous Next 17 Replies Latest reply on Sep 21, 2012 10:36 AM by philmodjunk

    copy id to selected records in a filtered portal

    AdamReed

      Title

      copy id to selected records in a filtered portal

      Post

           I'm trying to assign unpaid expenses (one or more) to a particular payment (only ever one), so I have a portal in which I filter out the paid expenses, and I would now like to have set up a button to apply selected expenses (usually all, but there may be some cases in which a particular expenses won't be applied) to a particular payment.

           The expenses are related to the person and now to the payment through "payment id".  I've been trying to "set field" using a button in the portal, but so far that hasn't done the trick.

           Thanks for any advice,

           Adam

        • 1. Re: copy id to selected records in a filtered portal
          philmodjunk

               Set field from a button inside the portal row should work, if the the value being set to the field in the portal is in a global variable, global field or there is a valid relationship linking the portal's table to the table where the value being referenced is stored and then only if your set field expression is set up to correctly use that relationship.

               I suspect that it is a case of setting up a relationship that will correctly link the two records.

               What relationship links the table storing the expense to the table storing the payment to which you want to link it? Is this a many to many relationship os that a payment can be applied to many expenses and so that an expense can be linked to many payments?

          • 2. Re: copy id to selected records in a filtered portal
            AdamReed

                 I've linked "payment id" in the payment table to "payment id" in the expense table.  Expenses are also linked to people, which are linked to books, then contracts, and contracts are linked to payment (so I've created a second instance of the expense table as there is more than one path between the two -- I think that's set up correctly).  I haven't allowed the creation of new records on either side as I don't want to create new records through this relationship.

                 This should be a one-to-many relationship, as many expenses can be related to a payment, but only one payment (they should never be applied more than once or we'd be charging the client multiple times for the same expense).

                 The set field button is a calculated result -- the target field is "payment id" in the second instance of the expenses table, and the calculation is simply "payment id" in the payment table.

            • 3. Re: copy id to selected records in a filtered portal
              philmodjunk

                   The details are critical.

                   Is the portal to expenses or the "second instance of the expenses table"?

                   And is the layout based on "Contract"? If so, what match fields link "contract" to "expenses 2"?

                   And in what field is the value referenced in the claculated result that is set to (I assume) your expenses table record in the portal? Is it a field in Contract?

              • 4. Re: copy id to selected records in a filtered portal
                AdamReed

                     The portal shows records from the "expenses" table (rather than the second instance).  The button does set field to the second instance (which I've named "expenses payment" but will here call "expenses 2").

                     The layout is based on a table "royalties".  The full, rather long, path of relationships is:

                     expenses:person id -- person author: person id -- person author book link:person id -- person author book link:book id -- book: book id -- book contract link: book id -- book contract link: contract id -- contract: contract id -- royalties: contract id -- royalties: payment id -- payment: payment id

                     As I said, I've also linked expenses 2: payment id -- payment:payment id

                     The field of the value referenced in the calculated result is payment:payment id.  It is set to expenses 2:payment id

                     I'm happy to post a diagram if that would be helpful?

                • 5. Re: copy id to selected records in a filtered portal
                  philmodjunk
                       

                            The portal shows records from the "expenses" table (rather than the second instance).  The button does set field to the second instance (which I've named "expenses payment" but will here call "expenses 2").

                       That isn't likely to work here as FileMaker will attempt to trace the relastionship to go from the current portal row in expenses to a related record in expenses payment. Both the expenses field and portal should be from the same "instance" (called a table occurrence).

                       You've described relationships but not the layout on which your created the portal. The layout's table occurrence establishes a "context" that is critical to how this must be set up in order to work.

                       Screen shots of Manage | database | relationships will reveal a lot. Also a screen shot of your layout including the following details can be helpful:

                         
                  1.           shoot it in layout mode
                  2.      
                  3.           Capture enough of the layout to show the layout name and "table" specified for it in the tool bar.
                  4.      
                  5.           Include the full portal so that I can see what's in the lower left corner of it.
                  • 6. Re: copy id to selected records in a filtered portal
                    AdamReed

                         What you write makes sense -- the 'payment' layout is, in fact, based on the 'royalties' table.

                         I should say that at this point that I'm not at all wedded to this route as a solution.  Another layout that pops up, etc. would be absolutely fine.  In the end all I need to do is present the user with a list of unpaid expenses, and allow her to select which one's she'd like to apply to a particular payment.  When she prints the layout only those expenses applied to that payment should appear.  (So if there's a better way...)

                         I've attached the manage database image (I didn't include the entire database, so let me knowi f you need more).

                         I'll send the layout next.

                    • 7. Re: copy id to selected records in a filtered portal
                      AdamReed
                      /files/f52a1ad535/payment_layout.png 590x801
                      • 8. Re: copy id to selected records in a filtered portal
                        philmodjunk

                             Why do you have this join?

                             Payment::payment_id= Expenses Payment::Payment id

                             Or are Payment and Expenses Payment two separate tables as they might well be given the set of relationships that you have defined here?

                             In otherwords, if you hover the mouse over the upper left corners, of these two, do you see the same name appear as the data souce table?

                             Your Layout is based on Royalties with a portal to Expenses.

                             This appears to be the relationships that matter for this issue:

                             Contracts----<Royalties>----Payment-----<expenses payment

                             But there's no table occurrence (Boxes in your relationship graph are called table occurrences) in your relationship graph named expenses yet your portal is to a table occurrence of that same name. Thus, we do not have all the needed information showing here.

                              

                        • 9. Re: copy id to selected records in a filtered portal
                          AdamReed

                               I've attached a more complete picture (some of this may not be relevant here) -- hope this helps.

                               I'd made the join payment:paymen_id = expenses payment:payment_id thinking that expenses should be related to payments via the payment_id.  The table expenses payment is an occurence of the expenses payment.

                          • 10. Re: copy id to selected records in a filtered portal
                            philmodjunk
                                 

                                      The table expenses payment is an occurence of the expenses payment.

                                 How's that again? Might Expenses Payment be and occurrence of Expenses?

                                 I'm trying to trace the relationship from Contract to Expenses and I think that you have:

                                 Expenses Payment>------Payment-----<Royalties>----Contract----<Book Contract Link>------book----<book author link>------Person Author----<Expenses

                                 From what I see here, the portal to expenses is not the right portal for what you want to do. If I am right that Epenses Payment has the same data source table as Expenses, then a portal to expenses payment might be made to work here.

                                 What does the links From contract to royalties to Payments to expenses payment represent?

                            • 11. Re: copy id to selected records in a filtered portal
                              AdamReed

                                   Yes, sorry Expenses Payment is an occurrece of Expenses.

                                   I had the same thought about basing the portal on Expenses Payment, rather than Expenses.  For some reason, however, I'm unable to view any expense records unless the portal is based on Expenses.

                                   One of the terms in the contract is the royalties (or payments due) -- but there are many other terms.  Royalties is it's own table as one contract can have multiple payments due.  I hope I've understood -- does that answer your question?

                              • 12. Re: copy id to selected records in a filtered portal
                                philmodjunk
                                     

                                          I had the same thought about basing the portal on Expenses Payment, rather than Expenses.  For some reason, however, I'm unable to view any expense records unless the portal is based on Expenses.

                                     Take a look at your relationships. The link to expenses is based on person id, but the link to Expenses Payment is based on payment id. Odds are that you have a valid relationship to expenses--so they appear in the portal, but do not yet have a valid relationship to Expenses Payment at the moment you try to perform this script--so the script fails to achieve anything.

                                     Having reviewed a few details here, the portal to expenses, since it is filtered, may not be such a bad approach here if we can get the right script in place here. But I still have unanswered questions that could easily change my opinion here.

                                     

                                          does that answer your question?

                                     Not really. I want to know why you have to link payments and expenses to royalties in this specific fashion. What (if these were just paper records) is the connection between them? Why do you need to click a button in this portal in the first place? What is that action supposed to do for you in terms of your records?

                                • 13. Re: copy id to selected records in a filtered portal
                                  AdamReed

                                       Do I need to also link the second occurence of the expenses table to person_author?  (I thought those links were carried over to different occurences of the same table.)

                                       Regarding how we think about contracts, payments, expenses, etc.:

                                       Expenses are incurred by clients at various times (when we mail a package, buy books for them, etc.)  They're unrelated to a specific book or contract, however we only charge clients for expenses when we're issuing a payment to them (we just deduct the amount of the expense from what we're paying to them -- we'd never ask them to write us a check).  Payments and payments are always linked to contracts.  So I do need to link payments to contracts, but expenses to people.  I've created a separate table for "royalties" (these payments are royalty paymets), as publishing contracts typically contain 2-4 payments of a standard type (money due on signature of the contract, on delivery of the manuscript, on publication of the book, etc.).

                                  • 14. Re: copy id to selected records in a filtered portal
                                    philmodjunk
                                         

                                              (I thought those links were carried over to different occurences of the same table.)

                                         They are not.

                                         

                                              So I do need to link payments to contracts, but expenses to people.  I've created a separate table for "royalties" (these payments are royalty paymets), as publishing contracts typically contain 2-4 payments of a standard type (money due on signature of the contract, on delivery of the manuscript, on publication of the book, etc.).

                                         Which means that some payments link to royalties, but not all payments. This makes a Royalty one kind of payment and something that can be recorded in your payment table, it doesn't look like you need a royalties table and also a payments table. Those payments that are due to royalties received can be linked by contract ID to the appropriate contract record. Those payments that are not royalty payments will have this field empty.

                                         How do you manage these expenses for cases where a book was co-authored by more than one author?

                                          

                                          

                                    1 2 Previous Next