12 Replies Latest reply on Apr 11, 2015 9:48 PM by WalterWhitmanMoore

    HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES

    WalterWhitmanMoore

      Could you help me with checkboxes and reports? I use checkboxes in one field; sometimes, two or more are checked. I want a report that will show me, for EACH checkbox that is checked, ALL of the records for which that box is checked. You with me so far? The problem is, when I set up a report to sort records based on each checked box, the report shows only one record per checked box. What am I doing wrong?


      The tables I'm using are set forth in the picture below. In particular, I want to print a report showing, for each "Issue," all of the facts that related to that issue. The "Issues" field in the "Facts" table is where I have the checkboxes. There is a checkbox in that field for each "Issue" that appears in the related "Issues" table. Thanks in advance for helping the new guy.


      Oh, and by the way, I was able to set up a nice portal report on the computer, which lets me see all of the facts relating to a given issue, but I really need something I can print out, without having to scroll.


      Screen Shot 2015-04-11 at 10.34.22 AM.png

        • 1. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
          siplus

          walterwmoore wrote:


          Oh, and by the way, I was able to set up a nice portal report on the computer, which lets me see all of the facts relating to a given issue, but I really need something I can print out, without having to scroll

           

          This is the easy part: use a list layout, showing facts selected with a go to related record from the issue table or with a specific find. A checkbox-formatted field is in fact a list field, containing 1 to n values separated with ¶. So go to your list layout, sort your facts records by issue (a sub summary helps here, in your case only for the title because there are no sums, but it's nice to have) and print.

          • 2. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
            erolst

            I think checkboxes are not what you should use … provided I did understand your description correctly.

             

            Consider this simple example: you have a table “Shapes”, with a field “type”, and a field “colors”, formatted with a “Colours” value list .

             

            If in a record of type “Square” you check “red¶blue¶green”, in another of type “Sphere“ check "red¶white¶blue”, and in “Triangle” there is "red¶green”, you expect a report “Shapes by Colour” to look this:

             

            RED

              Square

              Sphere

              Triangle

            BLUE

              Square

              Sphere

            GREEN

              Square

              Triangle

            WHITE

            Sphere

             

            which means there must be 8 lines, to summarize a total of 8 colour occurrences; but you only have 3 shape records to summarize …

             

            If, on the other hand, you use an additional table:

             

            Shapes  --< ShapeColours

             

            then, instead of writing values into a field, you can create related records – then use that table to create your report, because in the above example, you would indeed have created 8 ShapeColours records.

             

            PS: I don't notice any primary/foreign key fields in your se setup.

             

            PPS: Please don't use allcaps in your posting titles; in the Web, that's the typographical equivalent of screaming your words, and consequently is considered rude …

            • 3. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
              siplus

              In my fervid imagination I see the OP as a policeman who is trying to build a checklist template to be printed out and filled in by investigators; he left away on purpose some details, keys, and primary tables (like delicts)  - but they exist, even if he did not mention them explicitly. Then again I might be wrong...

              • 4. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                WalterWhitmanMoore

                I'm actually a trial lawyer trying to keep track, efficiently, of what evidence relates to which issues. My checkboxes use a value list based on the Issues table.

                 

                Sent from my iPhone.

                • 5. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                  erolst

                  walterwmoore wrote:

                  what evidence relates to which issues. My checkboxes use a value list based on the Issues table.

                   

                  So an Evidence (though I think it would be “type of evidence”) can occur in multiple Issues, and an Issue can have multiple Evidences: this is the join table “mantra”, and leads to a structure like:

                   

                  Issues --< EvidencesInIssues >-- Evidences

                   

                  where instead of having a list in a field, the list is represented by related join table records; base your report layout on that table. Also, you can have it both ways: create an IssueByEvidence report, or an EvidencesByIssue report.

                   

                  Basically the same structure as described above, with the semantical difference that that was a “child” table, while now we have a join table (since each record is a child of two parent entities, not just one).

                   

                  Only thing left to know is that this requires (a bit of) scripting, since instead of checking/unchecking a box, you now must add/delete related records. 

                  • 6. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                    WalterWhitmanMoore

                    First of all, thank you all for taking your very valuable time to share your insights. I REALLY appreciate it.

                     

                    Second, Erolst, you are right to note that any given fact -- the Evidence table is a list of facts -- can relate to numerous issues, and, conversely, any given issue can relate to many different facts.

                     

                    Allow me to be more specific. During a trial, we call one witness, elicit his or her testimony about various facts, then call the next witness, and so on. After all the testimony has been elicited, we give our closing arguments. That is when we need to explain to the jury how each issue -- e.g., formation of a contract, performance by the plaintiff of his or her duties under the contract, breach by the plaintiff and resulting damages -- is supported (or contradicted) by the facts elicited from each issue. So, to make sure I cover everything, I want my database to help me keep track of how I'm going to prove each fact (e.g., testimony from a given witness, or a particular document), and how I'm going to use those facts to establish each issue in closing argument. (There's a picture of the whole shebang below.)

                     

                    Too much information? Sorry. Thought you might like to know.

                     

                    Anyway, thank you, and I'm going to try to apply your suggestions now, and I'll report back. (I've been at the dentist all day, and they hate it when you try to work on a database while in the chair.) Thanks again! You rock!

                     

                    Screen Shot 2015-04-11 at 2.32.24 PM.png

                    • 7. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                      WalterWhitmanMoore

                      I understand the part about all caps, but the rest went over my head. (I generally use all caps as a headline, BTW, not to shout.) I'll read up on "primary/foreign key fields." I don't know what that means. Ditto re creating a table like "ShapeColours." A given lawsuit -- that's what this database is for -- can involve thousands of facts and dozens of issues. If what you're proposing involves a field for every single fact, I think that would be unwieldy. Also, if what I'm saying is idiotic, please forgive me, as I'm teaching myself this stuff as I go. Actually, now you guys are teaching me, too, for which I am very grateful!

                      • 8. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                        WalterWhitmanMoore

                        Siplus, that's actually a great way to look at it: a checklist for people to filled in by investigators. The goal is to win while keeping our clients' bills as low as possible, and one reason for the database is to let us delegate as much as possible to someone whose billing rate is lower rather than higher. For example, you don't need to have practiced law 25 years to enter into a database the date of a document you're reviewing, the name of the author, the name of the recipient, etc. After all the objective information is entered, someone with gray hair like me can come along and figure out whether an entry is significant and, if so, identify the issues to which it relates, determine whether it is a "key" document, and make any notes about it.

                        • 9. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                          WalterWhitmanMoore

                          DING DING DING! WE HAVE A WINNER!
                          And that IS shouting, but with joy and for good reason!

                          Here is the fine point that I didn't quite understand in your answer, but which I tried and it worked:

                          When designing the report, I needed to have it "Show records from:" my "Facts" table, rather than from the "Issues" table. By basing the "Body" of the report on the "Facts" table, and basing the "Sub-summary" on the "Issues" table, it worked just right.

                           

                          THANK YOU VERY MUCH! I wrestled with this one for hours.

                          • 10. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                            WalterWhitmanMoore

                            Oops!

                            I take it back, alas.

                            It SEEMED to work, but when I double-checked, it didn't really work.

                            The early entries in the issues list pick up all corresponding records, but subsequent issues do not.

                            Thanks just the same.

                            • 11. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                              jimfeuerstein

                              waltermoore,

                               

                              erolst has described the right approach, and I'm not suggesting anything different. However, I thought it might help if I explained it in litigation terms.

                               

                              Here (simplified a bit) is how we do something similar in a litigation discovery application that we built in FileMaker…

                               

                              First, here are the key tables that relate to your issue:

                               

                              DOCUMENT

                              Each ‘document’ record represents either a document or a passage of selected lines from a deposition. Each document record has a primary key (an internal FileMaker identifier) that uniquely identifies that document.

                               

                              BOOK

                              Each 'book' record identifies -- by name -- either a Fact, an Issue, or a Witness in your litigation. This is a ‘master list’ of your key facts, issues, and witnesses.

                               

                              Each book record has a primary key (an internal FileMaker identifier that uniquely identifies that book. It also includes:

                               

                              A book type (Fact, Issue, or Witness)

                              A book name (e.g. Warnings and Representations)

                               

                              BOOKDOCUMENT

                              Each 'bookdocument' record represents the intersection of a document with a book — that is, it means that the document identified in the record contains a reference to the book identified in the record.

                               

                              Each BOOKDOCUMENT record includes:

                               

                              The primary key (the unique identifier) of a single document

                              The primary key (the unique identifier) of a single book

                               

                              Thus, we can easily prepare a report organized around books that will list each document that relates to each book. Or we can do the reverse, and for each document list the books to which that document has been assigned.

                               

                              To make it easy for users to choose books that relate to a document, we've placed three portals on the document record, one that lists all Witness books, one that lists all Issue books, and one that lists all fact books. (Remember, each portal just shows the BOOK records — that is, the master list of all the issues, witnesses, or facts you’ve defined).

                               

                              When the user clicks on one of the books in a portal, a simple script runs that creates a BOOKDOCUMENT record that indicates that this document belongs in the clicked book. (We also display a colored rectangle in front of the book name, to show that it’s been selected for that document).

                               

                              If you want to remove the document from the book, you click that book in the portal again and we delete that BOOKDOCUMENT record. (In fact, we don’t really delete it, we just set a boolean field to show that the record isn’t active, but that’s an implementation detail; the effect is the same).

                               

                              -- Jim

                              • 12. Re: HELP! CHECKBOXES VS. VALUE LISTS VS. PRINTED SUMMARIES
                                WalterWhitmanMoore

                                Thank you VERY much.

                                That helps me conceptualize it. I was having trouble relating to the shapes and colors. Part of the problem or challenge of litigation is that there are so many facts and issues, and so many relations between them, that it's harder to set things up than, say, if I'm selling five products to 300 customers.

                                 

                                Now all I have to do is figure out the "simple scripts" you described. Child's play, I'm sure. : )

                                 

                                Thanks again. I really appreciate your valuable time and input.