12 Replies Latest reply on Dec 19, 2015 3:20 PM by disabled_paddo2002

    If Exists function for field Validation

      Hi all,

      I was just wondering if there is such a thing as an If Exists function? The reason is the solution I am working on has a date created and surname field. What I want to do is in the validation part, put in a calculation that checks to see if the surname and first name for a person has not already been entered today. I am OK if the get entered twice on two separate days, but not on the same day.

       

       

      If someone can suggest the calculation I would be most grateful.

       

      Regards

        • 1. Re: If Exists function for field Validation
          BruceRobertson

          There is an opposite expression you can use; isEmpty.

          See help for isEmpty and examples here:

           

          IsEmpty

          • 2. Re: If Exists function for field Validation
            Menno

            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):

            IsEmpty (

                 ExecuteSQL (

                      "SELECT \"Surname\" " &

                      "FROM \"IfExists\" " &

                      "WHERE Lower(\"Surname\")=? " &

                      "AND \"Date created\"=? " &

                      "AND \"ID\"!=?" ;

                           "" ; "" ;

                      Lower ( Surname ) ;

                      Get ( CurrentDate ) ;

                      ID )

            )

            You have to add an (unique) ID-field though, otherwise the record you are currently creating or editing will fail the validation.

            • 3. Re: If Exists function for field Validation
              FileKraft

              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..

              • 4. Re: If Exists function for field Validation
                Menno

                @FileKraft:

                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 )

                • 5. Re: If Exists function for field Validation
                  siplus

                  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.

                  • 6. Re: If Exists function for field Validation
                    FileKraft

                    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 ..

                    • 7. Re: If Exists function for field Validation
                      Menno

                      FileKraft wrote:

                      TO is magically working even on open set of records ..

                      On the same computer yes, in a network no.

                      • 8. Re: If Exists function for field Validation
                        Menno

                        FileKraft wrote:

                        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.

                        • 9. Re: If Exists function for field Validation
                          FileKraft

                          transactions with uncommitted records in portal ..

                          • 10. Re: If Exists function for field Validation

                            HI all,

                            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.

                             

                            Many thanks

                            Paddo2002

                            • 11. Re: If Exists function for field Validation
                              taylorsharpe

                              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. 

                              • 12. Re: If Exists function for field Validation

                                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.

                                 

                                Best Regards