13 Replies Latest reply on Mar 11, 2017 7:57 AM by AdamReed_1

    print sub-summary report based on related record

    AdamReed_1

      Hello,

       

      I'm trying to print a number of sub-summary reports based on a related record, but am instead banging my head against the wall.

       

      I have three tables: client, book, and contract, as well as client_book, and book_contract linking tables.

       

      My aim is to show the contracts for each client based on a particular book.  For example:

       

      William Faulkner

      - THE SOUND AND THE FURY

           - Random House, 1947

           - French, 2012

           - Italian, 2014

       

      - AS I LAY DYING

           - Random House, 1941

           - Japan, 1985

           - Turkey, 1989

       

      I have a layout based on the contract table that works well -- to generate the report for a particular client I just do a find for that client ("client id" in the "client_book" link table) in a dropdown menu, have a sub-summary of "book id" and then sort based on a few criteria to keep things in order.  Manually, it works fine.

       

      I would like to automatically generate these reports for a number of clients, but have thus far been unable to figure out how to automate the find.  What I would like is the script to start at this layout, find the first client, sort the sub-summary and save as a pdf, then move to the next, etc.

       

      Thanks for any help.


      Adam

        • 1. Re: print sub-summary report based on related record
          philmodjunk

          First, make sure that you really need your join tables.

           

          book_contract

           

          Is only needed if you can have more than one book for the same contract and also might have more than one contract for the same book. You know the business where I do not so I can't tell you that you don't need this join table, but if you don't, your solution becomes simpler and easier to work with if you remove that table.

           

          If you DO need that table, it seems to me that you would base such a summary report on this join table, Book_Contract, rather than books. You can include fields from Book and Contract on this layout and group the entries either by book or by contract as needed for what you want for your report.

          • 2. Re: print sub-summary report based on related record
            AdamReed_1

            I'm pretty confident I need the join tables.  One or more authors may write one or more books (sometimes they collaborate, etc.), and one or more books may be included in one or more contracts -- so there are many overlaps across the board.

             

            I switched the sub-summary report to the Book_Contract join table and that seems to work well, too.

             

            I think the pressing question is how, given that set up, to script a find based on author?

             

            It seems to me the logical steps are:

             

            find the first author, sort the records, save the report

            loop

            find the next author (exit after last), sort the records, save the report

            end loop

             

            ...but I have no idea how to find the "first" and "next" author since they're a related field.

            • 3. Re: print sub-summary report based on related record
              philmodjunk

              You can find for author right on the Book_Contract layout to find all records in Book_Contract linked to the author you specify in the find.

              • 4. Re: print sub-summary report based on related record
                AdamReed_1

                Yes, I can do this one by one on the layout itself no problem, just find and select the author from a dropdown list.  It's automating the find so I can run a number of reports without having to manually find each relevant author.

                 

                So I'm looking to script step to find first "author_id" and then next "author_id", etc. until it's run reports for all the authors.  I'm experimenting with GetNthRecords -- does that seem appropriate here?

                • 5. Re: print sub-summary report based on related record
                  philmodjunk

                  Doesn't seem like the right approach. How do you determine what author is the "next" author?

                  • 6. Re: print sub-summary report based on related record
                    beverly

                    I should think a normal subsummary (sort) with breaking to a New Page on each one when printed. For PDF, it separates the authors, but is just one PDF.

                     

                    And yes there is a way to capture the unique IDs & loop the list thus finding each one and creating separate print/PDF runs as needed.

                     

                    Sent from miPhone

                    • 7. Re: print sub-summary report based on related record
                      philmodjunk

                      I think that the presence of two join tables and two many to many relationships may also be complicating this. We started with Book_Contract, but now we are talking about authors--which can be linked both to a book and also a contract.

                       

                      I recommend showing how all of these tables link to each other and then outlining exactly what it is that you want to see in this report.

                       

                      Helping others in the forum here often feels like looking through the keyhole of a house's front door and then being asked questions about how to decorate the bathroom. The very limited view we have can greatly limit the assistance that we can provide.

                      • 8. Re: print sub-summary report based on related record
                        AdamReed_1

                        I was afraid that might be the case -- sorry not to have been more clear.

                         

                        The tables link to each other in what I hope is a fairly straightforward way:

                         

                        Person:person id -- Person_Book:person id / Person_Book: book id -- Book: book id -- Book_Contract: book id / Book_Contract: contract id -- Contract: contract id

                         

                        I hope that part is clear.

                         

                        Just to be clear, the report itself is working perfectly, it's scripting the printing of various reports that I'm having trouble with.

                         

                        The report is on a layout based on the Contract table.  I have a dropdown which shows values from Person_Book: person id.  I have a sub-summary report based on Book_Contract: book id, which shows basic information about the book and contract (title, publisher, language, etc.).

                         

                        So that all works, and by doing a find and selecting an author from my dropdown I'm able to see those contracts (and the associated books) that exist for that author.

                         

                        The trouble I'm having is that I don't know how to do that find in a script step.  Essentially, I'd like to tell the script to somehow replicate the find I'm doing based on Person_Book: person id for all of the values, generating separate for each (I then move them to the appropriate person's file).

                         

                        The reason for this, actually, is that we work with someone from another company who shares those files, but doesn't (and isn't supposed to) have access to our database.  So I'd to run this script frequently to keep her pdfs up to date.

                         

                        I hope this helps, and totally take your point about decorating the bathroom.  If I'm not trying your patience too much, don't hesitate to ask if I'm not providing enough detail or information.

                         

                        Thanks again,

                        Adam

                        • 9. Re: print sub-summary report based on related record
                          beverly

                          "Replicate the find" is a manual or scripted step

                          Modify last find

                          It brings up what you last did and you can change a value or two and perform the find with the new values (all other criteria are the same).

                           

                          Perhaps?

                           

                          Sent from miPhone

                          • 10. Re: print sub-summary report based on related record
                            philmodjunk

                            Person----<Person_Book>----Book-----<Book_Contract>------Contract

                             

                            I'm not sure that I follow this part:

                            Essentially, I'd like to tell the script to somehow replicate the find I'm doing based on Person_Book: person id for all of the values, generating separate for each (I then move them to the appropriate person's file).

                            Do you mean that for a given contract, you need to search back across the relationships and produce what is essentially the same report, but each copy lists a different person linked to that contract?

                             

                            Not sure how you have that working at all if I am reading your descriptions correctly as any record in Book_Contract can link to multiple records in the Person table due to the many to many relationship. Addtional work with either scripting or relationships (or both) can list the associated Book_Contract records for one Person at a time, but you said that you have this report already working--which doesn't seem possible from just the data model shared thus far unless you are setting some variables or fields to data about a given person just for use in this report...

                            • 11. Re: print sub-summary report based on related record
                              AdamReed_1

                              When I first open the layout,  all contracts are shown.  I then do a find for a given person, which then restricts the shown records to those for that person.  I then sort by title, but that's just superficial, really.  It is true that if a contract lists more than one author, the same contract (or book) might appear in more than one report -- but that's absolutely fine for our purposes (in fact desirable).  So that all is working in a straightforward way if I do the find manually.

                               

                              It's just writing a script step that does a find to show only those contracts for a particular author (then moves to the next author, etc.).

                               

                              Hmm, maybe I'm just going about this the wrong way, and the layout should be based on the Person table -- unless it's now more clear and you have a solution perhaps I'll play around with that...

                              • 12. Re: print sub-summary report based on related record
                                philmodjunk

                                I should have reviewed your original post.

                                 

                                You can script a loop that pulls a list of all related persons for a given contract and generates a report for each in turn.

                                 

                                Set Variable [$PersonList ; value: List ( Person::__pkPersonID ) ]

                                 

                                If executed from your Contract layout will put a List of ID's into the variable. You can then do a loop like this:

                                 

                                Set Variable [$PersonList ; value: List ( Person::__pkPersonID ) ]

                                Loop

                                  Set Variable [$K ; value: $K + 1]

                                  Exit Loop If [ $K > Value Count ( $PersonList ) ]
                                    Go to Layout [ "Report" (Contract_Book) ]
                                    Enter Find Mode [ ] --no stored criteria, clear the pause check box

                                  Set Field [ Person::__pkPersonID ; GetValue ( $List ; $K ) ]
                                    Perform Find
                                    Sort Recorsd
                                    Save As PDF

                                End Loop

                                Go to Layout [original layout]

                                 

                                For more on using scripts to perform finds, see:

                                Scripted Find Examples

                                • 13. Re: print sub-summary report based on related record
                                  AdamReed_1

                                  Thank you for this.  I ended up just printing a sub summary of all clients and all contracts, but it works for our purposes.

                                   

                                  The scripted find examples gave me a lot to think about, and I think will greatly improve things.  Thank you!