7 Replies Latest reply on Jun 6, 2014 7:39 AM by erolst

    How to transpose records at report form?

    egbaumier

      I'd like to get suggestions how to show at report form this information:

       

      At form I have

       

      Record Value Date

      Rec1 100 1/1/14

      Rec1 120 2/1/14

      Rec1 150 3/1/14

      Rec2 100 1/1/14

      Rec2 100 2/1/14

      Rec2 130 3/1/14

      SubTotal 700

       

      I really need to show at this view

       

      Account Jan Feb Mar Q1

      Rec1 100 120 150 370

      Rec2 100 100 130 330

      SubTotal 200 220 280 700

       

      Any suggestion how to do it? At spreadsheet is easy to show it.

       

      Regards

       

      Edison Garcia

        • 1. Re: How to transpose records at report form?
          taylorsharpe

          Confugration a lot has to do with whether you plan to continue this for the rest of the year or years in advance or not, etc.  But for what you showed above, here is a database with the records you have entered and "Layout 2" is the transposed results.

          • 2. Re: How to transpose records at report form?
            egbaumier

            Thank you for your help.

             

            There will be a lot of configuration  for years in advance.

             

            Regards,

             

            Edison

            • 3. Re: How to transpose records at report form?
              erolst

              Here's a modified version of Taylor's file. It uses repeating fields and SQL – which means you don't have to create the same set of fields for each month.

               

              Add a new field along those lines to get the figures per quarter.

               

              Note that if in the second sum field you feed in the date from e.g. a global field instead of taking it from the current record, this is a simple but very effective summarizing tool that works independently of any found set.

              1 of 1 people found this helpful
              • 4. Re: How to transpose records at report form?
                egbaumier

                Thank you for your suggestions.

                Very nice improvement using SQL.

                 

                "Note that if in the second sum field you feed in the date from e.g. a global field instead of taking it from the current record, this is a simple but very effective summarizing tool that works independently of any found set."

                 

                Could give more details about it? I think it will be useful to compare 2014 and 2013 results. Am I right?

                 

                Regards,

                 

                Edison Garcia


                • 5. Re: How to transpose records at report form?
                  taylorsharpe

                  +1 for erolst's SQL solution and repeating fields.  It is amazing how many ways one solve the same problem and then try to figure out which way works best for your needs.  I started a SQL solution at first and figured to keep it simple just the normal FileMaker way.  But how I was thinking of SQL was very different than what erolst came up with.  And I remember all the comments back in FileMaker 7 times when people said repeating fields would go away and be uselss after going fully relational, etc. - haha.  And yet there are so many useful ways to still use them... not as relationships, but good reporting techniques.  And here erolst combined one of the newsest FileMaker features, SQL, with a very old function that people said would go away. It is fun to stretch the mind and think differently about solutions. 

                  1 of 1 people found this helpful
                  • 6. Re: How to transpose records at report form?
                    erolst

                    Edison Garcia wrote:

                    I think it will be useful to compare 2014 and 2013 results. Am I right?

                     

                    I agree – it probably would be useful to compare those figures …

                     

                    But seriously … in that case, you could add a Report table where each record has a different year value; copy over the repeating fields and feed them that year value to create the date ranges for the query. Different record, different results. Display them in list view, and compare away.

                    • 7. Re: How to transpose records at report form?
                      erolst

                      taylorsharpe wrote:

                      And I remember all the comments back in FileMaker 7 times when people said repeating fields would go away …

                       

                      They'll never go away as long as one has the dubious pleasure of maintaining or modernizing other people's ancient solutions  …