1 2 Previous Next 20 Replies Latest reply on Dec 12, 2011 11:37 AM by philmodjunk

    Finding Similar Records

    JessicaFiorini

      Title

      Finding Similar Records

      Post

      Hi there,

      I have a database that contains information on lesson plans for different states. Some of these lesson plans are repeated between states and have indentical naming conventions, file types, etc.. Is there a way to make a list of all the identical or matching lessons between the two states in one field? In the same find, can I then mark those shared lessons indicating that it shares a common file with another state?

       

      Thanks!

        • 1. Re: Finding Similar Records
          philmodjunk

          If the names are letter for letter identical, you can set up a self join relationship and list all records with the same exact name in a portal.

          If this is by a partial match of the names, things get a lot messier, but there are option you can experiment with. One option is to perform a find for all records with a specific key word or a word that starts with specified letters, then use omit record to refine the list to those that you can identify those that have a common "file" and then a script can "mark" them for you.

          You can also use a "search portal" to list records that start with or contain a specified set of characters in the name. Records listed in this portal can be selected either individually or as a group to be "Marked" also.

          When it comes to "marking" a group of records, I can think of several approaches.

          1) Give each record in your table a unique serial number if you have not already done so. Identify one record as the "base" or "parent" record and put that record's ID number in a second field added to your table to identify all records that are part of that group. This is simpler to set up in some ways, but limits records to being a member of only one group and you'll have problems if you want to delete that "base" record.

          2) create a separate table where you have one record for each such group and a unique serial ID field to identify each. Mark records in your current table by using that same additional field but by putting the serial ID of that new table's ID into the field. A bit nicer in some ways but still limits you to one group for any given record

          3) Use a join table to facilitate a record being a member of more than one group. Now that you have a many to many relationship, a given record can be a member of multiple groups--either linking back to a "base" record like in option 1 or two a separate "group" record like in option 2.

          Here's the download link to a demo file that illustrates the use of two different text based "search portals":http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          • 2. Re: Finding Similar Records
            JessicaFiorini

            The names are identical due to file naming conventions. I will try the self join relationship method. Thanks. I'm sure I'll have more questions...

            • 3. Re: Finding Similar Records
              JessicaFiorini

              See, here I go with another question.

               

              Can self join relationships be based on more than one criteria?

              • 4. Re: Finding Similar Records
                JessicaFiorini

                I mean, the instructions I am reading to create the relationship only mention using one field to create the relationship. I would need a couple of things to match.

                • 5. Re: Finding Similar Records
                  JessicaFiorini

                  Also, I would like to see all the matching records in a set and not just the current record. Like an overall report.

                   

                  Sorry if I'm a mess! This is what my relationship graph looks like.

                  • 6. Re: Finding Similar Records
                    philmodjunk

                    If you need table1::field1 to match table2::field2 AND table1::field3 to match table2::field4, then yes, in both normal and self join relationships, you can match on multiple fields. You can drag from field3 to field4 to add another pair of fields or you can double click the relationship line to open a dialog box where you can add additional pairs of fields.

                    • 7. Re: Finding Similar Records
                      JessicaFiorini

                      Great! Thanks. It seems as if it is working. I can now see the repeated files in the database but only on individual records.  Do you have advice on how to display a full list of all the matching records in the database? I'm not exactly sure how my file differs from the demo file.

                      • 8. Re: Finding Similar Records
                        philmodjunk

                        If a Lesson Name is "XYZ Arithmatic" you want to see a list of al records with that name?

                        Or do you want to see a list of all records that have one or more matching lesson name?

                        The first can easily by done with a portal, or a script that uses Go To Related Records or Performs a find to pull up the records in a set.

                        The second can be done with a summary report where you add a sub summary part "when sorted by Lesson Name" and sort your records on that field to group them. This report can list every record or just have one line for every unique lesson name.

                        • 9. Re: Finding Similar Records
                          JessicaFiorini

                          Good clarification point! I would like to see a list of records that have one or more matching lesson name. I guess the second option with one line for every unique lesson name. Does that make sense?

                          • 10. Re: Finding Similar Records
                            philmodjunk

                            Ok, the basic layout design would be to remove the body and just keep the sub summar part I mentioned. That gives you one row of data for each group of identically named lesson plans.

                            To limit the report to groups of 2 or more can be done via your self join.

                            Define this summary field in File Usage:

                            sCount: "count of" Lesson Name

                            Add a calculation field, cCount defined as F

                            ile Usage TO::sCount

                            When you pull up this report use a find to find all records where cCount > 1, then sort your records. (Note that you can put sCount in your sub summary parts to show how many records are a member of each group.)

                            • 11. Re: Finding Similar Records
                              JessicaFiorini

                              Hi Phil,

                               

                              Thanks for all the help. In your last response you say to define a summary field in file usage. Do you mean to create a new summary part in my file usage layout? Or in my new report layout?

                              • 12. Re: Finding Similar Records
                                philmodjunk

                                Define the summary field named sCount in File Usage is what I meant.

                                • 13. Re: Finding Similar Records
                                  JessicaFiorini

                                  Right. Thanks. I wish I could send you some sort of fruit basket...

                                  • 14. Re: Finding Similar Records
                                    philmodjunk

                                    Others have, (at least I've been sent giftcards to Amazon, restraunt gift cards and canisters of pop corn), not that I do this for that reason, but it's been a nice to see people offer to say thank you that way once in a while...

                                    1 2 Previous Next