3 Replies Latest reply on Apr 29, 2015 11:22 AM by philmodjunk

    Finding an email or creating a new email record

    tays01s

      Title

      Finding an email or creating a new email record

      Post

      I have 2 related tables: Users::__ID < Licenses::_UserID & Users::Lic_n - License::Lic_n

      I copy an email request into Users::Email_in. I then want to run a script that checks to see if:

      a. The email is already cited in Users::Email_get. If it is, it the record is displayed, if not, a new email record is created.

      b. There is already a license corresponding to the 'Lic_n', if not (including if there wasn't even a user email record, then a new license is created.

      I have used the script below:

      Set Variable [ $EmailFind; Value:Users::Email_in ]
      Set Variable [ $Lic_nFind; Value:Users::Lic_n ]
      Enter Find Mode [ ]
      Set Field [ Users::Email_get; "==" & Quote ($EmailFind) ]

      Perform Find [ ]
      If [ IsEmpty (Users::Email_get) ]

      New Record/Request

      Set Field [ Users::Email_get; $EmailFind ]

      Perform Script [ “New License” ]

      Go to Related Record [ From table: “Licenses”; Using layout: “Licenses” (Licenses) ] [ Show only related records; Match found set ]

      Enter Find Mode [ ]
      Set Field [ Licenses::Lic_n; "==" & Quote ($Lic_nFind) ]

      Perform Find [ ]

      Else If [ Licenses::__ID ]
      Perform Script [ “New License” ]

      Else

      End If

      What I find is that:

      1. When I already have a LIcense, the script creates a new License record.

      2. Conversely when I have a User::Email_get record already, but not the corresponding LIcense, the script fails to create a new license..

      3. Lastly, 'No records match the find criteria', is there a way to get the script to just get on with creating a new record without the dialogue (I thought there was a checkbox, but haven't seen it).

        • 1. Re: Finding an email or creating a new email record
          philmodjunk

          Your relationships is not clear. Is there one table occurrence box named Licenses and you left out the letter s in your description of your relationships or do you have two occurrences of the second table, one named licenses and one named license?

          As to your scripted finds, if you are going to search for records in users, you need to be sure that you are on a layout that specifies users in the layout Setup | Show Records from drop down. If you are going to search for a record in Licenses, you should do so on a layout based on Licenses unless you really want to find a record in users that is linked to a specific record in licenses--which does not appear to be the case here.

          My guess is that your script should look like this:

          Set Variable[$EmailFind; Value:Users::Email_in]
          Set Variable[$Lic_nFind; Value:Users::Lic_n]
          Go to Layout ["Users" (Users) ]---> this step is only needed if you are not already on the "users" layout.
          Enter Find Mode[ ]
          Set Field[Users::Email_get; "==" & Quote ($EmailFind)]
          Set Error Capture [on]--->keeps the "no records" dialog from interrupting your script
          Perform Find
          [ ]
          If[ Get (FoundCount ) = 0 // a safer test for no records found ]
             New Record/Request
             Set Field[Users::Email_get; $EmailFind]
          End IF
          Go to Layout [“Licenses” (Licenses)]
          Enter Find Mode[ ]
          Set Field[Licenses::Lic_n; "==" & Quote ($Lic_nFind)]
          Perform Find[ ]
          If[Get ( FoundCount ) = 0//original test was wrong here as it created a new record if one were found ]
             Perform Script[“New License”]
          End If

          For more examples of scripted finds, see: Scripted Find Examples

          Caulkins Consulting, Home of Adventures In FileMaking
          • 2. Re: Finding an email or creating a new email record
            tays01s

            Correct, there's only a 'Licenses' table; 'License' was a typo.

            Modifying the script as below, appears to correctly generate new licenses for per User (email) but after the first User record was generated, a new email (User) failed to create a new User record.

            Go to Layout [ “Users” (Users) ]
            Set Variable [ $EmailFind; Value:Users::Email_in ]
            Set Variable [ $Lic_nFind; Value:Users::Lic_n ]
            Set Variable [ $UserLicense; Value:Users::__ID ]
            Set Variable [ $ItemLicense; Value:Users::Lic_n ]
            Set Variable [ $Input; Value:Users::Input ]
            Enter Find Mode [ ]
            Set Field [ Users::Email_get; "==" & Quote ($EmailFind) ] Set Error Capture [ On ]
            Perform Find [ ]
            If [ Get(FoundCount)=0 //safer test than 'is empty' ]

            New Record/Request
            Set Field
            [ Users::Input; $Input ]
            Set Field [ Users::Email_get; $EmailFind ]

            End If
            Go to Layout
            [ “Licenses” (Licenses) ]
            Enter Find Mode [ ]
            Set Field [ Licenses::Lic_n; "==" & Quote ($Lic_nFind) ]

            Perform Find [ ]
            If [ Get(FoundCount)=0 ]

            New Record/Request
            Set Field
            [ Licenses::_UsersID; $UserLicense ] Set Field [ Licenses::_ItemsID; $ItemLicense ] Set Field [ Licenses::Lic_n; $ItemLicense ]

            End If

            • 3. Re: Finding an email or creating a new email record
              philmodjunk

              There's a difference between "table" and "Tutorial: What are Table Occurrences?". I was pretty sure that there was only one table, but your notation raised the possibility that there might be two table occurrences--especially as using two pairs of match fields and the symbols between their references didn't make any sense to me.

              I see no reason in your script, why putting a new, unique email address into Email_in would fail to create a new record in the Users table.

              Please recall my comments about the Go to layout step. It is not clear to me what layout is current at the time the script runs. If it is always "Users", then you don't need the go to layout step. If it is not "users" or is only sometimes "users", then the go to layout step could change what values are set to the variables in the steps immediately after the go to layout step. (this is probably not the source of the trouble, but it might be.)

              You can also simplify this kind of find if you make the field where the user enters search criteria--such as the email address a global field. Then you no longer need to copy the value to a variable before entering find mode, you can have the set field step refer to the global field in place of the variable.