13 Replies Latest reply on Dec 3, 2012 4:19 PM by ChrisSmythe

    Selecting part values from Value List

    ChrisSmythe

      Title

      Selecting part values from Value List

      Post

           Hello, I am running FileMaker Pro v12 on Windows XP and am hoping that someone would kindly assist me with the following (please see my database at the following link:

           https://www.sugarsync.com/pf/D0681807_60165357_49551  (Account Name: Admin   Password: click on OK as there is no password).

      Background:

           My DeploymentDetails::OperationExercise field is link to a Value List named OperationExerciseNames.

           When a user selects an Operation / Exercise from the dropdown list, they can scroll down the list to select an existing Operation / Exercise name. This works OK.

           Alternatively, when a user decides to not use the dropdown list and types in the actual value directly, then that works OK too.

           For example: the user may choose to use the dropdown list to select value: Operation Silver (Operation Silver is in the Value List). Alternatively, they may type the whole value: Operation Silver into the DeploymentDetails::OperationExercise field.

      Question:

           Does FileMaker have the ability for a user to type in only one word in a multiple word value that is part of a Value List?

           As per previous example, can the user just type in the word Silver and then the Value List recognises that the word Silver is part of the value: Operation Silver?

           Kind regards,

           Chris smiley

      Values_List.jpg

        • 1. Re: Selecting part values from Value List
          philmodjunk

               Not as a built in feature. There is an "auto-complete" setting where you enter the first few characters and FileMaker fills in the rest. If you type Oper, for example, this might match to "Operation Silver" or a group of values that have "oper" as the first four letters.

               There are ways to produce that effect, however, with some scripting, a portal and a portal filter expression that uses the PatternCount function. See the selection portals in this demo file:

          https://www.dropbox.com/s/0pm1gdqcfi2ndpv/EnhancedValueSelection.fp7

               Since you are using FIleMaker 12, use Open from the File menu to open this file and FileMaker will produce a converted copy in FIleMaker 12 format.

          • 2. Re: Selecting part values from Value List
            ChrisSmythe

                 Hello PhilModJunk, thank you for your brilliant suggestion and the demo file - very much appreciated.  I played around with your demo file and it works great - very nice work.  I tried to copy your idea of using the PatternCount function in a portal.  Unfortunately, I have had no luck making it work.  It seems to be way, way above my very limited expertise.

                 I have uploaded by current version of my database as follows:

                 https://www.sugarsync.com/pf/D0681807_60165357_41553  (Account Name: Admin   Password: click on OK as there is no password).

                 Could you please provide simple guidance for me to achieve what I am trying to do.  I understand that you are very busy and do really appreciate all your terrific assistance and help.

                 Kind regards,

                 Chris smiley

            • 3. Re: Selecting part values from Value List
              philmodjunk

                   I'll see if I can get back to this later today. In the meantime. At what point does your efforts at using PatternCount fail? It might be a very small, simple thing that you need to do before it works for you.

              • 4. Re: Selecting part values from Value List
                ChrisSmythe

                     Hello PhilModJunk,

                     I created a Personnel::GlobalSearchField2 and a DeploymentDetails::GlobalSearchField.

                     I then entered the PatternCount function in the portal.  I right-clicked the portal and selected Filter Portal Records and then clicked on the Specify button.  I then entered in the following:

                     IsEmpty (Personnel::GlobalSerachField2) or PatternCount (DeploymentDetails::GlobalSearchField ; Personnel::GlobalSearchField2)

                     Kind regards,

                     Chris smiley

                • 5. Re: Selecting part values from Value List
                  philmodjunk

                       Using two fields with global storage does not make sense here. If you check the demo file you'll find that in this expression:

                       PatternCount ( Field1 ; field2 )

                       Field1 is not a global field, but that field 2 is a global field. This is because it checks the contents of Field1 for every related record to see if they contain the text entered in field2, your global search field.

                  • 6. Re: Selecting part values from Value List
                    ChrisSmythe

                         Hello PhilModJunk, thanks for replying to this post - much appreciated.

                         Should I have created a GlobalSearchField in my Personnel table or DeploymentDetails table?

                         Do I then add the GlobalSearchField to my DeploymentDetails Portal in my Personnel Layout so that users can type in the GlobalSearchField that somehow relates to the DeploymentDetails::OperationExercise field (located in the DeploymentDetails Portal?

                         Sorry, about not making much sense here - this is rather confusing to me.

                         Kind regards,

                         Chris smiley

                    • 7. Re: Selecting part values from Value List
                      philmodjunk

                           A global field can be defined in any table in your database and it will work in your portal filter expression. However, when you edit a field used in a portal filter, the portal does not automatically update unless you include the global field in the portal's relationship and the relationship will require that you define this field in the layout's table, not the portal's. In your case, I believe that you have a portal to DeploymentDetails on your Personnel layout. If so, the search field should be defined in Personnel so that you can include it in the relationship with the X opearator to facilitate smoother portal updates.

                           You can find an example of this in the demo file alonge with documentation explaining the remaining details of how a script is used to update the portal keystroke by keystroke.

                      • 8. Re: Selecting part values from Value List
                        ChrisSmythe

                             Hello PhilModJunk, thanks for the extra information - greatly appreciated.  I created a new search field as follows:

                             Personnel::GlobalSearchField

                             I do not understand how to include this new search field in the relationship with the X operator.

                             I tried to emulate in your demo, the Auto-Complete Drop Down Search --- Invoice::gSearchField2 --- which in turn gets its values from: ProductDescriptions (Value List Name).

                             In your database relationship model, you seem to have Products table linked to the LineItems table via the ProdID.  In my database I do not currently have any links with my ListOfOperations table.

                             I then looked at your Description Search script and noticed that it contains things such as: Freeze Window and Show Custom Dialog.  I also noticed that you use a Trigger Script OnObjectExit.

                             As my database is currently published to the Web using sharing with IWP, I can not use these features.

                             Any ideas please?

                             Kind regards,

                             Chris smiley

                        • 9. Re: Selecting part values from Value List
                          philmodjunk

                               Freeze Window can be omitted from your script if it is going to be used on the web. Show Custom Dialog can be replaced with code that changes layouts to a layout with the message and buttons to click to select a response.

                               

                                    I do not understand how to include this new search field in the relationship with the X operator.

                               

                                    I tried to emulate in your demo, the Auto-Complete Drop Down Search --- Invoice::gSearchField2 --- which in turn gets its values from: ProductDescriptions (Value List Name).

                               If you look again at the relationships graph, you'll find that there are two different relationships between Invoice and Products. One links Products to LineItems for use with the portal at the top of the layout. The second one, links Products4Selection to Invoices by the gSearchField in a relationship that uses the X operator. Products4Selectionis a second table occurrences that also refers to products. You can see this if you hover the mouse over the upper left corner of the "box" of each light blue table occurrence. You'll see the same table name pop up in both cases.

                               The selection portal is based on this second relationship and using gSearchField to match to ProdRefresh is key to getting the keystroke by keystroke updates to work smoothly. But this method will not work over the web so just be sure that you have a link that includes your global search field but with the X operator instead of = for the one used in the relationship. To select X as the operator in a relationship, double click the relationship line to open a dialog where you can make this change. You can also use this change to include additional pairs of match fields when such is needed.

                               You'll have to enter text in the search field and then click a button to submit the data before the contents of the portal will update.

                          • 10. Re: Selecting part values from Value List
                            ChrisSmythe

                                 Hello PhilModJunk, thank you again for your valued help - much appreciated.  I believe that I am getting close to a solution.

                                 Just some questions please?

                                 1.  In your Description Search script is the following line:

                                 Go to Object [Object Name: "LineItemPortal"]

                                 I have no idea what this is or where it is?

                                 2.  I am trying to emulate your Auto-Complete Drop Down Search and was wondering if I actually need to use your scripts:

                                 Description Search and/or Update Search.

                                 If so, how do I use them to make this all work?  I could not see any related buttons or Trigger Scripts.

                                 Kind regards,

                                 Chris smiley

                            • 11. Re: Selecting part values from Value List
                              ChrisSmythe

                                   Hello again PhilModJunk, please be advised that I discovered that when I looked at your demo file in Layout Mode and went into the Invoice Layout and looked at the LineItems Portal, that there was the Products::ProdDesc field on top of another field: LineItems::ProdDescSearch.

                                   This particular field (ProdDescSearch) has a Trigger Script (OnObjectExit) that I believe runs the Description Search script --- which in itself runs the Select product script.

                                   Also, the Invoice::gSearchField2 field has a Trigger Script (OnObjectModify) that I believe runs the Description Search script --- which in itself runs the Select product script.

                                   I then found the Database Design Report from the Tools Menu to try to understand where or what the Go to Object [Object Name: "LineItemPortal"] is??

                                   Had no luck so far.

                                   I originally thought it was the LineItems Portal but am unsure??

                                   I tried Edit > Object > Show Object.  However, the Show Objects was dimmed and could not be selected.

                                   Is the Go to Object [Object Name: "LineItemPortal"] LineItemPortal the same as the LineItems Portal that I can see in the Invoice Layout Mode?

                                   Kind regards,

                                   Chris smiley

                              • 12. Re: Selecting part values from Value List
                                philmodjunk

                                     This demo file illustrates several different approaches to making selecting from a large value list more user friendly. I don't think the section you are describing is the best option for what you want. I've assumed that you were looking at the selection portals at the bottom of the screen, one of which illustrates the "contains partial text" type of search in a manner that is more user friendly than the drop down lists at the top of the screen.

                                     gSearchField2 uses the OnObjectExit trigger and actually would be better off with OnObjectSave. It is there so that the demo can illustrate two different approaches to using the drop down, auto-complete enabled value list. It illustrate that you cn have the drop down out side of the portal.

                                     Object names are names assigned to objects on the layout. If you click one and then check the position tab in the inspector, you'll see the object's object name appear in the "Name" box if it has an object name. Go to Object puts the focus on the specified object--this is needed so that Go to Portal row goes to a row in the line items portal and not one of the portals at the bottom of the layout.

                                     The OnObjectModify trigger is used with the copies of gSearchField located above the search portals at the bottom of the layout. This trigger is used to update the search portals on a keystroke by keystroke basis--something that won't work in Instant Web Publishing. Please note that script triggers cannot be used like this on a layout to be accessed via a Web Browser, you'll have to add buttons to perform the scripts involved.

                                • 13. Re: Selecting part values from Value List
                                  ChrisSmythe

                                       Hello PhilModJunk, thank you for your kind assistance  - very much appreciated.

                                       As I am running out of time to complete this database, I have had to leave this piece of functionality out.  I have spend many, many hours trying to understand your excellent demo and incorporate your kind and useful suggestions.

                                       I found myself not understanding your demo, but really appreciated that your provided it to me, along with your suggestions on how it works.

                                       Unfortunately, this is way above my very limited knowledge and experience of FileMaker and relational databases in general.

                                       Again, I thank you for all your hard efforts and help - most grateful.

                                       Kind regards,

                                       Chris :)