6 Replies Latest reply on Aug 18, 2010 11:33 AM by Frinholp

    Custom Validation via Script

    Frinholp

      Title

      Custom Validation via Script

      Post

      Ok I have a few questions here that I have not been able to resolve over the last couple of days. If you read this post and can answer only one question your help would be greatly appreciated.

      I have decided to validate my data via script, bypassing Filemaker's native field validation. There are a couple of reasons for this (i) I would like to give the user a meaningful dialog when validation fails to give some indication as what to correct. (ii) Depending on where validation fails I would like to perform various different scripts for some fields.  

      (1) Checking for unique fields

      I would like to check for uniqueness for certain fields. I have setup a self-join relationship joining the tables with the fields I would like to be unique. I have a problem with the If statement I am using and I think it is my understanding of the Count function:

      If [Count (Table::Field ; SJTable::Field ) = 2]

      Show Custom Dialog[.............

      Exit Script[]

      End If 

      I am always getting a returned value of 2.

       

      (2) Flag uppercase

       I want to check if a field has been entered in uppercase and warn the user that caps lock may be switched on and flag that with a dialog asking if this was intentional. I thought the following return the boolean I want

       Table::FieldName  =  Upper ( Table::FieldName ))

      but no, they are both recognised as the same string.

      Is there a function that returns the ascii value?

      I have read that maybe I could switch to unicode language for the field in concern, but then my searches will be case sensitive. Is my understanding correct?

      (3) Detect change in field value

      I am currently using a custom function called scriptlog based on Nightwing Enteprises' Super/Ultralog.

      The code is as follows:

       

      Let(
      [
      // Set field name to be searched for

      _FieldName = FieldChanged;

      // Locate Any Previous Field Value in Log

      _FieldInLog = Position(LogField; Separator & _FieldName & Separator; 1; 1);
      _Divider = Separator & "-»" & Separator;
      _Start = Position(LogField; _Divider; _FieldInLog; 1) + Length(_Divider);
      _End = Position(LogField & ¶; ¶; _FieldInLog; 1);
      _PreviousValue = If(_FieldInLog; Middle(LogField; _Start; _End - _Start); "[---]");

      // New Field Value (fixes returns)

      _FieldValue = GetField(_FieldName);
      _FixedValue = Substitute(_FieldValue; ¶; "‡");
      _NewValue = If(Length(_FieldValue); _FixedValue; "[null]");

      // Format Values

      _%Date = GetAsDate(Get(CurrentHostTimeStamp));
      _%Time = GetCorrectedTime;
      //_%Time = GetAsTime(Get(CurrentHostTimeStamp));

      _%Account = Get(AccountName);
      _%Field = _FieldName;
      _%Change = _PreviousValue & Separator & "-»" & Separator & _NewValue;
      _%Table = Get (LayoutTableName);
      _%Layout = Get(LayoutName);
      _%Key = GetAsNumber (Get(CurrentHostTimeStamp)) & _PreviousValue & _NewValue;

      _LogFormat = Substitute( Format;
                                    ["%Date"; _%Date & Separator];
                                    ["%Time"; _%Time & Separator];
                                    ["%Account"; _%Account & Separator];
                                    ["%Field"; _%Field & Separator];
                                    ["%Change"; _%Change & Separator];
                                    ["%Table"; _%Table & Separator];
                                    ["%Layout"; _%Layout & Separator];
                                    ["%Key"; _%Key & Separator]
                                    );
      _LogString = Left( _LogFormat; Length( _LogFormat ) - Length( Separator ) )

      ];

        _LogString & ¶  & LogField
       
      )

      Now I need to detect a change in field and either

      (i) modify the function to only write to the log if the field value has changed.

      Problem: I can't for some reason insert an If statement to check for a change within the function. Debugging is proving to be problematic as I cant watch the variables and step through the function.

      (ii) Detect changes in fields via script then execute the function if there has been a change.

      Problem: I am setting local variables to the existing values of the fields in the record before editing. I then pause the script indefinately until a submit button has been pressed, but I don't know how to unpause the script.

      Obviously the first solution would be the best way to go about the problem but I seem to be getting nowhere.

      Please help, as I say even if only on one matter as I'm completely stuck!

      Thanks in advance

      Lee