AnsweredAssumed Answered

Custom Validation via Script

Question asked by Frinholp on Aug 18, 2010
Latest reply on Aug 18, 2010 by 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 

Outcomes