10 Replies Latest reply on Sep 2, 2014 9:27 AM by philmodjunk

    Search within dropdown within portal

    jetserkok

      Title

      Search within dropdown within portal

      Post

           Hey all, 

           I've been looking online for an answer on this, but can't seem to find it. 

           I have three tables; 
           1. Incoming
           2. Accounts
           3. Lineitems (a joined table between incoming and accounts)

           I have a portal in a layout based on 'Incoming', which allows me to CREATE records in 'Lineitems'. In that portal i select from a dropdown a value (an account) and then it automatically fills some information about the account. 

           The dropdown has a quite extensive list of accounts, so I would like that the user can enter data in the dropdown, and that at typing each character the dropdown shortens. Is this possible? 

           Another related question; the dropdown is currently the Foreign Key field of the joined table 'lineitems', showing the 'Primary Key' of the 'Accounts' table + a second field, the account name (that seems the only way, because choosing any other field than the connection FK with PK, will not show the related information). Then when selecting is shows the PK (and the related values) but the user doesn't care about the PK. Can I not directly show the account name for example?

      Screen_Shot_2014-08-28_at_14.53.11.jpg

        • 1. Re: Search within dropdown within portal
          philmodjunk

               To answer your last question first. When you set up a "use values from field" value list, you have an option that you can select that hides the value from the first field--the PK value. This requires that the second field value be unique for each corresponding PK value, but as long as this is the case, you can hide the PK values and only list the names. When the user selects an account, they only see the name, but the value list enters the PK value from field 1. If you use a POP up menu with this option, the PK value in the field will never be visible to the user, they'll only see the associated account name.

               For a way to use an auto-complete enabled value list to select an account but still link records by PK values see this file:

               FileMaker 12 or newer users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
               Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

               Note, I have just included the concepts from this demo file in "Adventures in FileMaking #2". This will be a file that demonstrates this technique plus many other enhanced value selection techniques along with detailed information on how each demonstration was set up and how it works. You can check my FaceBook page from time to time if you want to download that freeware file once I release it.

               Another way to make a long list of values in a value list easier to manage is to set up a conditional value list where you select a category in one field and a second field then lists only the values that are a member of the selected category. That can produce much shorter lists of values. Adventures in FileMaking #1 explores 8 different variations of conditional value lists.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Search within dropdown within portal
            jetserkok

                 Hi Phil, 

                 Thanks for your extensive answer. I already applied your suggestion to not show the first value, only second. I prefer to do this, then to use a pop up, too much hassle. I was hoping there was another way not involving/showing the PK, but well... 

                 I looked at your file, and tried to reproduce it, but it seems that i'm doing something wrong. At the moment i select the value list as a dropdown, the option to auto-complete gets disabled. I added a copy of my file, maybe you can have a look to help me:

                 http://www.asarti.com/temp/Admin.fmp12

                 The yellow field on the layout 'ingresos'  is the one i try to make work like in your example. 

            • 3. Re: Search within dropdown within portal
              philmodjunk

                   Take a look at the value list options for the drop down list in the demo file. It's not a value list that uses the ID and thus does not use the option to specify field 1 as the hidden field with ID. It only lists text values, the names from the related table. The other thing to look out for is that the field formatted with the value list is now a text field and not a number field. That also might be an issue here.

              • 4. Re: Search within dropdown within portal
                jetserkok

                     Hi Phil, 

                     I checked, and (like in the file http://www.asarti.com/temp/Admin.fmp12 ) i was already using the value list as you say, only listing text values. 

                     Then the second thing i checked is that the field is a text field, and its not: its actually a calculation of two fields together (to provide some more info to the user). But in your file, the field is also not a text field, its also a calculation of first and last name. I checked that if i change the field from calculation to text, it works perfectly though, so i'm on the right track :). I only wonder now; how can i remain having it as a calculation, like you do?

                • 5. Re: Search within dropdown within portal
                  jetserkok

                       Oh, have to correct myself: 

                       Both my field, and your field are text fields. However, both you and me, have a calculation field. Yours is named 'cfullname' with 'LastName & ", " & FirstName' and mine is named 'CombinaciónClave1Nombre' with calculation 'Clave1 & " " & Nombre'. And if i change that field (cominacionClave1nombre') to a text field, then it works, but of course i lose my combined info... 

                  • 6. Re: Search within dropdown within portal
                    philmodjunk

                    I don't see why any such name is needed. I do not format a calculation field with this drop down list. The cFullName field is a calculation field with a text result type specified. Is it possible that you have selected a Number result type for this calculation? There's a drop down in the specify calculation dialog for specifying the result type.

                    • 7. Re: Search within dropdown within portal
                      jetserkok

                      Thats it! How stupid of me... 

                      Thank you!

                      • 8. Re: Search within dropdown within portal
                        jetserkok

                        Leaves with me with one other problem, now it doesn't show the related records anymore. 

                        Where before I would select the primary key in the portal, and it would auto-fill some values, no it doesn't do that anymore. Any solution for that? 

                        • 9. Re: Search within dropdown within portal
                          jetserkok

                          Correction: i didn't set the fk as a lookup to other table occurance, but instead to the original table. So now it works like perfectly!

                          • 10. Re: Search within dropdown within portal
                            philmodjunk

                            Glad to see that you persevered and worked out all the "fiddly little details". The need for better info on how to use this method is why I incorporated it into a new file where it is one of many "enhanced value selection" methods. That new file, named "Adventures In FileMaking #2" is nearly ready for release on my FaceBook page and I hope to release it by the end of this week.