1 2 Previous Next 26 Replies Latest reply on Aug 5, 2010 4:17 PM by BillRyan

    Matching Fields

    BillRyan

      Title

      Matching Fields

      Post

      Hi, all. I've set up a portal in one database to show all records that have a matching field, which works great.

      Is there any way, however, to show all records that have a partial match in the linked fields? Right now, the portal only shows records where the linked fields are an exact match. But sometimes a name will appear in the matched fields by itself or as part of a team of names, and I'd like to include all records where the name appears, whether by itself or part of a group of names. Is that possible? Thanks!

        • 1. Re: Matching Fields
          FentonJones

          We really have no idea what you mean by "team" or "group" of names. Basically a relationship will match vs. each record/field/line of what you point it to. The target has to be able to be a "stored" field (which precludes using relationships in calculations to produce the field).

          • 2. Re: Matching Fields
            philmodjunk

            If your "team of names" has each name separated by a return, this list will match if any one name in the list matches the field on the other side of the relationship. Not sure if that's what you want or not.

            If you want partial string matching such as having "Phil" match "Phillip", then, while possible, things get quite a bit more complex as this is not something simple to set up in Filemaker.

            Some explicit examples of what you are trying to do would help here.

            • 3. Re: Matching Fields
              BillRyan

              Okay, thanks for responding... I'll try to better explain:

              I have a database of writers. I have a portal in that database that shows all records from another database of scripts that match the name of the writer. So any document written by the writer shows up in the portal. So far so good.

              The problem is that some scripts are written by more than one writer. It might be writer A & writer B. But those records don't show up in the portal of writer A, because it only shows records that have an exact match. I'd like to include ALL records where a given writer is involved, whether as a solo writer (which already show up), or if co-written with another writer. Does that make more sense? Is there any way to do that? Thanks so much!

              • 4. Re: Matching Fields
                BillRyan

                Phil, I didn't read your post until after I had posted my last response. Your solution is exactly what I need! Thanks so much... I really appreciate it... you don't know how long I've been pouring over the manual attempting to figure this out. Thanks again!

                • 5. Re: Matching Fields
                  philmodjunk

                  Use a join table instead.

                  Writers----<Writer_Script>----Scripts

                  Writers::WriterID = Writer_Script::WriterID
                  Scripts::ScriptID = Writer_Script::ScriptID

                  With this structure, you can use a portal to Writer_Script on a scripts layout to list all the writers for that script or a portal to Writer_Script on the Writers layout to list all the scripts that writer has contributed to. In either case, you can add fields from the third table to the portal to show information such as a writer's name or a script title.

                  Here's a demo file that matches "Contracts" to "Companies" in this fashion:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

                  • 6. Re: Matching Fields
                    BillRyan

                    Thanks for the additional info, Phil. I'll check that out... it sounds like a more sophisticated and stable solution for what I'm doing. Thanks again!

                    • 7. Re: Matching Fields
                      philmodjunk

                      It gives you a lot more flexibility when creating reports and editing the "connections" between writers and scripts. You also don't want to link things by writer or script names as these won't always be unique and might get changed on you.

                      • 8. Re: Matching Fields
                        BillRyan

                        Phil, I don't know if you're still looking at this thread, but one issue I've already noticed with your solution is this:

                        In the writer database, the portal will always show all records from the script database where any given writer is included (assuming there is a carriage return between names in the field as you suggested).

                        It doesn't work as well the other way, however. In the script database, the portal only shows writer records that match the first name in the writer field on any given script... all other writers (even when carriage return separates them) don't show up in the portal.

                        Is this an issue that would be addressed if I looked at the more complex solution you suggested above? Thanks, Phil!

                        • 9. Re: Matching Fields
                          philmodjunk

                          Any post you make pops the thread back into recent items where I'll see it.

                          I don't recommend the return separated list for this. I've recommended you use a join table instead. This is fact, one of the reasons why I recommended it. It really isn't more complex. It can in fact, be less complex than trying to manage a return separated value list for this purpose.

                          • 10. Re: Matching Fields
                            BillRyan

                            Got it. Okay, I'll check out that demo file you've referred me to and give it a shot. If I have any questions I'll let you know. Thanks for the help and your time... much appreciated!

                            • 11. Re: Matching Fields
                              BillRyan

                              Phi, I have a question... once I've set up the join table, how do I populate it with all the data from the existing tables that I want to connect? Do I have to go through and enter it all manually?

                              • 12. Re: Matching Fields
                                philmodjunk

                                Before you can set up the join table, you'll need to set up your writer table with one record per writer and a unique serial number for each. You don't want to link on writer names here. Names, even those of script writers, aren't guaranteed to be unique and sometimes people change their names and you don't want to put yourself in a position where changing a writer's name in your database breaks all the links to the writer's related records.

                                You may have this already or be able to modify your existing table to this design. I can't tell from here.

                                I'd try to use a script to build the records in the Join table if possible by looping through your existing records and creating records/setting values in the table.

                                • 13. Re: Matching Fields
                                  BillRyan

                                  Got it. But is there a way (and I'm sorry if I'm being remedial) to enter data in one of the linked tables and have it automatically enter the data into the join table as well? For example, if I create a new record in the Scripts database, and I enter the name of the script in Scripts::ScriptID, is there a way to have it automatically enter the data into Writer_Script::ScriptID, or do I need to do that separately through a portal? Thanks!

                                  • 14. Re: Matching Fields
                                    philmodjunk

                                    You could script it, I suppose, but it won't help all that much. The join record has to link a given writer to a given script. So a record in the join table that only has a script ID or only has a writer ID won't mean anything until you complete the link by selecting either a writer or a script so that you have both ID values entered into their respective fields in the record.

                                    This may be what you have in mind:

                                    Enable allow creation of records via this relationship in the relationships that link the join table to your other tables. Now, for example, you can place a portal to Writer_Script on your writers layout and format ScriptID in the portal with a drop down that lists all scripts. When you select a script from this drop down in the bottom blank row of the portal, it will automatically create a new record and enter the WriterID from the current Writer record into the WriterID of this new Writer_Script record. Thus, whith one menu selection, you've linked the current writer record to an existing script record by creating a new record in Writer_Script populated with both ID numbers.

                                    1 2 Previous Next