4 Replies Latest reply on Jun 14, 2014 9:49 PM by YoshioOgata

    Reformat or Transpose data from table1 to table2 (spread sheet)

    YoshioOgata

      Title

      Reformat or Transpose data from table1 to table2 (spread sheet)

      Post

           Hi, thanks for your tips and knowleddge.

           This is a English translation of Japanese post “テーブル1からテーブル2へデータを動的に組換えたい(一覧表)

            

           I am trying to transpose a table’s entries to a different table, but with no avail, 

           What I want to accomplish are;

           1) table1 is a base table whose composition is displayed in a screenshot image attached.

               From the table, I want to transpose cells into table2 format.

           2) I want the data of both tables are relationally linked, which means, one of a cell entry is changed manually, the relationed data cell on the other table IS automatically updated.

           3) I prefer, if able,  these link of the both tables are established via filemaker relationships between TBOs, not with functions.

           4) As a fundamental rule, each tables will be displayed on a different layout.

           5) A portal utilization is welcome.

           …what are the possible solution for this to come in practice. I want you guys lend me experiences and knowledges. Thank You.

            

           My working Env;

           1) MacOSX 10.93, with Filemaker Pro 13

           2) I want the solution be feasible both Mac & windows

            

      %E4%B8%80%E8%A6%A7%E8%A1%A8%E7%B5%84%E3%81%BF%E6%8F%9B%E3%81%88%E3%80%81%E6%A6%82%E5%BF%B5.png

        • 1. Re: Reformat or Transpose data from table1 to table2 (spread sheet)
          YoshioOgata

               Sorry initially included image is still in Japanese, here is English version. Tank you.

          • 2. Re: Reformat or Transpose data from table1 to table2 (spread sheet)
            philmodjunk

                 Table 1 is what is commonly called a "cross tab" report. There are many posts on that subject in this forum.

                 Table 1 might be a table with one record for each analysis ID value with portals that arrange the data from different related records from table 2 into columns. This is often done with a list view layout where the columns of data are created with one row portals. Different relationships or different portal filters can pull records for "item 1" into the first such one row portal and a different filter or relationship pulls data for "item 2" into the next column.

                 The columns can also be produced with calculation fields using ExecuteSQL(), but you have indicated that you did not want to use that option.

            • 3. Re: Reformat or Transpose data from table1 to table2 (spread sheet)
              YoshioOgata

                   PhilModjunk, Thank you for your reply.

                   This type of table is called “cross tab” report.  That information IS quite a big help, since I did not know how it’s called in English.  I will now on search with the topic. Many Many thanks.

                    

                   Here’s my progress report; 

                   I have been trying for some time, but for the time being, without any success as far as relationship part is concerned.

                   So, I, tentatively, tackled it, from a opposite direction. 

                   As you mentioned, I prepared some repetition fields with ExecuteSQL() functions., and managed to mimic the table-1 from table-2 records.  For a data editing issue on the table-1, I placed a button containing a script for calling data entry layout.  This is in a roundabout way from what I am expecting, but still it is functional.

                    

                   I will start looking into Cross Tab Report issues,  your comment leads me to it.  Thanks again.

                   Yoshio Ogata, Kyoto Japan.

              • 4. Re: Reformat or Transpose data from table1 to table2 (spread sheet)
                YoshioOgata

                     Thanks for Phil's advice, I somehow managed to implement the Relational parts.

                     I found the attached image has logical errors, I will post a collected image.  Sorry for messing up this board, and Thanks again.

                     Yoshio, from kyoto Japan