Custom Validation via Script
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[.............
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:
// 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