10 Replies Latest reply on Jun 10, 2013 12:25 PM by GuyStevens

    Displaying data from one field depending on the data from another

    DaveHardings

      Title

      Displaying data from one field depending on the data from another

      Post

           Hi,

           I have two tables: Table 1 & Table 2.  Each has a unique serial ID field and Table 2 has a foreign key field.  They are related by Table 1's serial ID and Table 2's foreign key field.

           Table 1 has a Site Name.  Table 2 has a Group Name and Site Name field.  When I enter data into Table 2's Site Name field, I need the Group Name to display on Table 1 IF the Site Name in Table 2 matches the Site Name in Table 1.

           I'm still very new to this program, so any help would be appreciated!

        • 1. Re: Displaying data from one field depending on the data from another
          GuyStevens

               Maybe you need something like this:

          http://youtu.be/Sg4vyifqZEM

               I think the structure in that file is two related tables.

               And if you need any more info on learning to work with filemaker you might want to try this one:

          http://youtu.be/5v2EzvaWyBk

                

          • 2. Re: Displaying data from one field depending on the data from another
            DaveHardings

                 Thanks for the response,

                 I have actually viewed both of these earlier today...  Perhaps I am missing something.

            • 3. Re: Displaying data from one field depending on the data from another
              GuyStevens

                   If you could (if there's no confidential data in your file) you might share it with us so we could have a look.

                   If you use dropbox you can put it in the public folder and share the public link with us.

                   Then we can see what you are doing wrong.

              • 4. Re: Displaying data from one field depending on the data from another
                GuyStevens

                     I think where you might be going wrong is that you need to enter an ID in your IdFk field.

                     You can do that using a dropdown that shows the values from the other field but enters an ID.

                     That's the only way to properly relate two tables together.

                     Maybe you are trying to enter the name in a text field. But that doesn't relate the two fields together.

                • 6. Re: Displaying data from one field depending on the data from another
                  GuyStevens

                       Hey Dave,

                       First thing:

                       Your ID is a number. That means your Foreign Key will also need to be a number.

                       Then what you need to do to relate the two tables together and get a valid relationship is enter the Id of the related record in the Foreign key field.

                       You could manually enter a number there but because you won't know the ID numbers by hart you will want to use a dropdown list.

                       Exactly like I described in great detail in my youtube video.

                       I could correct it in your file, but that way you not learn a thing.

                       Instead try for yourself to set up a dropdown list on the fireign ID field of Table two that shows the records from table one.

                       Make sure that your dropdown list is set up to use the ID of table 1 in the first field and the Site_Name in the second field.

                       That way you see the Site name in the dropdown but your foreign key field still contains the ID.

                       And have a look at that first video of mine again. Try and follow the excercise.

                       Also, Table one contains no extra data. So you won't really be able to check if your relationship is valid. But as a test, add a second field in your Table one and add that field on your layout of Table two. That way when you select a value in the dropdown that value will change.

                  • 7. Re: Displaying data from one field depending on the data from another
                    DaveHardings

                         Thank you, that worked great.  Next time I watch your videos I'll pay closer attention.

                         I do have one more problem now though.  I need the possibility to enter Site name manually in Table 2 and still have it link properly.  In addition I need to be able to do an excel import of the Site name (along with Group name) into Table 2 and have those link properly.  Is there any way of getting around this without knowing the ID numbers by heart?

                         Here's an updated vers. of the database

                         https://www.dropbox.com/sh/dhnjtff252jynn1/I-ij9pwaKX

                    • 8. Re: Displaying data from one field depending on the data from another
                      GuyStevens

                           If you don't have multiple site names that are the same you could import the excell file into Table 2 and then use "Replace Field Contents" and a relationship based on the name to enter the Id numbers automatically.

                           But I don't really understand what it is you are trying to do here.

                           Here's how I understand it:

                           You have an excell file that contains both the Site name and the Group name. Probably the Site name is repeated often.

                           And you want to get it so that your Sites are all in Table 1 (one record per site) and properly linked (by ID) to the records in table 2.

                           Here's what I would do:

                           - Import both site name and Group name in Table 2.

                           - Import the site names from table 2 into table 1 but make sure that Table 1 is set to only allow Unique Values in the Site name field.

                           Then you'll have 1 record per site in Table 1.

                           - Then you create a relationship based on the Site Name and in Table 2 run a "Replace Field Contents" on the Foreign Key ID field to fill that field with the ID from Table 1.

                           When that's done you can:
                           - Change your relatiponship back to how it was.
                           - Remove the Site Name field from table 2.

                           Let me know if I'm getting close.

                            

                            

                      • 9. Re: Displaying data from one field depending on the data from another
                        DaveHardings

                             Thanks again for your great responses,

                             I'm not sure if I am following your directions correctly, but it is not creating the functionality that I would like. 

                             My goal is to have site names already present in Table 1 and then if one types a site name into Table 2 that matches one from Table 1, Table 1 should "recognize" this and the group name from Table 2 should be displayed on Table 1.  This functions to an extent with the initial directions you provided, but it does not allow me to import a batch of group names (with associated site names) from an excel file. 

                             I can accomplish this simply by creating a relationship between the site name fields in both tables, but I don't know if this will cause problems later on as I expand my database to include more tables and relationships... 

                             And ideally, I would not have to manually manipulate the data with the Replace Field Contents and changing relationships before and after an import, as this will be an ongoing process of importing.

                        • 10. Re: Displaying data from one field depending on the data from another
                          GuyStevens

                               Hey Dave.

                               I guess I'm not entirely understanding what it is that you want to do.

                               You now have more data in Table 2 then in Table 1.

                               But you talk about importing data in Table 1 and then entering Data in Table 2.

                               Nut there is more data in Table 2 then in Table 1.

                               Maybe in your previous post you confused Table 1 with Table 2.

                               But why don't we start from scratch.

                               Try and explain to me whet you want to do. Not in Database terms, but tell me what Table 1 is and What Table 2 is and what your workflow is.

                               Because right now I'm really not understanding what it is you want to achieve.