validation scripting
am 17.04.2008 09:19:38 von FastWolf
Here's the bare bones of a validation script I'm working on. Steps in
brackets [] represent functions that don't seem to exist in FMPA:
* set field ( titles::t_idv ; titles_lookup::t_idc )
* [ check validation of t_idv ]
* If [ titles::t_idv does pass validation ]
* set field ( titles::t_id ; titles_lookup::t_idv )
* commit records
* halt script
* EndIf
* If [ titles::t_idv does not pass validation ]
* set field ( titles::t_idv ; titles_lookup::t_idc&"a" )
* EndIf
* [ check validation of t_idv ]
* If [ titles::t_idv does pass validation ]
* set field ( titles::t_id ; titles_lookup::t_idv )
* commit records
* halt script
* EndIf
where:
# titles_lookup = another table occurance of titles
# titles::t_id = text field, must be unique
# titles::t_idc = calc field that generates a text string by grabbing
characters from another text field
# titles:t_idv = text field, must be unique
I want to create unique values for t_id. There doesn't seem to be a
function for testing validation.
My idea was to generate an ID (t_idc), stick it in a field (t_idv),
and check to see if it already exists in any record by using a value
list. The value list consists of all values in t_id. If it's not in
the list (does pass validation), then stick it in t_id and save it. If
it is in the list (does not pass validation), then append "a" to the
end of the ID and check it again. This way the ID is held out of the
value list for comparison purposes, and isn't added to the list if
it's in there already. It seems like there ought to be a way to do
this using the ValueListItems function.
thanks in advance
--
FW
FileMaker Pro Advanced 8.5 on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2
Re: validation scripting
am 17.04.2008 09:40:26 von E W P Appeldoorn
"FastWolf" schreef in bericht
news:e2ud04lb0ost5kelrfrrb2ivkvej0q089n@4ax.com...
> Here's the bare bones of a validation script I'm working on. Steps in
> brackets [] represent functions that don't seem to exist in FMPA:
>
> * set field ( titles::t_idv ; titles_lookup::t_idc )
> * [ check validation of t_idv ]
> * If [ titles::t_idv does pass validation ]
> * set field ( titles::t_id ; titles_lookup::t_idv )
> * commit records
> * halt script
> * EndIf
> * If [ titles::t_idv does not pass validation ]
> * set field ( titles::t_idv ; titles_lookup::t_idc&"a" )
> * EndIf
> * [ check validation of t_idv ]
> * If [ titles::t_idv does pass validation ]
> * set field ( titles::t_id ; titles_lookup::t_idv )
> * commit records
> * halt script
> * EndIf
>
> where:
>
> # titles_lookup = another table occurance of titles
> # titles::t_id = text field, must be unique
> # titles::t_idc = calc field that generates a text string by grabbing
> characters from another text field
> # titles:t_idv = text field, must be unique
>
> I want to create unique values for t_id. There doesn't seem to be a
> function for testing validation.
>
> My idea was to generate an ID (t_idc), stick it in a field (t_idv),
> and check to see if it already exists in any record by using a value
> list. The value list consists of all values in t_id. If it's not in
> the list (does pass validation), then stick it in t_id and save it. If
> it is in the list (does not pass validation), then append "a" to the
> end of the ID and check it again. This way the ID is held out of the
> value list for comparison purposes, and isn't added to the list if
> it's in there already. It seems like there ought to be a way to do
> this using the ValueListItems function.
>
FW,
Since the values pre-exist, you could easily check if a value was used
through the relationship with the function isvalid(titles::t_id ;
titles_lookup::t_idv). Returns True when the relationship is valid (The
value already exists). You could also create a calculated field in one of
the tables, with the same calculation and check that value with an IF. Both
solutions have the same result.
You don't need any hagling with values in ValueLists, this would be
cumbersome at best.
Keep well, Ursus
Re: validation scripting
am 17.04.2008 16:02:50 von Grip
On Apr 17, 1:19 am, FastWolf wrote:
>
>
> # titles_lookup = another table occurance of titles
> # titles::t_id = text field, must be unique
> # titles::t_idc = calc field that generates a text string by grabbing
> characters from another text field
> # titles:t_idv = text field, must be unique
>
> I want to create unique values for t_id. There doesn't seem to be a
> function for testing validation.
>
> My idea was to generate an ID (t_idc), stick it in a field (t_idv),
> and check to see if it already exists in any record by using a value
> list. The value list consists of all values in t_id. If it's not in
> the list (does pass validation), then stick it in t_id and save it. If
> it is in the list (does not pass validation), then append "a" to the
> end of the ID and check it again. This way the ID is held out of the
> value list for comparison purposes, and isn't added to the list if
> it's in there already. It seems like there ought to be a way to do
> this using the ValueListItems function.
>
> thanks in advance
>
> --
> FW
>
> FileMaker Pro Advanced 8.5 on Windows XP Pro SP2
> FileMaker Server 8.0 on Windows 2003 Server R2
To check if a record passes validation by calculation, you can:
Set Error Capture [On]
Commit Record/Request
If[Get(LastErrorMessage) =/= 507]
do this
Else If
do that
End If
But I agree the method you're using sounds clunky, but I do like the
use of Value Lists. Instead of using FM's validation tools, I would
use this series of script steps to get a valid field value:
Set Variable[$list; ValueListItems(Get(FileName); "ValueListName")
Loop
Exit Loop If[ IsEmpty(FilterValues( $list; t_id ))]
Set [$counter; $counter + 1]
Set Field[t_id; t_id & $counter]
End Loop
G
Re: validation scripting
am 17.04.2008 22:50:42 von Helpful Harry
In article , FastWolf
wrote:
> I want to create unique values for t_id. There doesn't seem to be a
> function for testing validation.
The usual way to get unique values for IDs is to use an auto-enter
serial number. If needed this incrementing value can be appended to the
end of other text, still resulting in a unique value.
Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)
Re: Re: validation scripting
am 21.04.2008 23:32:20 von FastWolf
On Fri, 18 Apr 2008 08:50:42 +1200, Helpful Harry
wrote:
>In article , FastWolf
> wrote:
>
>> I want to create unique values for t_id. There doesn't seem to be a
>> function for testing validation.
>
>The usual way to get unique values for IDs is to use an auto-enter
>serial number. If needed this incrementing value can be appended to the
>end of other text, still resulting in a unique value.
Right you are, Harry, and I do use that method in this solution.
However, I also need to generate a completely different set of unique
identifiers based on a text string because the identifier gets plugged
into another text string and the whole thing is used as a filename on
a Web server.
The next thing you (probably) are going to say is, why not just use
the auto-generated numeric ID for the filename? And that would be a
valid suggestion. In this case, however, I am taking an existing
database that was built in mySQL (using Servoy as a front-end) and
*converting* it to FMP. Well, not converting actually, what I'm doing
is building a FileMaker solution that's WAY WAY more user-friendly,
robust, and stable than the Servoy app they had. The point is, they
are kind of locked into this method of naming their files so I need to
make it work that to make them happy.
Because Servoy hasn't worked all that well for them up to now. Prior
to this they were using a bit of Java code on a different Web server
to generate the ID, but that script keeps crashing. All manual entry
too. Not only that but it never did validate the unique value. At
this point their db is small enough where I have culled out and
replaced the dupes manually. But going forward I need an automated
solution to this problem.
thanks
--
FW
FileMaker Pro Advanced 8.5 on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2
Re: Re: validation scripting
am 21.04.2008 23:38:28 von FastWolf
On Thu, 17 Apr 2008 07:02:50 -0700 (PDT), Grip
wrote:
>On Apr 17, 1:19 am, FastWolf wrote:
>>
>>
>> # titles_lookup = another table occurance of titles
>> # titles::t_id = text field, must be unique
>> # titles::t_idc = calc field that generates a text string by grabbing
>> characters from another text field
>> # titles:t_idv = text field, must be unique
>>
>> I want to create unique values for t_id. There doesn't seem to be a
>> function for testing validation.
>>
>> My idea was to generate an ID (t_idc), stick it in a field (t_idv),
>> and check to see if it already exists in any record by using a value
>> list. The value list consists of all values in t_id. If it's not in
>> the list (does pass validation), then stick it in t_id and save it. If
>> it is in the list (does not pass validation), then append "a" to the
>> end of the ID and check it again. This way the ID is held out of the
>> value list for comparison purposes, and isn't added to the list if
>> it's in there already. It seems like there ought to be a way to do
>> this using the ValueListItems function.
>>
>> thanks in advance
>>
>> --
>> FW
>>
>> FileMaker Pro Advanced 8.5 on Windows XP Pro SP2
>> FileMaker Server 8.0 on Windows 2003 Server R2
>
>To check if a record passes validation by calculation, you can:
>
>Set Error Capture [On]
>Commit Record/Request
>If[Get(LastErrorMessage) =/= 507]
>do this
>Else If
>do that
>End If
Grip,
I love the elegance of this but I can't make it work. I'm sure I'm
doing something wrong so I'll keep working on it -- I just wanted to
thank you for your help.
>But I agree the method you're using sounds clunky, but I do like the
>use of Value Lists. Instead of using FM's validation tools, I would
>use this series of script steps to get a valid field value:
>
>Set Variable[$list; ValueListItems(Get(FileName); "ValueListName")
>Loop
>Exit Loop If[ IsEmpty(FilterValues( $list; t_id ))]
>Set [$counter; $counter + 1]
>Set Field[t_id; t_id & $counter]
>End Loop
Now THIS I like -- I know FMP's validation tools are very good but
there's something about scripting my own validation that appeals to
me. When I get a chance I'm going to mess around with this.
thanks again
--
FW
FileMaker Pro Advanced 8.5 on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2
Re: Re: validation scripting
am 21.04.2008 23:42:08 von FastWolf
On Thu, 17 Apr 2008 09:40:26 +0200, "Ursus"
wrote:
>
>"FastWolf" schreef in bericht
>news:e2ud04lb0ost5kelrfrrb2ivkvej0q089n@4ax.com...
>> Here's the bare bones of a validation script I'm working on. Steps in
>> brackets [] represent functions that don't seem to exist in FMPA:
>>
>> * set field ( titles::t_idv ; titles_lookup::t_idc )
>> * [ check validation of t_idv ]
>> * If [ titles::t_idv does pass validation ]
>> * set field ( titles::t_id ; titles_lookup::t_idv )
>> * commit records
>> * halt script
>> * EndIf
>> * If [ titles::t_idv does not pass validation ]
>> * set field ( titles::t_idv ; titles_lookup::t_idc&"a" )
>> * EndIf
>> * [ check validation of t_idv ]
>> * If [ titles::t_idv does pass validation ]
>> * set field ( titles::t_id ; titles_lookup::t_idv )
>> * commit records
>> * halt script
>> * EndIf
>>
>> where:
>>
>> # titles_lookup = another table occurance of titles
>> # titles::t_id = text field, must be unique
>> # titles::t_idc = calc field that generates a text string by grabbing
>> characters from another text field
>> # titles:t_idv = text field, must be unique
>>
>> I want to create unique values for t_id. There doesn't seem to be a
>> function for testing validation.
>>
>> My idea was to generate an ID (t_idc), stick it in a field (t_idv),
>> and check to see if it already exists in any record by using a value
>> list. The value list consists of all values in t_id. If it's not in
>> the list (does pass validation), then stick it in t_id and save it. If
>> it is in the list (does not pass validation), then append "a" to the
>> end of the ID and check it again. This way the ID is held out of the
>> value list for comparison purposes, and isn't added to the list if
>> it's in there already. It seems like there ought to be a way to do
>> this using the ValueListItems function.
>>
>
>FW,
>
>Since the values pre-exist, you could easily check if a value was used
>through the relationship with the function isvalid(titles::t_id ;
>titles_lookup::t_idv). Returns True when the relationship is valid (The
>value already exists). You could also create a calculated field in one of
>the tables, with the same calculation and check that value with an IF. Both
>solutions have the same result.
>
>You don't need any hagling with values in ValueLists, this would be
>cumbersome at best.
>
>Keep well, Ursus
Ursus,
I'm sure I missed something -- however the
"isvalid(titles::t_id ; titles_lookup::t_idv)"
syntax isn't correct for the IsValid function. Is there a self-join
or something implied here?
thanks
--
FW
FileMaker Pro Advanced 8.5 on Windows XP Pro SP2
FileMaker Server 8.0 on Windows 2003 Server R2
Re: Re: validation scripting
am 22.04.2008 10:46:24 von E W P Appeldoorn
>>>
>>
>>FW,
>>
>>Since the values pre-exist, you could easily check if a value was used
>>through the relationship with the function isvalid(titles::t_id ;
>>titles_lookup::t_idv). Returns True when the relationship is valid (The
>>value already exists). You could also create a calculated field in one of
>>the tables, with the same calculation and check that value with an IF.
>>Both
>>solutions have the same result.
>>
>>You don't need any hagling with values in ValueLists, this would be
>>cumbersome at best.
>>
>>Keep well, Ursus
>
> Ursus,
>
> I'm sure I missed something -- however the
> "isvalid(titles::t_id ; titles_lookup::t_idv)"
> syntax isn't correct for the IsValid function. Is there a self-join
> or something implied here?
>
> thanks
>
> --
Implied was a relationship between titles and titles_lookup. Since you use
the suffix _lookup I thought you already had one defined here.
Keep well, Ursus
Re: Re: Re: validation scripting
am 22.04.2008 19:28:16 von FastWolf
On Tue, 22 Apr 2008 10:46:24 +0200, "Ursus"
wrote:
>>>>
>>>
>>>FW,
>>>
>>>Since the values pre-exist, you could easily check if a value was used
>>>through the relationship with the function isvalid(titles::t_id ;
>>>titles_lookup::t_idv). Returns True when the relationship is valid (The
>>>value already exists). You could also create a calculated field in one of
>>>the tables, with the same calculation and check that value with an IF.
>>>Both
>>>solutions have the same result.
>>>
>>>You don't need any hagling with values in ValueLists, this would be
>>>cumbersome at best.
>>>
>>>Keep well, Ursus
>>
>> Ursus,
>>
>> I'm sure I missed something -- however the
>> "isvalid(titles::t_id ; titles_lookup::t_idv)"
>> syntax isn't correct for the IsValid function. Is there a self-join
>> or something implied here?
>>
>> thanks
>>
>> --
>
>Implied was a relationship between titles and titles_lookup. Since you use
>the suffix _lookup I thought you already had one defined here.
>
>Keep well, Ursus
The table titles_lookup is another table occurance of titles. But now
that I understand your suggestion, I believe I see a way to implement
it. Thanks for clearing it up for me.
--
FW