1 2 3 Previous Next 39 Replies Latest reply on Aug 28, 2016 8:04 AM by realgecko

    Combining fields from different records

    realgecko

      Dear all,

       

      For a database on medicinal plants i would like to combine fields from different records based on the condition that the records refer to the species.

      To illustrate my question I include a screenshot showing a small portion of one of the 30 tables in my database:

      Screen Shot 2016-08-26 at 4.02.09 PM.png

      What I am hoping to do, is reduce the number of records and effectively create unique identifyers in the "Scientific Name" field.

      Therefore, I need a function, which allows me to combine the distinct entries in the "Use category" field, whenever the records refer to the same "Scientific name". That is, rather than having 3 records for "Scientific name = Abelmoschus moschatus Medik" I would like a single record which in the "Use category" field reads "A1, D1, S1"

       

      Any help would be greatly appreciated.

        • 1. Re: Combining fields from different records
          alangodfrey

          A classic use of a Parent table and a Child Table.

          Create a Parent Table to hold one record for each Scientific Name.  Allocate a unique ID to each record.

          Create a Child table of 'Plant Uses', with a Unique ID for each record (as is good practice).  Have in that 'Plant Use' table a PlantID field, and populate it with the Plant ID the Use refers to.  So you will have one table with (say) 100 plants, and another with (say) 2000 'Plant Uses - that's the equivalent of the table of data you have now.

          Create a Relationship between the two tables using the PlantID and you will be able to display all of the uses for any plant in a portal.  You can also 'find' all plants that have use 'A', for example.

          • 2. Re: Combining fields from different records
            realgecko

            Thank you! What is the easiest way to creat the Parent table? The issue is that I do not have just one of these tabeles but 30 making it a total of over 1000 plant species and over 15000 uses...

            I hope I don't have to create the parent table manually?

            • 3. Re: Combining fields from different records
              beverly

              careful export and import may help you create the table(s).

              but it does seem that you should revise your current schema (even in many tables). A PITA now will save hundreds later (been there, done that!)

               

              beverly

              • 4. Re: Combining fields from different records
                alangodfrey

                Create the parent table, and import the data from Excel.

                You can import all of the lines blindly if you set the PlantName field to not accept duplicate values.  It may seem like FM has imported 10,000 lines, but it hasn't really - it has checked 10,000 lines and rejected any that do not meet your non-unique criterion.

                 

                Then create the child table and import all of the lines to it.

                You will have to do a bit of a compromise at first, though, by matching not with unique IDs (best practice) but by matching by Plant Name.

                • 5. Re: Combining fields from different records
                  beverly

                  or export summarized, so that there is a unique name.

                  beverly

                  • 6. Re: Combining fields from different records
                    realgecko

                    Do I understand you correctly that you mean to create the parent tabel manually? Is there no way to save me this effort?

                    • 7. Re: Combining fields from different records
                      realgecko

                      Hi berverly,

                       

                      Thanks for you suggestions. I am new to filemaker and after creating the tables I have realized that I should have gained a better understanding of the database software first? Could you make any suggestion on how to change the schema? Currently I have 28 tables, each representing 1 ethnobotanical study. Further, I have one "studies", providing details on each of the 28 individual studies as well as one summary table which combines the data from each of the 28 individual tables. The issue is that my unique identifyers (Scientific species names) are not actually unique as there are duplicates in each of the 28 tables as well as the summary table. What I need is a function to merge fields of different records in the tables if they are identified by the same species name.

                      I am aware that I screwed this up by not defyning unique identifyers from the beginning. What do you consider the easiest way out? Would greatly appreciate any help/suggestions!!

                       

                      Matthias

                      • 8. Re: Combining fields from different records
                        realgecko

                        Hello Alangodfrey,

                         

                        Could you tell me where to set the import criteria to not accept duplicates?

                         

                        Cheers

                         

                        Matthias

                        • 9. Re: Combining fields from different records
                          alangodfrey

                          "Do I understand you correctly that you mean to create the parent table manually? Is there no way to save me this effort?"

                          If you already have the Filemaker tables written, then there is no extra effort.  If you have not already created them, then you can create a Filemaker file directly from importing an Excel file (but I wouldn't...).  It's not too much work; from your screenshots it's only 8 fields (that we can see).

                           

                          When you find yourself creating many tables that are (even 'almost') identical you should consider if they should just be one table.  It looks to me that all of yours should be a lot of records in one table.  It also looks to me that you should include in the Table a field 'Study', and import all of your sheets into that table.  They can then be separated again because you will have imported the 'Study Code' field, which identifies them.

                           

                          When you create the field in the Table for Plants set the PlantName to be unique, click on the 'Options' button (bottom right), then on the 'Validation' tab, and set it to accept 'Unique values only'.

                          • 10. Re: Combining fields from different records
                            beverly

                            Without knowing entirely what you have, it may be a Hierarchical set of data. A child has parent(s) and parents have child(ren). But they are all "people". You know best how they should be combined (or separated). Come up with a plan and then let us know.

                             

                            There are ways to push data from one schema into another so that things are related correctly. It may take some temporary "keys" and/or temporary self-joins. From there the data is correctly sorted and related.

                             

                            You might contact a developer near you to assist with the assessment of what you have and what you need. But certainly think about your needs and ask here again.

                             

                            beverly

                            • 11. Re: Combining fields from different records
                              realgecko

                              "When you create the field in the Table for Plants set the PlantName to be unique, click on the 'Options' button (bottom right), then on the 'Validation' tab, and set it to accept 'Unique values only'."

                               

                              That is what I did, it does not seem to work. It imports all duplicates. Any idea on the reason for that?

                               

                              As you can see in teh screenshot, I've tried to do as simple as possible importing only the field scientific name into a new table "taxa" as you suggested. As suggested I set validation unique values only upon import (and this has been accepted as can be seen in the details in the manage database box. However, this did not achieve the hoped result: all values have been imported irrespective of being duplicates or not. I am puzzled...

                              Screen Shot 2016-08-27 at 1.05.21 PM.png

                              • 12. Re: Combining fields from different records
                                realgecko

                                Hello Beverly,

                                 

                                Thanks a lot for your help!

                                The problem is that I live on an island and there is not a single developer here

                                I'll try to explain more clearly what I have and what I need:

                                I have one parent table which has 28 children. Each cild represents and individual ethnobotanical study and follows the same format as the one in the example provided in the screenshot. In the parent table all of these children are represented with all of their records as well as with a study code. Each study code is a unique identifyer in an additional table called "studies".

                                The issue is that in all 28 children as well as the parent (which I guess makes it a fake parent of some sort) the identifyers for each record (Scientific plant name for the species) occur in duplicates. The reason why they appea in duplicates is that for each identifyer (species) in each table (study 1-28) other fields vary (such as the medicinal uses in the field "use category" of the above screenshot.

                                Thus, I am looking for a way to reduce this redundancy and create unique identiyers in the parent table. Basically what I was hoping to to is the following: If the entry in field "Scientific name" is the same, combine the different records into a single one thereby making "scientific name" a unique identifyer.

                                How would you approach this issue?

                                • 13. Re: Combining fields from different records
                                  alangodfrey

                                  On the same Validation tab, untick 'Allow user to override', and set it to Validate 'Always'.

                                  1 of 1 people found this helpful
                                  • 14. Re: Combining fields from different records
                                    alangodfrey

                                    I'm not totally clear about what you are recording, but I think you should consider also "Are these tables (or one of them) Reference Tables (or should they be)?"

                                    In other words, you may want a Reference Table of one record per Plant, with all of its 'standing details' - Scientific Name, Common Name, Photo, etc.

                                    Then you may want a table (like you have here) that uses the Plant Details many times, each with a new parameter, like 'Study Number', or something, and pulls all of the Standing Data into it, automatically.  That way you only create the Plant Record once, but can use that data over and over in records that represent a Study, for example.

                                    1 2 3 Previous Next