1 2 Previous Next 16 Replies Latest reply on Jan 22, 2014 6:15 AM by philmodjunk

    From Dialog box: Check if record exists, if not create new record

    DrBev

      Title

      From Dialog box: Check if record exists, if not create new record

      Post

           Hello, I am having trouble creating the following script. A similar request was posted in 2002 by someone else, but my request here has some slight differences. I am using FileMaker Pro 12 and developing this database for use in iPhones.

           I have created a vocabulary flashcard database with the fields "word" and "definition". I want to create a script that goes something like this:

           1. User presses a button and the following dialog box comes up:

            "What is the word you want to create a flashcard for?"

           2. User enters text in an input field in the dialog box and presses OK.

           3. Upon pressing OK, the database checks to see if the word exists in the "word" field, ignoring lower/upper case and with the condition that the word exists alone. I wouldn't want a situation where the word "all-inclusive" already exists in the database but when the user wants to create a flash card for "all", the database erroneously says the word "all" already exists.

           4. If the user input word exists in the field "word", then the script pops up another window to say "Word already exists" and returns the user to the original layout and record prior to the user requesting creation of a flashcard

           5. If the user input word does not exist in the field "word", then the script creates a new record AND automatically inputs the word into the field "word".

            

           The script I came up with, which seems to be very problematic is the following:

            

      Script.png

        • 1. Re: From Dialog box: Check if record exists, if not create new record
          DrBev

               I'm not sure why the attachment isn't showing. Anyhow, here it is typed:

          Allow User Abort (On)

          Set E rror Capture (Onl

          Show Custom Dialog ("Requesting Input"; "What is the word you want to create a flashcard for? "; Vocabulary Flashcards::Wordl

          If (Get ( lastMessageChoice) = 1 )

          Show All Reco rds

          Perf orm Find (Restorel

          If (Get (Fou ndCoun!) > 01

          Show Custom Dialog ("Flashcard for word exist s" )

          Else If (Get (FoundCount ) < 0 1

          Go to Layout ("Word")

          New Record/Request

          In sert Text (Vocabulary Flash ca rds::Wordl

          End If

          End I f

          If (Get ( lastMessageChoice) = 2)

          Halt Script

          End I f

          • 2. Re: From Dialog box: Check if record exists, if not create new record
            philmodjunk

                 In your script as shown below, lines in green could be deleted and the script would produce identical results. Lines in red may have a problem

                 Allow User Abort (On)
                 Set Error Capture [On]
                 Show Custom Dialog ["Requesting Input"; "What is the word you want to create a flashcard for? "; Vocabulary Flashcards::Word]
                 If (Get ( lastMessageChoice) = 1 )
                Show All Records
                     Perform Find [Restore]
                     If (Get (FoundCount) > 0]
                         Show Custom Dialog ("Flashcard for word exists" )
                     Else If [Get (FoundCount ) < 0 ]
                         Go to Layout ("Word")
                         New Record/Request
                         Insert Text (Vocabulary Flash cards::Word]
                     End If
                 End I f
            If (Get ( lastMessageChoice) = 2)
                     Halt Script
                 End I f

            Perform Find [Restore]

                 Could only be used if you had a line that used Set Variable to store the value of Vocabulary Flashcards::Word so that you can then use the variable in the stored find. In my opinion, I'd make Vocabulary Flashcards::Word a field with global storage so that I could use the scripted find method illustrated in this thread of scripted find examples instead: Scripted Find Examples

                 Insert Text (Vocabulary Flash cards::Word]

                 will fail to insert any text unless Vocabulary Flash cards::Word has global storage specified. Come to think of it, if Vocabulary Flash cards::Word is the word that stores each vocabulary word in your table, it's the wrong field to use in your custom dialog or you will be overwriting existing words when you use that dialog.

                 But there's a much simpler way.

                 Define a relationship where Vocabulary Flash cards::SelectWord is a match field to your table of vocabulary words:

                 Vocabulary::SelectWord = Vocabulary|Select::Word

                 Where SelectWord is the field your users will use for entering a word for a new flash card and Word is the field that stores these words in your table. SelectWord must have global storage specified. Vocabulary and Vocabulary|Select will be two table occurrences of the same data source table.

                 Your script then becomes:

                 Show custom Dialog ["What is the word you want to create a flashcard for? "; Vocabulary Flashcards::SelectWord]
                 If [ Get ( LastMessageChoice ) = 1 // OK was clicked ]
                    If [ Not Isempty(Vocabulary|Select::word ) // this is a new word ]
                       New Record/Request
                       Set Field [Vocabulary::Word ; Vocabulary::SelectWord ]
                    Else
                       Show Custom Dialog [ Vocabulary::SelectWord & " already exists."]
                    End If
                 End IF

            • 3. Re: From Dialog box: Check if record exists, if not create new record
              DrBev

                   Thank you for the quick response! Let me go over your script slowly and carefully. It'll take me time to understand what's going on.

                   Will definitely let you know if I have any problems! :-)

                   Thanks!

              • 4. Re: From Dialog box: Check if record exists, if not create new record
                DrBev

                     I'm having trouble executing this script. I'm not 100% sure that I understand what the tables and the relationships are, but this is how I set them up. Is this how it's supposed to be (see image)? As you can see, I'm not too familiar with defining table relationships.

                     In the image below, Vocabulary is the table with the "real information" and Vocabulary:Word is the field with the real information.

                • 5. Re: From Dialog box: Check if record exists, if not create new record
                  philmodjunk

                       You have two data source tables instead of a single data source table with two table occurrences that point to it. I left out a link on table occurrences from my last post: Tutorial: What are Table Occurrences?

                       Delete the table named VocabularySelect on the Tables tab in Manage | Database. Then click over to the relationships tab and delete the VocabularySelect table occurrence box you have in the above screen shot. Click Vocabulary and then click the duplicate button. This creates a new occurrence of Vocabulary, it's not a new table, it's a new way to refer to the original Vocabulary table. You can double click this new occurrence box to open a dialog to rename it to something more descriptive than Vocabulary 2--such as Vocabulary|Select as I named it in my example.

                       Then add SelectWord to the Vocabulary table and define it as a global field.

                       then you can drag from SelectWord in Vocabulary to Word in Vocabulary|Select to create the needed relationship.

                  • 6. Re: From Dialog box: Check if record exists, if not create new record
                    DrBev

                         Ugh...not working quite right! :) Sorry for being such a nincumpoop!

                         I've created the self-referring table relationship between two fields as you've expressed. I've double checked my script too. Somehow though, when I go to execute the script, not matter what word I input (whether it's an existing word or a word that doesn't exist in the "word" field) a new record is created.

                         SelectWord is a global field

                         Here is what I have:

                          

                    • 7. Re: From Dialog box: Check if record exists, if not create new record
                      philmodjunk

                           You are not being a Nincompoop! You're just a beginner at this and I'm the guy running behind you holding up your bike until you can keep from falling off when you try to do this on your own. wink

                           The if step's Boolean expression should be: Not IsEmpty ( Vocabulary|Select::Word )

                      • 8. Re: From Dialog box: Check if record exists, if not create new record
                        DrBev

                             Thanks! You're being very patient, very helpful and going above and beyond! Thank you!!

                             I made the changes as you suggested and I'm still getting the same problem where regardless of what I'm entering into the dialog box, that word creates a new record even though the word could be pre-existing.

                             Eep!

                        • 9. Re: From Dialog box: Check if record exists, if not create new record
                          philmodjunk

                               I suggest re-posting your script so that I can see the current version.

                          • 10. Re: From Dialog box: Check if record exists, if not create new record
                            DrBev

                                 Thanks, PhilModJunk, for your generosity and time!!

                                 I have attached a screenshot of my entire script along with the table relationship, and the table.

                                 I don't know if it matters that I had 29 or so records PRIOR to the creation of the "SelectWord" field and prior to creating all this new script etc. information.

                            • 11. Re: From Dialog box: Check if record exists, if not create new record
                              philmodjunk

                                   Everything that you show looks correct. Click over to the Fields tab in Manage | database and check the data types for these two fields. Both should show as type Text. If either or both are number fields, change them to Text and see if that makes a difference.

                                   You might also insert a Commit Records script step as line 3 of your script, but I really don't expect that to be needed here...

                              • 12. Re: From Dialog box: Check if record exists, if not create new record
                                DrBev

                                     All the data types for both of these fields are text with regards to type. The only one that isn't is the auto-entry field for "date" which is a field that automatically enters the date for when the record was created.

                                     I've also inserted a "Commit Records" script in line three as such (see below). Not quite working thought :( Hmm.....

                                • 13. Re: From Dialog box: Check if record exists, if not create new record
                                  philmodjunk

                                       What exactly does "not quite working" mean?

                                  1 2 Previous Next