I'm using this to check for date clashes when booking a room.

The idea is that when entering a new booking, as soon as a date is chosen in the "Date" field drop-down calendar - a script will run that has Find Matching Records [ Replace ; Date_Happening ] in it.

That works, but I can only see the results if I include a View As [ Table/Form ] line - which isn't what I want.

In the interest of speedy data entry, I don't want to do this checking in another form, or using another dialog box. I'd like it to just pop up a warning only if the date chosen already has something booked there.

I've tried other ways using a summary field in a sub-summary, that works as well - showing me a number representing how many other records have that date.   

But all I really want is just a pop-up warning only if this is true.

36 minutes ago, Bullion said:

The idea is that when entering a new booking, as soon as a date is chosen in the "Date" field drop-down calendar - a script will run that has Find Matching Records [ Replace ; Date_Happening ] in it.

I don't think that's a good idea, because performing the find will take you to the first record of the found set - so if a duplicate exists you will lose your place and your draft record will be committed. But, to answer your question as asked, you could test if Get ( FoudCount ) > 1  (since the current record will always be found).

Perhaps consider using a relationship to test for duplicates. This way you can restrict the match not only to the same date but also to the same room. Another option is to use validation - but that does not create a pleasant user experience.

 

Thanks so much for your response!

I was looking into FoundCount - but it wasn't coming up as an autocomplete option in my Script Workspace?  I also searched in the script terms panel and it didn't come up. V19 - I was wondering whether it had been removed or something?

Hadn't thought of a calculation in Validation!  But I'll take your word on the user experience - I'm guessing it's because you can't customise the not-validated message?

What kind of relationship do you suggest?  Are you suggesting using a "temporary" field for date entry, that after checking that date is free, then gets copied to the actual record date field?  Would I use a global field for this?

EDIT: Actually I've found this article which seems to be describing it really well: https://www.philosophyoffilemaker.com/preventing-duplicates-168

Edited May 6, 20223 yr by Bullion

2 hours ago, Bullion said:

I was looking into FoundCount - but it wasn't coming up as an autocomplete option in my Script Workspace?

Get ( FoundCount ) is a function, not a script step. The script step you want to use is If , and within its Specify Calculation  option you would use the formula above. 

 

2 hours ago, Bullion said:

I'm guessing it's because you can't customise the not-validated message?

More precisely, it's because you cannot control the options given to the user (you can specify your own message text).

 

2 hours ago, Bullion said:

What kind of relationship do you suggest?

I suppose:

Bookings::Room = Bookings 2::Room
AND
Bookings::Date = Bookings 2::Date
AND
Bookings::BookingID ≠ Bookings 2::BookingID
 

 

Thanks so much. I’ll give that a try. 

This is so strange, and is no doubt because of something I'm not understanding. But this is not working for me. I've tried it several different times and ways.  Based on several articles about it.

As an experiment I have shifted over to trying a script to detect duplicates; this one from Claris

Show All Records
Sort Records [Restore; No dialog] <--- Sort by the UniqueID field in ascending order
Go to Record/Request/Page [First]
Set Variable [$id; Value:SampleTable::UniqueID] <-- Sets "$id" variable to be equal to UniqueID field
Replace Field Contents [No dialog; SampleTable::Mark; " "] <--- Replace the contents of the "Mark" field with a blank (" ")
Loop
     Go to Record/Request/Page [Next, Exit after last]
     If [$id = SampleTable::UniqueID]
          Set Field [SampleTable::Mark; "X"] <--- Marks record as a duplicate by placing an "X" in the "Mark" field
     Else
          Set Variable [$id; Value:SampleTable::UniqueID]
     End If
End Loop
Perform Find [Restore]

Which I have tried with several different fields being the "UniqueID" field.

The Date one that I want this to work on. A RoomID field which is from a Value List. I've also tried a unique field that is a calculation field putting together the date and the Record UUID field into a single text field to make sure it's *really* unique!

All of them end up marking every single record as a duplicate, except for the one based on the calculation field. Which returns "no matching records found".

Can you see anything that I'm missing that is obvious?

 

Thanks

I am afraid I couldn't say what's wrong with your implementation without seeing it. In any case, this method is not suitable for your situation, because - same as before - running the script will cause you to lose your place and commit the draft record. Not to mention that this script can easily fail if some records are locked by other users.

See if the attached demo works as you would expect. Note that there are actually two warnings you'll get: one via a script triggered by modifying the Date field, the other through conditional formatting.

 

DetectDuplicate.fmp12