1 2 Previous Next 18 Replies Latest reply on Apr 4, 2014 6:55 AM by philmodjunk

    deselecting a value from a drop down Value list

    dinoapolito

      Title

      deselecting a value from a drop down Value list

      Post

           Hi,

           I have a field that I will use in Find Mode. The field is 'accountID' and I have it's data as a Drop Down list looking at an Accounts table. So if I have 20 accounts the drop down list will show 20 accountID's.

           The same layout has the same again for seasonID and workspaceID each with drop down lists looking at a table (a seasons table and a workspace table.

           The idea is the user can just use those the drop downs to select a specific combination of season, account and workspace before hitting a FIND button that will perform a find based on those three criteria.

           All good so far.

           But what if the user selects a season from the season drop down value list but then wants to deselect it so that it will not search for a season? I'd like a way for the field to go back to empty other than hitting the delete key to empty the field. 

           Ideally I'd like the value list to have something like a * as the first record then the 'seasons' table records.

           Hope that makes sense

           Dino

            

        • 1. Re: deselecting a value from a drop down Value list
          philmodjunk

               You seem to be asking for two things, the ability to clear the field with a single click of the mouse:

               Set Field [YourTable::YourField ; "" ]

               And setting up a value list that includes the * find operator as a value in the value list.

               There are two ways that I can think of for setting up a value list based on fields in a table to include that * character:

               add a record where the account ID field has an * as the value. This may require that the field be a text field instead of a number field.

               Define a calculation field with text as the result type:

               List ( "*" ; Account_ID )

               Set up your value list to refer to this calculation field instead of account_ID and you'll have an added * value appear. (But field 2 won't show the correct value)

               You may instead want to add a button for inserting the * instead of putting it in the value list.

               And you might also be interested in a set up where the fields are global fields that you enter/select criteria while in Browse mode instead of entering find mode. See the scripted find examples found here: Scripted Find Examples

          • 2. Re: deselecting a value from a drop down Value list
            dinoapolito

                 Yes I did think of adding a new record to the season, account and workspace tables but these tables are shadow tables from an MySQL database and we can't do that.

                 So I thought I might create a local table called say VirtualSeasons, add the first record to it with a seasonID of * then copy all the other seasonID's over to it from the shadow Season table.

                 But to be honest I don't know how to write a script that will do that.

                  

            • 3. Re: deselecting a value from a drop down Value list
              philmodjunk

                   Import Records can copy the datea form the shadow table into your VirtualSeasons table. But note that you don't have to use a drop down list to add the wild card operator.

              • 4. Re: deselecting a value from a drop down Value list
                dinoapolito

                     Import Records? Isn't that for importing external data?

                     I already have the data in the Seasons Table and want to copy all records to the VirtualSeasons table. They are in the same .fp12 file

                • 5. Re: deselecting a value from a drop down Value list
                  philmodjunk

                       Import records can import records from any table to any other table, whether the target table is in the same file as the source table or a different file. When you go to set up the import and get the open file dialog, you go ahead and select the file that you already have open.

                  • 6. Re: deselecting a value from a drop down Value list
                    dinoapolito

                         OK, but I'm not sure that will work in the longer term. When I give this file to a customer, I have no idea where it will live. Therefore the path I've set in the script will not be valid.

                    • 7. Re: deselecting a value from a drop down Value list
                      dinoapolito

                           Grt(Filepath). Sorry I should have looked

                            

                      • 8. Re: deselecting a value from a drop down Value list
                        philmodjunk

                             You shouldn't need any file path as you can set up a relative file refernce back to your current file. A relative path won't change when you move the file or even if you rename it.

                             A relative path will have this format:

                             Filename.FMP12

                             or  in older versions:

                             Filename.fp7

                             This tells filemaker to "refer to the file of this name located in the same folder as the current database file." Since the two tables are in the same file, this will always be the case.

                        • 9. Re: deselecting a value from a drop down Value list
                          dinoapolito

                               Thanks for your help. I have it working quite nicely now. As the user selects a season, workspace or account the records displayed in the poral adjust accordingly and if they select * it will show all records - in this case "jobs"

                               (A job can have any combination of seasonid, workspaceid or accountid or none at all

                                

                               A related question perhaps.....

                               On the same form I have a field that is another edit box with a drop down value list liked to the master jobs table. The drop down shows all the records in the job table.

                               It would be great if I could have conditional value lists. So if the user selects a particular season in Field 1,  the drop down list in Field 2 (jobs) would only show jobs from that season. Likewise for any combination of season, workspace and account.

                               In summary

                               Field 1 = Season

                               Field 2 = Account

                               Field 3 = Workspace

                               Field 4 = Job - and is a value list of the jobs table.

                               It would be great if the drop down list in Field 4 was dependant on the values in Fields 1, 2 and 3.

                                

                                

                          • 10. Re: deselecting a value from a drop down Value list
                            philmodjunk

                                 Conditional Value lists are something that I know a bit about. Here are some links on the subject:

                                 There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                                 The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                                 Forum Tutorial: Custom Value List?

                                 Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                                 Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                                 Hierarchical Conditional Value lists: Conditional Value List Question

                                 Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                                 Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                            • 11. Re: deselecting a value from a drop down Value list
                              dinoapolito

                                   Thank you. I will jump straight to the SQL method because I'm quite comfortable with SQL but have never really understood where, when or how to use the ExecuteSQL function.

                                   (Currently I just use it in refresh scripts to call procedures to refresh the data in the external MySQL database,)

                              • 12. Re: deselecting a value from a drop down Value list
                                dinoapolito

                                     OK, I'm getting my head around this...I think.

                                     In my case I want the value list to have job names (from the Jobs table)  based on the season, workspace and account entered into  the INPUT table. My calculation field is going to be (I think...)

                                      

                                ExecuteSQL( "
                                select name from Jobs
                                where seasonID = ?
                                and  accountID = ?
                                and workspaceID = ? ;
                                ""; ""; Input::seasonID ; Input::accountID; Input::workspaceID)

                                     Does that look right?

                                     My question is how will it handle empty values or when the user doesn't select a workspace  

                                     For example many jobs will not have a workspaceID. So in that case will it return jobs from that match the requested season AND account?

                                     And if the user doesn't care about accounts - in other words wants to see every job from a particular season and workspace regardless of account will this query still work?

                                      

                                • 13. Re: deselecting a value from a drop down Value list
                                  dinoapolito

                                       Further to my last the following works but only if there is a value entered for all three. If the user only selects a season but leaves the other two blank it returns nothing.

                                       ExecuteSQL( 
                                        
                                       "select name from Jobs
                                        
                                        
                                       where 
                                       seasonID = ?
                                        
                                       and  
                                       accountID = ?
                                        
                                       and 
                                       workspaceID = ?"
                                        ;
                                       ""; ""   ; gSeasonID ; gAccountID; gWorkspaceID )

                                        

                                  • 14. Re: deselecting a value from a drop down Value list
                                    philmodjunk

                                         Your SQL is doing exactly what you designed it to do. If the user doesn't select a value for one of the referenced fields, the WHERE clause does not evaluate as TRUE for any record in your Jobs table. Your expression makes the selection of ID's in all three fields required input.

                                         You'd need a different calculation if you want this to work in cases where the user is leaving one or more of these fields blank.

                                    1 2 Previous Next