Search based on field content

[Columbo]

I am new to FMPA 18 and trying to find out if a name already exists in the database and I don’t know if I can explain this clearly but,... The idea is that I have a button that runs a script to add a new record which is simply:

 

1 New Record/Request

2 Go To Field [PrehistoricLife::Name]   //Sets the focus in the Name field

 

Then a name is entered into the Name field and when <TAB> is pressed to go to the next field, (exits the Name field), it runs another script that searches the database based on the name that was entered into the name field to see if it already exists.

 

I set the Name field trigger to OnObjectExit, figuring that when a name is entered and the <TAB> is pressed to go to the next field, it will trigger the Find script but for some reason it is running the script as soon as I click the Add Record button. The New Record form comes up and the cursor is in the Name field  but I immediately get the dialog stating that no records match the criteria.  No name has yet been entered and the field has not yet been exited.

 

It seems that a script to check to see if a name already exists should be fairly simple.  Can someone give me an example of such a script in FMPA?

 

Thanks

[Steve Martino]

Technically easy, but it depends on the details.  What if someone enters the same name but slightly misspells it?

Google:

filemaker check for duplicate records

[rivet]

For creating and editing records, I am a fan of form tables.  A form table contains only global (storage type) fields that match the field names in your main table (typically a subset of fields). 

When you are creating and editing in a form, all your validation and other logic is contained in that form. You can even add extra form fields for UI elements and user feedback. This approach keeps your main layout and table clean. 

I have attached a simple example that shows if there is a match on the first name and if the form is valid ( first and last names are not empty ).  Hope that is helpful.

 

 

form tables v2.fmp12.zip

Edited March 9, 2023 by rivet

[comment]

6 hours ago, Columbo said:

I set the Name field trigger to OnObjectExit  ...  for some reason it is running the script as soon as I click the Add Record button

We don't see your file and therefore cannot reproduce the problem. In general, a script triggered OnObjectExit does not run on creation of a new record - so either there's something else triggering the script or there is something in your Add Record script that exits the field.

Even in more general, you can also use a relationship to find if a name already exists. Or perhaps validating the field as unique would be sufficient? The problem with using a find is that it may also find the current record. Anyway, I won't make a suggestion without knowing what exactly you want to happen if a duplicate is detected.

 

1 hour ago, rivet said:

For creating and editing records, I am a fan of form tables.  A form table contains only global (storage type) fields

That is a very problematic suggestion, at least in a multi-user scenario - see here why:
https://fmforums.com/topic/47273-portal-filter-and-highlight-row/?do=findComment&comment=221213&_rid=72594

At minimum you should start by locking the edited record.

But even for a single user I don't see the advantage of the added complexity. A record being edited (incl. a new record) already holds "draft" fields for user entry. All you need to do is control when the record commits.

 

 

[Columbo]

BTW:  My attempt at a Find script is as follows:

 

Set Error Capture [Off]
Go To Field [PrehistoricLife::Name]
Set Variable [$$newName; Value: Get(ActiveFieldContents)]
Enter Find Mode [Pause: Off]
Perform Find [Restore]
If [ Get (FileCount) > 0]
   Show Custom Dialog [“Duplicate Record”; “File Already Exists”]
       If [Get (LastMessageChoice) = 2]
          Clear [Select; PrehistoricLife::Name]
          Show All Records
       End If
Else
     Go To Field [PrehistoricLife::Meaning]
     Show All Records
End If

 

[Columbo]

13 hours ago, rivet said:

I have attached a simple example that shows if there is a match on the first name and if the form is valid ( first and last names are not empty ).  Hope that is helpful.

I looked at your example but to be honest, I'm not sure what is happening there.  I tried entering a record in your example to see if it recognized that the name I entered was already in the database but the save button would not work.  What I am trying to achieve is a new record is being added and a name is entered in the name field, it checks to see if that name is already in the database, before the user fills out the rest of the information.  (Saves a lot of typing for nothing if the file already exists).

Here is my script for checking if the file already exists but it may well be incorrect.

Set Error Capture [Off]

Go To Field [PrehistoricLife::Name]

Set Variable [$$newName; Value: Get(ActiveFieldContents)]

Enter Find Mode [Pause: Off]

Perform Find [Restore]

If [ Get (FileCount) > 0]

   Show Custom Dialog [“Duplicate Record”; “File Already Exists”]

       If [Get (LastMessageChoice) = 2]  // If name already exists.

          Clear [Select; PrehistoricLife::Name]

          Show All Records

       End If

Else

     Go To Field [PrehistoricLife::Meaning]   // If name does not exist.

     Show All Records

End If

[Steve Martino]

What's get (filecount)?