12 Replies Latest reply on Feb 29, 2012 10:20 AM by philmodjunk

    Calculating, Summarizing and Reporting Portals



      Calculating, Summarizing and Reporting Portals



      I am trying to report or summarize or calculate portal rows. Here is the deal:

      I am a printer. We print large graphics. I have created a portal that tracks these graphics per job. An image showing an example of 1 job is attached. 

      Graphics use the following materials:

      1. Substrate (the surface its printed on)

      2. Lamination (protective covering over printing)

      Different Lams and Substrates can be used on each.

      I need to get the foloowing :

      Total square feet used of each Substrate

      Total linear feet used of each Substrate

      Total square feet used of Lam

      Total linear feet used of Lam

      I figure I only need to enter the width and height once. Then FM needs to group all the substrates by name and the total sq ft and linear feet used. Then do the same for the Lams. 

      The Tiles and Rotate fields are only needed to calculate the sq and lin feet so i don't think that is my issue.

      So I tried reports but i don't see how on a report I can get two different bodies. 1 for Substrates and 1 for Lams.




        • 1. Re: Calculating, Summarizing and Reporting Portals

          First idea to come to mind is to create a summary report on a layout based directly on the portal's table instead of using a portal.

          However, with that approach, you have to separate laminate data from substrate data into different records and you are using one record for both a substrate and a laminate. (Makes sense for data entry as both laminate and substrate have the same dimensions and you don't want to have to enter this data twice.)

          If I am interpreting your example correctly, you can't have a laminate without a substrate but can have a substrate without a laminate. Is that always the case or are there exceptions to that apparent pattern?

          If that's always the case, I'm considering this table structure:


          "Allow creation via this relationship" would be enabled for both PrintItems and Laminates in this relationship and the Laminate field in your portal would be from Laminates. Thus, selecting a laminate in this field creates a related record in laminates with a one to one relationship so that the dimensions specified for related substrate can be used to compute total area and linear feet for the specified laminates.

          Problem with that is that you end up with one summary report on a PrintItems layout totaling substrate figures and a second layout totaling laminate figures--which means even the simplest print job has a two page report to give you your totals. That's not the ideal result here. Another option is to use a script to use the data from the portal to generate separate laminate and substrate records in the same report table--which gets you a report all on one page--but now you have your data duplicated in two tables. I'm just not sure which option I dislike least here...

          • 2. Re: Calculating, Summarizing and Reporting Portals

            OK well then I am bumming if i am putting PhilMod in a quandry.

            • 3. Re: Calculating, Summarizing and Reporting Portals

              Can you confirm that you never have a laminate without also having a substrate?

              I assume you want a list such as:

                  Avery 2920   Total sq ft     Total Lin feet
                  3M 180C


              With one row for each substrate and laminate....

              I just had a major Eureka here. I'm going to play with a simple demo file and then check back here to see if you have confirmed the above...

              • 4. Re: Calculating, Summarizing and Reporting Portals

                Ok, see if this works for you.....

                Take your portal table and define the following fields:

                InvoiceID (Foreign key to layout's table)
                MaterialID ( auto-enter serial number)
                LaminateID (number)
                SubStrateSquare Feet

                Then, go to the relationship tab and make a new occurrence of your portal table (I'll call it printItems, name it Laminates) and link it like this:


                Define the self join relationship between PrintItems and Laminates as:

                PrintItems::InvoiceID = Laminates::InvoiceID AND
                PrintItems::MaterialID = Laminates::LaminateID

                Enable "allow creation of records..." for Laminates in this relationship.

                Now return to the fields tab and add these fields:

                cLaminateSqFt (calculation field)
                Define it as PrintItems::SubStrateSquare Feet and select Laminate from the context drop down.
                sTotalSubSqFt (Summary, total of SubstrateSquare Feet)
                sTotalLamSqFt (Summary, total of cLaminateSqFt)
                cMaterialType (calculation, returns text, If ( LaminateID ; "Laminate" ; "Substrate" ) , clear the "do not evaluate if all referenced fields are empty" box. )

                In your portal, add a portal filter: Not LaminateID

                Use PrintItems::Material for your substrate field and Laminate::Material for your laminate field.

                Now each time you select a laminate, a related record is added linked by Invoice ID, but the portal filter keeps it from appearing and causing confusion in your portal, but now you can set up a summary report using the summary fields (Linear feet fields would be set up just like the square feet fields.)

                In this report, you can remove the body layout part and replace it with a pair of sub summary layout parts. Make the first one "when sorted by cMaterialType and put cMaterialType in the layout part to serve as a sub heading. Make the second "when sorted by Material" and place the material field and the 4 summary fields inside this layout part. (You can stack the square feet summary fields on top of each other in one stack and the total linear feet fields in another as only one field of each pair will have data for a given material.)

                To produce the report use Go To Related Records or perform a find to pull up all Print Item records with the specified InvoiceID, then sort them by cMaterialType and Material. (without sorting, the report will be blank.)

                • 5. Re: Calculating, Summarizing and Reporting Portals

                  Sorry i thought I posted this but i must have just hit preview.

                  Must have a substrate to lam. Sometimes I suppose we could lam someone else's work, but that is really really rare, and i would suppose if that is the case i could just have the opeator enter the lam under the substrate side. I know that is funky but for those rare times it would be OK.

                  • 6. Re: Calculating, Summarizing and Reporting Portals

                    Then the above method should work...

                    • 7. Re: Calculating, Summarizing and Reporting Portals
                      /files/65b15f3d47/ColorXCapture.jpg 1279x1008
                      • 8. Re: Calculating, Summarizing and Reporting Portals


                        I laid everything out to the best of my understanding. I posted some screen captures above. I don't think everything is correct and i have to admit i have no clue how to get the report to work, I tried a GTRR script but got nowhere.

                        On data input I Get this error: This field cannot be modified until “InvoiceID” is given a valid value. when I try and click in the laminates field. So I tried making InvoiceID a serial auto-entry and it worked, but you didn't say to do that in your notes. So I wonder if i screwed up.

                        Let me know if you see anything right off the bat that I meesed up. 

                        PS My table that you call PrintItems I call GraphicsListings

                        Thanks  Sincerely

                        • 9. Re: Calculating, Summarizing and Reporting Portals

                          What I call InvoiceID is what you are calling Job#. Remove Invoice ID and use your exisitng Job# field in its place.

                          • 10. Re: Calculating, Summarizing and Reporting Portals


                            Please see your InBox

                            • 11. Re: Calculating, Summarizing and Reporting Portals


                              I sent you an email

                              • 12. Re: Calculating, Summarizing and Reporting Portals

                                I know. Monday's are always crazy and I spent Tuesday evening filing my taxes. I should be able to take a look tonight and get a response back to you.