10 Replies Latest reply on Jan 29, 2010 1:32 PM by NYCTC

    How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?

    NYCTC

      Title

      How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?

      Post

      Hi, this is probably a simple thing but I'm still quite new to this:

      I am trying to use a pop-up value list which looks up from a table of user-names. I've done this via a table (as opposed to a pre-defined val-list) so it's relatively easy for anyone to go to the "NewUser" table/layout and add a new UserName. So, for example a new person comes to work on a project, and we add a new record under the UserName Table. The new name is typed into the "name" field as "NameD".  Already there exists 3 other records in this table, "NameA", "NameB", and "NameC". 

       

      Ok, so then from another Table/Layout called "NewProject", I want to have a field called "SelectUser" to lookup the value list from the table  "NewUser" and give the list of availableuser names to choose from.  No problem. 

       

      Now, this part may sound stupid,  but later on sometime. We realize that one (or more) of the names originally entered into the "NewUser" table has been mis-spelled. If we go back and fix it, then the UserName as it was auto-entered in the "NewProject" table wont match any more.

       

      How can I get the function of a look-up with a pop-up list, while still getting a dynamically updated data from the orignial table?

        • 1. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
          philmodjunk
            

          There is no perfect solution for this with current versions of filemaker.

           

          You should related your two tables via an auto-entered serial number in your name table. Your value list should enter this number instead of the name. Now, name changes will not affect the values selected in this pop up. To select the number by finding the name, make your value list a two column value list with the number as column 1.

           

          To see the name on your layout: Simply add the field from your name table on your current layout. (You must have a relationship defined in Manage | Database that links the two tables by these two ID number fields.

           

          It's not perfect, because you may want that nice auto-complete feature, you see in Field\Control | Setup... but it doesn't work when column 1 is a number field. It's not perfect because you may want to conceal the number field from the value list by hiding column 1, but if you do, you'll have problems when you end up with two people with identical names as the second instance of the same name will not appear in such a value list.

          • 2. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
            NYCTC
              

            Thanks much for the reply.  I think I was heading in that direction before-- I had a "GetRecordID" field in the name table. Then in the project layout, upon using the pop-up value list. I would auto enter the up-to-date name in another field based on an if-satement testing for the name's record ID..  However, this only updated with the "correct, updated" info only when you would re-invoke the pop-up... basically the same usefulness as the pop-up alone!  I couldn't get this continually update the info to the "correct name" field (like for searches).  

             

            I think what your describing is actually more direct-- I just can't seem to understand how to make the relationship between the name fields in the 2 tables be conditional to the record number (or ID).  It's probably real simple and I'll just mess with this some more.  But you have an idea as to what part of the picture I'm missing, I'd again, much appreciate your input!

            Thanks

            • 3. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
              philmodjunk
                

              RecordID... as in get(recordID) in a calculation field or an auto-entered calculation? I don't recommend that approach and here's why:

               

              If you ever have to import your records into a new copy of your file (the old copy became damaged, you have an upgraded version to install...), the records may get new record ID's during import and they will fail to match to the correct group of related records.

               

              Use an auto-entered serial number field instead. This is safe, reliable, and the numbers won't change during imports unless you specifically enable that option.

               

              To see updates automatically, Place the name field from NewUser on your NewProject layout. Any changes to the name field in NewUser should then automatically appear on matching records in NewProject. (The Looked up value approach you are using is very useful when you want such changes to affect only new records--such as making price changes in an invoice database.)

               

              • 4. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
                NYCTC
                  

                Thanks again Phil for your help. I really thought I was understanding the concept, followed (I think) your advice and got it to work. The pop-up on my ProjectTable uses a 2 column value-list which references the both a serial# field & the names field from my NamesTable. The two tables were also linked via the serial# field (from Names) and another, kind of unneeded number field (in Projects) -- I just put it there, thinking I had to store the serial number there-- but the pop-up was now working, updating directly from the NamesTable as hoped.   

                 

                Only, when I looked more closely, I realized I had no idea why it was working. I can't seem to understand how the relationships editor really works. I thought this worked by way of having the auto-serial # field in my NamesTable linked to a this unused # field in my ProjectstTable.  So to try to figure it out, I deleted the number field in the ProjectsTable. This then broke the relationship linkbetween the 2 tables. Somehow, without these linked, the thing still worked!! 

                 

                Finally, when I changed the auto-enter pop-up Value list from the 2 column (with serial# & name fields) to a single filed-- just the names, THEN it stopped working. It went back to a typical "passive-paste" value-list pop-up.

                 

                So, just when I thought that the purpose of the serial # was to define a relationship between the 2 tables-- it seems that it's doing something else, via the referenced value list.  Anyway, I know this is probably so basic, again I thank you in advance your time and any light you can shine on my dim understanding of this!

                 

                Teo C

                • 5. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
                  philmodjunk
                    

                  Can't really say why deleting the "unused field" didn't break your relationship without being able to see what other relationships were defined in your relationship graph and how you set up your layout. Deleting a field used as a match field in a relationship breaks that relationship and that should result in fields on your layout not displaying the expected data. Given that you might have more than one Table Occurrence box in your graph that refers to the same data source table, you may have inadvertently selected your name field from a different TO that was unaffected by deleting the field--but I can only speculate.

                   

                  Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:

                  Table vs. Table Occurrence (Tutorial)

                  • 6. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
                    NYCTC
                      

                    Thanks again Phil. Ok, I read your post about Tables verses TO's. I think I understand the basic idea. But, still I'm stumped as to how/ why my file works.  When I used "select tables w/ same source tables"  on my mystery graph, there seems to be really nothing linked.  It seems that just by having the field set up for auto-enter from the value-list but with the 2 column setup, the second fileld (the name) will update in real-time (the first field is the serial number).  If I change the the auto-enter setup to use just the name, that's when it stops working-- meaning, it wont update changes to the original names table instantly. It has to be re-applied, just like a fixed typed-in value list.  I don't know if there's a way to send this file to you for your opinion (just 56k), but I have to imagine I'm just missing something here.  Of course the value list itself, which pulls values from the 2 fields in the NamesTable is making a connection betwen the layouts. But, then  1) why are there no connections showing in the relationship editor?  2) Why does it break when the value-list is not a 2 column source using the serial number as the first column?

                     

                    my thanks again for your time and thought!

                     Teo

                    • 7. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
                      philmodjunk
                        

                      "...If I change the the auto-enter setup to use just the name, that's when it stops working..."

                      That makes sense, IF the field is used directly or indirectly in a relationship between the two tables. Changing the data entered to the name instead of the ID number results in no matching data in this field and that would keep the other field from displaying data from a matching record. I suspect the tables are linked in your graph--just not in a way that is immediately obvious to you. It's possible to relate records from different tables in your relationship via one or more table occurrences--though that's not necessarily a good idea in many cases.

                       

                      You might have something that similar to this:

                      YourTable:: DropDownField = Table2::field = NameListTable::Field

                       

                      Even though YourTable and NameListTable aren't directly linked, Filemaker can match a record from NameListTable to a record in YourTable if there is a record in Table 2 that matches records in both YourTable and NameListTable.

                      • 8. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
                        NYCTC
                           Hello once again, and thanks once again for your patience.  I guess I should be happy that it works and move on-- but it's so hard for me to work without being able to follow the logic! 

                        So I sarted over with a fresh file  to make sure that there's no additional TOs or possible defined-relationships somewhere from messing around... Just the default table "ProjectTable" and one new one, "NamesTable". 
                        "ProjectTable" has just 1 field  called "ChooseName".  NamesTable has 2 fields,  "UserName" (text entry) and "UserNumber" (auto-enter serial number, on creation). 

                        Then there's a value list called "ValList_UserNames":  Then under the EditValueList dialog, and "use names from field" / "specify field", I've chosen to "Use values from field" for the table "NamesTable", and first field as "UserNumber" and second field as "UserName".  Then in my default Layout/Table "ProjectTable", the only field there, "ChooseName" is set with the Control-Style as a pop-up menu which displays values from the value-list "ValList_UserNames". 

                        Sorry, this must be getting boring by now.  But are you saying that just by defining the pop-up to use the 2-field value-list, that this is the same as asking it to look for a match via the serial number?



                        • 9. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
                          philmodjunk
                            

                          OK, now I see the question. No relationship involved here at all is there?

                           

                          If you choose the pop-up menu option for a value list and choose the "show values only from second field option", You are entering the number in the field, but the field's formatting conceals it and displays the name in column 2. This can be seen as a type of "relationship without a matching relationship graph entry". Yep, that's how it's set up to work. If you switch formats to drop down list, you'll see the number instead of the name after you exit the field. It's set up that way in part to give developers the choice.

                           

                          Note: for name lists like this, you may not want to hide the first column of your value list. If you have two identical names and hide the ID column, you'll only see the name displayed once and will be unable to select the second instance of the name.

                          • 10. Re: How to get both a pop-up/look-up from another table, AND still get dynamic data from that table?
                            NYCTC
                              

                            Thank you. Thank you!  I get it now! It's not just some random thing that happens to work out... I can get some sleep now.  (kidding)

                            Many thanks again

                            Teo