EnsureNewRecordExists

Here's a FileMaker script that creates a new record with a given value for the recordID field, but skips the creation if a record with the same recordID value already exists:

# Step 1: Get the value for the recordID field
Let ( [
    newRecordID = Get ( ???  ) # Replace with the field or calculation that should provide the new recordID value
];

# Step 2: Check if a record with the given recordID already exists
If ( 
    IsEmpty ( 
        Get ( FoundCount )
    );

    # Step 3: Create a new record with the given recordID
    New Record/Request;
    Set Field [ $$Table::recordID; newRecordID ]
)

Here's a breakdown of the script:

1. Get the value for the recordID field: The first step is to get the value that should be used for the recordID field. This can be done by getting the value from a field or a calculation.

2. Check if a record with the given recordID already exists: The script uses the Get(FoundCount) function to check if any records with the given recordID value already exist. If the FoundCount is empty, it means no records were found.

3. Create a new record with the given recordID: If no record with the given recordID value was found, the script creates a new record and sets the recordID field to the provided value.

Make sure to replace the placeholder $$Table::recordID with the actual field name for the recordID field in your database. Also, replace the Get(...) function call with the appropriate field or calculation that should provide the new recordID value.

This script will ensure that a new record is created only if a record with the given recordID value does not already exist in the database.

# Script Name: Create New Record with Unique ID

# Declare variables
Let ( [
	$recordID = "YOUR_UNIQUE_ID"  # Replace with the desired value for the recordID field
] ;

# Check if a record with the given ID already exists
If ( 
	# Use a Find command to search for a record with the specified ID
	Find ( 
		recordID = $recordID 
	) ;

	# If a record is found, show an error message
	Show Message ( "A record with ID " & $recordID & " already exists!" ) ;

	# Exit the script if a record exists
	Exit Script ;
) ;

# Create a new record
New Record/Request ;

# Set the value of the recordID field
Set Field [ recordID ; $recordID ] ;

# Optionally, perform other actions after creating the record
# ...

Explanation:

1. Declare variables:

   • $recordID: This variable holds the unique ID value you want to use for the new record. Replace "YOUR_UNIQUE_ID" with the actual value.

2. Check for existing record:

   • Find command: This searches for existing records where the recordID field matches the value stored in the $recordID variable.
   • If statement:
     • If a record is found (the find was successful), an error message is displayed.
     • Exit Script: This stops the script from creating a duplicate record.

3. Create new record:

   • New Record/Request: This creates a new empty record.

4. Set the recordID field:

   • Set Field: This command sets the value of the recordID field in the newly created record to the value stored in the $recordID variable.

5. Optional Actions:

   • You can add any additional actions you want to perform after creating the record within this section.

Key Points:

• Replace "YOUR_UNIQUE_ID": Make sure to replace this placeholder with the actual value you want to use for the recordID field.
• Field name: Ensure that recordID is the correct name of the field in your database.
• Error message: You can customize the error message to be more specific to your needs.
• Additional Actions: You can add more script steps to populate other fields in the new record, perform calculations, or trigger other actions.

To create a FileMaker script that adds a new record with a specified recordID, while ensuring that no duplicate recordID values exist, follow these steps:

1. Open Script Workspace:

   • In FileMaker, go to the "Scripts" menu and select "Script Workspace."

2. Create a New Script:

   • Click on the "+" button or select "New Script" to create a new script.

3. Script Steps:

   • Use the following steps in the script to achieve your goal:

# Script: Create New Record with Unique recordID

# Get the intended recordID from a dialog or a passed script parameter
Set Variable [$recordID; Value:Get(ScriptParameter)]

# Search for duplicates
Enter Find Mode [Pause: Off]
Set Field [YourTable::recordID; $recordID]
Perform Find []

# Check if any records were found
If [Get(FoundCount) = 0]
    # No duplicate found, proceed to create a new record
    New Record/Request
    Set Field [YourTable::recordID; $recordID]
    Commit Records/Requests
Else
    # Duplicate found, optionally notify the user
    Show Custom Dialog ["Duplicate Record"; "A record with this recordID already exists."]
End If

4. Save the Script:
   • Click "Save" or press Cmd + S (Mac) / Ctrl + S (Windows) to save your script.

Detailed Explanation:

• Set Variable [$recordID; Value:Get(ScriptParameter)]: This script step captures the recordID from a script parameter. You can also modify the script to get this value from another source like a global field or a user dialog.

• Enter Find Mode [Pause: Off]: This enters find mode to perform a search without pausing.

• Set Field [YourTable::recordID; $recordID]: Sets the recordID field to the value that needs to be searched.

• Perform Find []: Executes the find request to look for any matching records.

• If [Get(FoundCount) = 0]: Checks if any records were found with the given recordID. If none are found, it implies that it's safe to create a new record.

• New Record/Request: Creates a new record in the current table context.

• Set Field [YourTable::recordID; $recordID]: Sets the recordID field of the new record to the desired value.

• Commit Records/Requests: Saves the new record with the committed data input.

• Show Custom Dialog: Provides user feedback if a record with the same recordID already exists. You can customize this dialog or the entire approach based on your requirements.

This script ensures that no duplicate recordID is inserted into the recordID field by checking the records first and only adding the new entry if it does not exist. Make sure to replace YourTable::recordID with the actual table and field name in your database.

Here's a step-by-step guide to creating a FileMaker script that creates a new record with a given value for field recordID unless a record with that ID already exists.

Create a New Script

1. Go to the "Scripts" workspace in FileMaker Pro or FileMaker Pro Advanced.
2. Click the "New Script" button.
3. Name the script, e.g., "Create New Record with Unique ID".

Script Steps

1. Set Variable: Set a variable to hold the record ID value. We'll call this variable $recordID.

   • Script Step: Set Variable
   • Variable Name: $recordID
   • Value: // prompt the user to input the record ID
   • For a more robust solution, you could use a prompt to get the record ID from the user, or get it from another field or calculation.

2. Perform Find: Perform a find for records with the specified recordID value to check if such a record already exists.

   • Script Step: Perform Find
   • Criteria: recordID = $recordID
   • Select: go to layout option to access the layout where recordID is located.

3. If No Records Match: Check if any records were found with the specified recordID.

   • Script Step: If
   • Condition: Get ( FoundCount ) = 0

4. Create New Record: If no records were found, create a new record and set its recordID field.

   • Script Step: New Record / Request
   • Select: perform without dialog option.
   • Script Step: Set Field
   • Target: recordID
   • Value: $recordID

5. Else: Show a message or alert to indicate that a record with the specified recordID already exists.

   • Script Step: Else
   • Script Step: Show Custom Dialog
   • Message: A record with the record ID $recordID already exists.

6. End If: Close out the If condition.

• Script Step: End If

Example Script

# Create New Record with Unique ID
Set Variable [ $recordID; Value:Input ( "" ; "Please enter a record ID" ) ]
Perform Find [ Specified Find Requests: Find Records; Criteria: recordID = $recordID ]
If [ Get ( FoundCount ) = 0 ]

Here is a possible FileMaker script that creates a new record with a given value for the field recordID only if there is no existing record with that value:

1. Go to the layout for the table where you want to create the new record.
2. Set a variable, e.g. $recordID, to the value you want to use for the recordID field.
3. Perform a Find request to search for any existing records with the same recordID value. Set the search criteria to recordID = $recordID.
4. If the Find request returns any records, exit the script without creating a new record. You can use the Get(FoundCount) function to check if any records were found.
5. If no records were found, create a new record by performing the New Record/Request script step.
6. Set the value of the recordID field in the new record to the value of the $recordID variable.
7. Commit the record by performing the Commit Record/Request script step.

Here is an example of what the script might look like:

Go to Layout [“Table Name”]
Set Variable [$recordID; Value: “your value here”]
Perform Find [Set Error Capture: On; Criteria: recordID = $recordID]
If [Get(FoundCount) > 0]
  Exit Script []
End If
New Record/Request
Set Field [Table Name::recordID; $recordID]
Commit Record/Request []

Note that this script assumes that the recordID field is unique and that you want to prevent duplicate values. If this is not the case, you may need to modify the script accordingly.

I hope this helps! Let me know if you have any questions.