You want to validate an entry, so you want check if a value does not exist and then the IsEmpty() is an excellent suggestion from Bruce.
You asked for a suggestion for a formula, so here is one that uses ExecuteSQL() (substitute IfExists for your own table-name):
"SELECT \"Surname\" " &
"FROM \"IfExists\" " &
"WHERE Lower(\"Surname\")=? " &
"AND \"Date created\"=? " &
"AND \"ID\"!=?" ;
"" ; "" ;
Lower ( Surname ) ;
Get ( CurrentDate ) ;
You have to add an (unique) ID-field though, otherwise the record you are currently creating or editing will fail the validation.
an ExecuteSQL query for validation on the same table is a bad idea because it will be extremely slow executing the query if a record is open which is the case when you validate.
create a self join instead and check via isempty. relate the self-joined TO to the name and date to check for existence..
I disagree with you, it is not a bad idea. I tested this setup a moment ago in a network with three clients each with 2 records in "open state" from the same shared FM-database with 50000+ records entered of the past 10 years and then started editing and entering records on the third client. There is no noticeable difference in speed in that real world situation.
This particular SQL-query is targeting only records for the current date and maybe it slows down considerable when a large set of data is queried. But i suppose that OP won't be entering 10000's of records each day, so I think this works fine for him.
Your suggestion works fine too, but this field is probably not the only field that will be validated for some reason and I personally like to avoid creating TO's for only validations. I am not saying one should not avoid using SQL (because if you write a sql-query careless, it can "halt" a solution, you're correct on that), but I would prefer to be a bit more pragmatic about it: if the impact is acceptable, apply a solution that fits your taste. In my case that is using ExecuteSQL (ich wil ja nicht päpstlicher als der Papst sein )
Get data in globals in a popover with an ok button, on ok run your sql or look at a count of related records, exit script [false] if matching or found or Count or whatever is greater than 0.
It's vital to understand the problem, which is the fact that you're trying to validate a record (implying the combo of 3 different fields) and not a single field.
Therefore field validation is the wrong approach.
not convinced - the index is not available completely for ExecSql if record(s) of same table by same user are open. TO is magically working even on open set of records ..
TO is magically working even on open set of records ..
On the same computer yes, in a network no.
if record(s) of same table by same user are open
Why would a user create 2 records at the same time? Why would I program my solution in such a way that a user even would be able to do that? To an extend I understand your reservation against using ExecuteSQL for validation, but there are lots of tradeoffs for any function or procedure in FileMaker. For example a recursive CF may only do 10000/50000 iterations, that stil is not a reason not to create recursive CF's.
transactions with uncommitted records in portal ..
Thank you for the very quick response. I will give the SQL a go. Typically I have 2 users on in the network, and they would not be entering the same record. This is a relatively small setup, and so if there is a performance issue, I probably won't notice it. I will give it a go and report back.
ExecuteSQL will work and is often opens up calculations that would otherwise be very hard or impossible. And I use it a lot. But take note of what FileKraft is saying. He's not saying it won't work, but just be aware of the issue where FileMaker does ExecuteSQL much slower when a record is open. It is like other slow functions and unstored calculations, etc.. You can use them, just be aware of the time penalty that might be associated, particularly when scaling a solution or to avoid if you're trying to make things go faster. If the time penalty is not hurting your solution, then go for it.
Hi Menno and all,
I tried this formula with a couple of slight changes and it works a treat. Thank you Menno for this very helpful formula.