12 Replies Latest reply on May 22, 2016 8:39 PM by sam0723

    Portal Summary with same project number

    sam0723

      Hello, everyone. i'm new to filemaker.

      Now i'm developing a database with portal

       

      Here is my portal

      Record 1

      Project Number: A-1

       

      (Portal)

      Product    Number

      A               20

      B               30

      C               40

       

      Record 2

      Project Number : A-1

       

      (Portal)

      Product   Number

      A               30

      B               50

      C               90

       

      So my question is how could I generate the summary report of these two records so that in the summary report

       

      Project Number A-1

       

      Product   Number

      A               50

      B               80

      C               130

       

      I could get the subtotals of the products A, B and C with same project number. Thank you very much

        • 1. Re: Portal Summary with same project number
          SteveMartino

          So: 

          Record 1 is based on what table?  What does this table represent?  The reason I ask is there may be a structural issue if all the following is true:

           

           

          Record 1 has Project Number A-1, which is a field on the main table.

              When you look at the portal based on Record 1 you see:

          Product    Number

          A               20

          B               30

          C               40

           

          Record 2 is also called Project Number A-1. On it's portal you see:

          Product   Number

          A               30

          B               50

          C               90

           

          I assume there will be other records on the main table either called Project Number A-1 or maybe other names, Project A-2, etc.?

          So you may want to generate a report that summarizes all the portal records of Project A-1, but not Project A-2 or Project A-3?

          • 2. Re: Portal Summary with same project number
            sam0723

            hi, steve, thank you for your help.

             

            Record 1 and 2 are based on table system while the portals are based on the table Product.

             

            Yes, you are right. There are other records for example, Project A-2, Project A-3 and i would like to generate the summary report of all Project A-1. In this case, 2 of them. Also, I would like to summarize all the project with same project name. Can i use the filemaker to generate the summary report? Thank you

            • 3. Re: Portal Summary with same project number
              taylorsharpe

              You can do this normally in a layout sub summary parts.  Or you could do a SQL like this

               

              Let ( [

              F1 =  "SELECT

                             \"Project Number\",

                             Product,

                             SUM ( Number )

                        FROM
                             PortalTable

                        WHERE

                             \"Product Number\" = ?

                        GROUP BY

                             \"Project Number\",

                             Product" ;

              F2 = ExecuteSQL ( F1 ; Char ( 9 ) ; ¶ ; "A-1" )

              ] ;

              F2 )

               

              I'm not sure if you wanted to search for A-1's only or if not, then search by the key for records 1 and 2, which I don't know the key field. 

              • 4. Re: Portal Summary with same project number
                sam0723

                HI, Taylor. Thank you for your help. I will try it later. If i would like to get all the summary of all the project number with same project name. For F2, what should i include in the "A-1" position?

                • 5. Re: Portal Summary with same project number
                  taylorsharpe

                  sam0723 wrote:

                   

                  HI, Taylor. Thank you for your help. I will try it later. If i would like to get all the summary of all the project number with same project name. For F2, what should i include in the "A-1" position?

                   

                  I guess I assumed Project Number was the same as Project Name.  The SQL includes a Group By statement for grouping together Project Number (as well as Product).  But if there is a separate field for Project Name, you could group by Project Name. 

                   

                  If you have

                            Project Number          Product          Number

                            A-1                              A                    10

                            A-1                              A                    20

                            A-1                              B                    35

                            A-2                              C                    10

                            A-2                              C                    5

                   

                  Then if you Group by Project Number and Product, you get:

                   

                            A-1                              A                    30

                            A-1                              B                    35

                            A-2                              C                    15

                   

                  SQL is powerful and challenging.  You can just test these things in the Data Viewer to see how the groupings work.  Keep in mind ExecuteSQL just gives you an array in a variable.  It is not being displayed.  What I do is usually use Virtual Reports to store this data as a global variable so that I can see it in a layout in FileMaker.  Otherwise all you have is what you see in the data viewer as a variable.  Or if you want it in the portal, you can do a virtual array in the portal too. 

                   

                  Another thing to remember is that if you Group By Project Number, then you can't just select Product because you could have multiple products per Project.  So if you want to Group by Project Number, if you also want the Product, you have to do something like Max or Min of the Product or some summary.  You can't just ask SQL to give you a product unless you group by it or else summarize it. 

                  • 6. Re: Portal Summary with same project number
                    sam0723

                    Thank you . Taylor, if i would like to summarize the data in a field by execute SQL. Is it possible?

                    • 7. Re: Portal Summary with same project number
                      taylorsharpe

                      sam0723 wrote:

                       

                      Thank you . Taylor, if i would like to summarize the data in a field by execute SQL. Is it possible?

                       

                      Well  you can store any calculation in a field including the SQL result.  So you could store a single value from a SQL such as summing a column or you could store the whole returned SQL array in a text field.  Usually, however, I make it into an array and parse it out with a loop into FM fields if I need to store the results. 

                       

                      To do this you Set Variable that has an ExecuteSQL result.  Then you Set Field and set it equal to the Variable you created for the ExecuteSQL result. 

                      • 8. Re: Portal Summary with same project number
                        sam0723

                        hi Taylor,

                        i have tried the code you mentioned, but the result is "?", i don't know what was going on?

                        • 9. Re: Portal Summary with same project number
                          taylorsharpe

                          "?" means there was an error in something you typed.  It doesn't tell you the error.  So what I do is simplify the SELECT statement taking out things until it works and slowly adding things back in.  Often it is as simple as misplaced comma or not quoting a field name that must be quoted to work.  It is rather hard for us to tell you how to test, but try starting simple and let us know if that works and how far you get.  So start with something like:

                           

                          "SELECT COUNT ( * ) FROM PortalTable"     does that work?  If not, then the table name is wrong. Keep adding more as you succeed like:

                           

                          "SELECT \"Project Number\" FROM PortalTable

                          • 10. Re: Portal Summary with same project number
                            sam0723

                            Thank you Taylor so you mean i need to try to type the code by myself?

                            • 11. Re: Portal Summary with same project number
                              taylorsharpe

                              sam0723 wrote:

                               

                              Thank you Taylor so you mean i need to try to type the code by myself?

                               

                              SQL is  real picky on it syntax.  So I almost always start off by trying it in the data viewer and experimenting.  Usually I start with real basic SQL and slowly keep adding statements until I get what I want. Doing things in Data Viewer is not going to hurt anything and is a great debugging environment.  If you try things and they don't work, come back and post what you tried here and we'll help out as best we can. 

                               

                              Of course if you're looking for the easy way out, you can always just hire a FileMaker Consultant.... errrr... they are now called "Partners" <grin>.  But if you want to do some learning and do it yourself, yes, you should try typing the SQL "code" yourself. 

                              • 12. Re: Portal Summary with same project number
                                sam0723

                                Taylor, Thanks again,

                                Actually, I'm using Filemaker Pro, so I could not test the code using data viewer.

                                So I should start with SQL and test the code I typed? right?