12 Replies Latest reply on Apr 19, 2013 5:47 PM by debi

    Newbie question about variables and fields


      I come from an HTML/PHP/mySQL background and am flummoxed by something that seems rather simple:


      I'm setting up a form where the user searches for a unique identifier linked to an individual, if it exists already, then they are directed to simply re-activate that person or do other stuff. If it does not exist, then I want them to be able to enter that new person into the database.


      I'm having trouble simply carrying the unique identifier forward to the point where it gets inserted into the database in the appropriate field.


      To be clear: it goes like this - search for records where field A is equal to X; if none found, then create new record with field A = X.


      If I put that value in the search box and get nothing back, I want to be able to display or otherwise use that same value without re-typing it.


      Thanks in advance. I'm sure this is super simple. I've been trying to devise ways to hold the field value in a variable, etc, but there must be a very easy way to do this.

        • 1. Re: Newbie question about variables and fields

          I suspect I haven't understood your problem, but would this do?


          Enter find mode

          User enters find criterion in Field A

          Set variable $Value to the value in Field A

          Perform Find

          If FoundCount = 0

            New Record

            Set field A to $Value


          End if



          1 of 1 people found this helpful
          • 2. Re: Newbie question about variables and fields

            Hi mh and welcome to the community.


            As with most things FileMaker, there are a few ways to accomplish what you need. Firstly, though, when possible, let us know what version you're working with. I'm going to assume v12. You also might want to let us know how x is getting set; is FMP setting x within a script? A field? Is it coming from outside of FMP? Is the find a scripted find or is the user manually performing it? Is the find using a specific field or using the QuickFind feature? I ask this last question as we may want to use the Get (QuickFindText) script step.)


            Assuming FMP is setting x, performing the find, and upon the 401 error  creating the record, then you just need to store x temporarily in a local variable -- $myX. If you are using multiple scripts you set a global var -- $$myX. If you're doing something that's more manual please let us know the exact process so we can give a more complete answer.



            • 3. Re: Newbie question about variables and fields

              Thanks to you both. Sorry for not being more specific.


              The user types in a value to a field and searches the database, when no results are found, I want that same value to be entered into a new record in that field.


              So, my tiny problem is simply the syntax for assigning the value that the user types in to search to the appropriate field. As you suggest, using a variable.


              But how do I "get" that value from the form the user typed into?


              I've been trying variations on this:

              Set Variable [$variable; ValueGetField("fieldname")]


              But that's not right.

              Thanks. I know this is simple. In html forms, you just grab what you need. It must be the same.

              • 4. Re: Newbie question about variables and fields

                If fieldA is the field that holds the value, the script step is:


                Set Variable [$variable; value: fieldA]



                • 5. Re: Newbie question about variables and fields

                  Welcome to FileMaker.


                  Let's back up just a bit and cover some FileMaker basics. Dave and Nick have answered your basic question - "How do I set a variable?" - but your original problem was, "How do I detect a value that already exists in the database?" Doing a search and then flagging based on the results is only one method. There are others that may suit better.


                  First, let's introduce a FileMaker concept known as the "global" field. This is a field in the database schema that holds the same value across every record (not across every user). Such a field is often used for temporary storage, and, in this case, you might use it to crosscheck the database for an existing value without doing a search. How? By leveraging the relationship engine.


                  You can define a relationship between a global field and any other field, even within the same table (known as a self-join). So what you could do is set up a global field, tie that field to the field you want to "search" using another table occurrence on the Relationships Graph, and put it on your "search" layout. At this point, if the user enters a value in the global field and there is another record in the targeted table, there will be a relational match - you can simply use a calculation like:


                       not IsEmpty ( relatedTable::keyField )


                  If that calc is true, then there's a match. You can then perform whatever logic you like based on the results.


                  Another way of doing this is to leverage the validation engine. Make your field validation "unique". Go ahead and create the new record. Then, if the validation fails, revert the record (the new record will disappear) and perform your other logic.


                  Lots of ways to skin the cat. Enjoy!    



                  • 6. Re: Newbie question about variables and fields

                    I tried this before, too. Instead of giving me the value entered into that box on the form, it gives me the actual name of the field. So if a user searches and puts "12345" into fieldA, when I say Set Variable [$myvariable; value: fieldA] and then test it to see what value is returned using a dialog box, I get: "fieldA" and not "12345".


                    Also, FM automatically puts quotes around "fieldA" for me in the Set Variable statement. Not sure if that is the problem, but I can't figure out how to make it stop.


                    I feel this is getting closer to being solved.

                    • 7. Re: Newbie question about variables and fields

                      You're getting the quotes because you're not entering the fieldname correctly, so FM is treating it as a literal value.  And yes, that explains why the variable isn't being set as you hope.  I suggest using the "Specify..." button to ensure that you select the field, then there's no danger of getting it wrong.


                      But I would also suggest backtracking and consider Mike's really helpful response as an alternative.



                      • 8. Re: Newbie question about variables and fields

                        Thanks, I hadn't thought of using Revert to do this. I'll see what I can come up with. Meantime I want to get to the bottom of my variable problem. But your solution does seem more elegant.

                        1 of 1 people found this helpful
                        • 9. Re: Newbie question about variables and fields

                          Thanks for your patience. I guess there's still something that I just don't get.


                          I have used the Specify button with the same effect. It doesn't make sense to me to specify the field in the database when I want to just hold onto the value that was input in the search form---it's not even in the database yet. Is there a get function that will take the value from the last searched term or something like that?


                          So, if I'm putting the value that the user types into the form into a variable, how, exactly do I just grab that value? I created the form by inserting the appropriate field onto the template. My button runs my script which performs the find and gives the correct result: yes record exists or no record doesn't exist. If no, then make a record putting that number in the field (it is a unique identifier).


                          I understand if you've spent enough time on this. I'll keep looking for an example script that will show me what I'm doing wrong. The field name in question has a space in it. Maybe that's the problem. I'll experiment with other fields, too.

                          • 10. Re: Newbie question about variables and fields

                            See the attached. It has a script trigger on a global field where the user does the find. Then on error it creates a new record with the global value set into the field.


                            To use just enter a value into the global and exit the field.


                            I hope this helps.

                            • 11. Re: Newbie question about variables and fields

                              Thanks so much. I'm starting to get the hang of it.

                              • 12. Re: Newbie question about variables and fields



                                Another approach could be to provide a button for the user to initiate this process (as opposed to just starting it by typing in a field). The button would run a script that presented a dialog (Show Custom Dialog script step) that includes a global field (second tab of the script step) for them to type into, and take care of all the rest.


                                Just another way to slice the dog (because my cat likes her skin).


                                Debi Rubel

                                FullCity Consulting