1 2 Previous Next 29 Replies Latest reply on Nov 21, 2015 3:58 PM by BruceRobertson

    Print report with two portals from different tables

    AndrewJudd

      hi all,

       

      Usually with my print layouts I use list view and a script step to related records in the line items table, say for invoices etc that works fine. This avoid page break portal issues.

       

      However, I am drawing up an accounts table which has portals to two tables. These are expenses and accounts line items. You can see the set up attached.

       

      My print layout currently has two portals on - "EXPENSES" and "ACCOUNTS LINE ITEMS". A user enters a list of costs asscoaietd with an event and then adds artwork that has been sold to accounts line items. Fairly simple, these two amounts are set against each other for a quick  accounts / income vs outgoings print out.

       

      What I wonder is how to avoid using portals to print with - they are not reliable for page breaks etc. Image can bleed across page breaks etc.

       

      Should I pull everything into another table and print from that?

       

      Whats best?

       

      thank you

       

       

       

      Screen Shot 2015-11-19 at 18.50.40.png

        • 1. Re: Print report with two portals from different tables
          Mike_Mitchell

          You're right to want to avoid portals for printing. Host of issues there.

           

          This is an ideal use case for Virtual Lists. Here are some links to get you started:

           

          Virtual List in 3 Easy Steps - MightyData

          FileMaker Virtual List Technique

           

          You can also search the forum here for related threads. Good technique to learn.

           

          HTH

           

          Mike

          • 2. Re: Print report with two portals from different tables
            StephenWonfor

            Andrew

             

            +1 on the Virtual List implementation here.

             

            Stephen

             

            "Be careful about virtual relationships with artificially intelligent pieces of software." ~Larry Ellison

            • 3. Re: Print report with two portals from different tables
              AndrewJudd

              thanks chaps, looks complicated... Is that the best way to go to produce something like the attached?

               

              Looks like rows have to be related and directly below / above each other.. anyway will look more into it if recommended.

               

               

               

              Screen Shot 2015-11-19 at 19.48.30.png

               

               

              Screen Shot 2015-11-19 at 19.48.42.png

              • 4. Re: Print report with two portals from different tables
                AndrewJudd

                Could this work? GetRows() custom function

                • 5. Re: Print report with two portals from different tables
                  StephenWonfor

                  Andrew

                   

                  VL gets easier the more you use it.  Once you "get" it you can break it.  For example, and this is assuming that the positioning of the fields and alignment are what matters most, "the vaguely virtual list" with apologies in advance to BR.

                   

                  What I would do here is gather the data needed for the report into stacked variables(¶ stacking) - each variable would belong to a group - e.g.. header, info, details and footer. - e.g. $$Header1, $$Header2, $$Header3, $$Header4 etc.

                   

                  Then, in the scratch table, you could have a number of fields for each group and set them up in the format you want for printing.  Then group them - e.g. the header fields and set a visibility rule ( hide if GroupTag ≠ "Header"). e.g. the info fields and set a visibility rule ( hide if GroupTag ≠ "Info").  You could then overlay the 4 field groups on a LIST layout.

                  Getting the image to show in a container would require an id_image that you'd collect as well and use in a join to the image source.  I realize this rather breaks the elegance of the VL technique but I suspect it would work.  It becomes very ungeneric and one-off, the universality of the layout is one of the gems of the VL technique.

                   

                  So you'd gather the data from whatever tables it might be in, stacking the values into variables, then unpacking the variables into empty records in the Scratch table (could delete all records there then create as needed).  Unpack the header vars first, then the info vars, then the details vars then the footer vars. Use ValueCount($var) to control the extraction loops and create records as needed.  And you just write the data in fields that are in sync with the variables you are extracting from.  Note: I am breaking a VL "rule" here by not having the Scratch table field(s) as calculations (the coolest part of VL), having too many fields in the Scratch table and using more variables than is "normal" but...just trying to solve a problem.

                   

                  Alternately you could just use stock text fields and the formatting functions and you'd still need a container field for the images.

                   

                  And, on the plus side, you are not printing portals.

                   

                  Stephen

                   

                  "Computers are like air conditioners.  They work fine until you start opening windows."  ~Author Unknown

                  • 6. Re: Print report with two portals from different tables
                    BruceRobertson

                    Stephen, nice explanation of the use of scratch tables the way you like to do it.

                    Note that one of the key concepts of virtual lists, and several variations of virtual lists, is that they are multi-user safe.

                    By creating global variables to hold report content, each user can collect and see something different.

                    In your case you are writing to actual stored data fields in a scratch table.

                    Useful; but not multi-user safe. Now I can pull up your report and read it or overwrite it.

                    • 7. Re: Print report with two portals from different tables
                      StephenWonfor

                      Bruce

                       

                      I had not really thought about the multi-user safety issue.  Now that I do - very nice! - now that may well be the "Gem of The Virtual List".


                      Stephen

                       

                      "If you see this message, John Doe lost the bet and must show up next work day in a chicken suit. Suit in locker 41, combo 13-7-30"

                      • 8. Re: Print report with two portals from different tables
                        AndrewJudd

                        Thanks all, So far if I'm understanding it correct -

                         

                        I have a portal in "ACCOUNTS" which runs a script and adds entries to "EXPENSES". I have added a global variable to the script - as below. I have a new table called "ACCOUNTS REPORT" and this is a list view layout and has <<col2>> in the body of the list view with this text calculation GetValue ( $$COLUMN1 ; _pkAccountReportID ).

                         

                        Now, when processing the script in ACCOUNTS it sets a new global variable every time, so the first record (of 100) does show the correct word for the _matchexpense. However if you add another entry it replaces to global variable with the new one. So the "list" in ACCOUNTS REPORT always just has one entry.

                         

                        Not sure I understand how the virtual list becomes a list. how does a global variable have more than one word or number at a time?

                         

                        thank you

                         

                        Screen Shot 2015-11-20 at 15.15.39.png

                        • 9. Re: Print report with two portals from different tables
                          Mike_Mitchell

                          AndrewJudd wrote:

                           

                          Now, when processing the script in ACCOUNTS it sets a new global variable every time, so the first record (of 100) does show the correct word for the _matchexpense. However if you add another entry it replaces to global variable with the new one. So the "list" in ACCOUNTS REPORT always just has one entry.

                           

                          Not sure I understand how the virtual list becomes a list. how does a global variable have more than one word or number at a time?

                           

                           

                           

                          A variable contains a string of text. It can contain lots of words and numbers. The trick is to realize it can also contain other characters, such as the pilcrow (¶), or carriage return. This is the key to how the Virtual List works. Every carriage return designates a new value, which is how the GetValue ( ) function knows to separate the individual entries. It's called a "return delimited list", and all the "value" functions work with it. (It's also how values are stored in fields formatted as checkboxes or radio buttons, just FYI.)

                           

                          Now, when you're adding multiple values to the list, you don't just set the variable to the next value. As you have discovered, if you do that, the most recent value wipes out what you already have. Instead, you use the List function, like this:

                           

                          List ( $$virtualList ; "newValue" )

                           

                          This will append (add) the phrase "newValue" to the end of the existing list stored in the variable $$virtualList. So say it contained this list before:

                           

                          Red

                          Blue

                          Green

                           

                          If I say:

                           

                          List ( $$virtualList ; "Yellow" )

                           

                          the result looks like this:

                           

                          Red

                          Blue

                          Green

                          Yellow

                           

                          Make sense?

                          • 10. Re: Print report with two portals from different tables
                            AndrewJudd

                            Thanks, yes I think that is possibly what this is below? - which I'm struggling to follow from the Mighty Date how to you sent earlier. This returns a list of states from contacts I think -

                             

                            Let([

                            query = "select distinct \"Work State\" from contacts order by \"Work State\"";

                            result = ExecuteSQL( query ; "" ; "" )

                            ];

                            result

                            )

                             

                             

                            Where do the list function and pilcrow go in the scripts?

                             

                            My add to expense script in the accounts table is here - can't see how i would add List ( $$virtualList ; "newValue" )

                             

                            Thank you!

                             

                             

                            Screen Shot 2015-11-20 at 15.55.00.png

                            • 11. Re: Print report with two portals from different tables
                              StephenWonfor

                              Andrew

                               

                              Try this sort of thing.

                               

                              Data collection.

                              Loop though your data and use case( not is empty($$Variable); $$Variable & "¶") & SomeData) // the ¶ is Option 7 on a Mac

                              That will let you stack your data.

                               

                              Data Extraction.

                              Then GetValue($$Variable;$Count) will allow you to extract the data row by row. $Count is incremented each loop.

                              You can exit the loop when $Count > ValueCount($$Variable)


                              Stephen

                               

                              "Any sufficiently advanced bug is indistinguishable from a feature." ~Rich Kulawiec

                              • 13. Re: Print report with two portals from different tables
                                taylorsharpe

                                I use virtual list reporting all the time for multi-user, security and performance reasons.  I almost always generate the array with a SQL that is done with a Perform Script on Server, which makes it very fast.  Virtual lists are great for any report where editing is not required.  There are a few weird things about it though.  For example, if you create a virtual list based on a global array, you can't do a SQL against it.  But it has vastly improved a lot of reporting for clients making them much happier. 

                                • 14. Re: Print report with two portals from different tables
                                  AndrewJudd

                                  HI stephen, could you expand on this?

                                   

                                  "Getting the image to show in a container would require an id_image that you'd collect as well and use in a join to the image source.  I realize this rather breaks the elegance of the VL technique but I suspect it would work."

                                   

                                  Just working on the image section of the report now so expecting difficulties.. thanks

                                  1 2 Previous Next