9 Replies Latest reply on Apr 2, 2013 2:47 PM by philmodjunk

    Consolidating multiple records with few fields into a single record with multiple fields?

    ALB

      Title

      Consolidating multiple records with few fields into a single record with multiple fields?

      Post

            

           I’m using Filemaker Pro version 11.0v4, and I’m trying to do something that I think should be simple, but I just can’t figure it out.

           I am importing data from Excel into an existing FileMaker database, and the structure of the data in the Excel file is not the way I would like my FileMaker table to be structured. In my imported file, each imported data point is in its own row, and I would like my Filemaker table to have multiple data points per record. For example,

           Imported:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                          

                               Student ID

                     
                          

                               Judge number

                     
                          

                               Grade

                     
                          

                               1

                     
                          

                               1

                     
                          

                               77

                     
                          

                               1

                     
                          

                               2

                     
                          

                               70

                     
                          

                               1

                     
                          

                               3

                     
                          

                               82

                     
                          

                               2

                     
                          

                               1

                     
                          

                               85

                     
                          

                               2

                     
                          

                               2

                     
                          

                               80

                     
                          

                               2

                     
                          

                               3

                     
                          

                               87

                     
            

            

           Desired:

                                                                                                                                                                                                                                                                                   
                          

                               Student ID

                     
                          

                               Grade Judge 1

                     
                          

                               Grade Judge 2

                     
                          

                               Grade Judge 3

                     
                          

                               1

                     
                          

                               77

                     
                          

                               70

                     
                          

                               82

                     
                          

                               2

                     
                          

                               85

                     
                          

                               80

                     
                          

                               87

                     
            

            

           I have currently set up a child table to contain the imported data, and then I am trying to relate a parent table to consolidate the data. I want to use the “Judge number” field of the child table to assign which of three fields to populate in the parent table, using the contents of the “Grade” child field. Is there a way I can define the three fields in the parent table as calculation fields so that this happens dynamically as soon as I import data into the child table?

            

        • 1. Re: Consolidating multiple records with few fields into a single record with multiple fields?
          philmodjunk

               I think the structure you are getting from your import is the correct structure that you should have. It does not keep you from displaying your data in the format shown at the end of your post.

               A set of "horizontal" portals can display the scores from the judges in columns. And you also need a table of one record for each student, but this should be a separate table from your imported data.

               In a horizontal portal you arrange a series of one row portals in a row. You either set up a filter on the portal that selects for a specific judge or you specify that the first one row portal have an initial row of 1, the second, an initial row of 2 and so forth...

          • 2. Re: Consolidating multiple records with few fields into a single record with multiple fields?
            ALB

                 Thanks for your response. I have a few different databases where I have the same type of issue. In most cases, horizontal portals would be the best solution. I do have some instances where I will need to add a vertical scroll bar though, wouldn't that mean that each horizontal portal would have its own scroll bar and then the neighbouring portals could become misaligned? I'd like to be able to scroll through all rows at the same time. I can't put a portal within a portal can I? I imagine if I could, then I'd be able to apply specific filters to individual columns?

            • 3. Re: Consolidating multiple records with few fields into a single record with multiple fields?
              philmodjunk

                   I don't see why you'd need that scroll bar. The entire layout is a list view layout and the window would have it's own scroll bar for scrolling through the information.

                   You cannot put a portal inside a portal.

              • 4. Re: Consolidating multiple records with few fields into a single record with multiple fields?
                ALB

                     I thought that the vertical scroll bar would be useful because I want to use form view (each of these tables is actually tied to other parent and grandparent tables), and if the number of records at any point exceeds the number of rows defined by my portal, then the scroll bar would capture those extra records while they would just be left out of the portal if I don't have a scroll bar (unless there's another way to achieve what I'm trying to accomplish with the scroll bar?). I know that my proposed solution of a calculation field to re-organize the data is not the most efficient, but is there even a function that would allow me to do this or is it impossible?

                • 5. Re: Consolidating multiple records with few fields into a single record with multiple fields?
                  philmodjunk

                       I don't see the need for form view. You can place fields from a parent record in the header or a trailing grand summary and use list view for the body to list your individual rows with a horizontal portal in each row.

                  • 6. Re: Consolidating multiple records with few fields into a single record with multiple fields?
                    ALB

                         OK, I went with a list view for that database, but I just wanted to follow up on a similar scenario I have in another database. I am importing raw data into the database and I want to display it in a portal in a parent table. In the child table (raw data), I have a unique ID field, a field that corresponds with the parent unique ID, a field that contains the data value, and a field that describes the type of data (multiple categories; A, B, C, etc) - this would be the field I would normally use for filtering the portal columns so that each data category could be in a different column within a portal.

                         What I'm struggling with is that the values of one of my data types can be derived by combining values in two other categories (A = B + C). For a given parent record, I will generally only import "A" child data records (a single child record/parent) OR "B" and "C" data records (2 child records/parent). However, I always want to display "A" values in the portal found on the parent table, which should be possible because it could be calculated from "B" and "C" records, if the "A" records are absent. I don't think that I can use a filtered horizontal portal for this since sometimes it involves a calculation of two child records, and sometimes it does not. I was hoping to consolidate these various records at the level of the parent table, with a calculation field that represents "A" data values. I would have to use a formula that reports the single child data record value if the category field is "A", and if there is no "A" data value, then calculate the sum of "B" records and "C" records. I'm not sure how to structure that formula. Is it possible?

                          

                    • 7. Re: Consolidating multiple records with few fields into a single record with multiple fields?
                      philmodjunk

                           It sounds possible, but can you give an example or two with actual values instead of the letters A, B and C?

                           A horizontal portal typically only shows a maximum of one record in that single portal row, but that one record can use a summary field to display an aggregate value, such as a sum, of all related records that meet the portal criteria specified for that portal.

                      • 8. Re: Consolidating multiple records with few fields into a single record with multiple fields?
                        ALB

                             Sure, sorry! So here is an example of the child table (the imported data):

                              

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                            Data Unique ID                     Patient Unique ID                     Data type                     Cell concentration
                                            1                     1                     Lymphocyte                     1.1
                                            2                     1                     Myelocyte                     2.5
                                            3                     2                     Lymphocyte                     2.4
                                            4                     2                     Myelocyte                     3.5
                                            5                     3                     Leukocyte                     6.3
                                            6                     4                     Leukocyte                     5.7

                             and here is how I would like it to be displayed in the parent "Patient" table:

                              

                                                                                                                                                                                                                                                                                                                                                
                                            Patient Unique ID                     Leukocyte concentration
                                            1                     3.6
                                            2                     5.9
                                            3                     6.3
                                            4                     5.7

                             (Leukocytes = Lymphocytes + Myelocytes)

                             Thanks!

                        • 9. Re: Consolidating multiple records with few fields into a single record with multiple fields?
                          philmodjunk

                               Define a summary field in this table that computes the total of Cell Concentration.

                               Put this summary field in the one row portal for the column showing total Leukocyte concentration.