View Full Version : Updating a Calculated Field on a Form


Ron Elliott
08-26-2002, 03:49 PM
I am trying to figure out a way to generate a Sort key when I am entering or changing a record. I will try to explain the situation:

I have a field where the user enters a Catalog Number. This value can contain both alphabetics and numerics or just numerics (e.g. CE4b, 341, or 1354i).

When the user tabs out of the Catalog Number what I want to do is to build a Sort Key behind the scenes so that the numeric portion is expanded to 10 digits with leading zeroes. Using the above examples, the result would be:

CE4b would become CE0000000004b
341 would become 0000000341
1354i would become 0000001354i

I would like to be able to display this calculated field on the screen.

Using this approach, embedded numerics would be kept in the proper sequence.

I tried using the following script that I built and added it to the Depart event for the Catal_No field. Everything seems to function properly except that I cannot get the calculated value to appear on the form in the Srt_Cat_No field nor to update the corresponding field in the database. I am missing something. The traceln statements show that the fields contain the proper values.
Here is my script:

Dim Char As c

Dim CatNoA As c
Dim CatNoB As c
Dim CatNoC As c

Dim CatNoAFlag As c
Dim CatNoBFlag As c
Dim CatNoCFlag As c

Dim WrkCatNo As c

Dim intIndex As n

CatNoAFlag = "N"
CatNoBFlag = "N"
CatNoCFlag = "N"

WrkCatNo = obj_get("Catal_no", "text")

traceln("Catal_No was "+WrkCatNo)

' Split out the Catal_No field into up to 3 components.
For intIndex = 1 To Len(WrkCatNo) Step 1
Char = Substr(WrkCatNo, intIndex, 1)
If IsAlpha(Char) Then
If CatNoAFlag = "N" Then
CatNoA = CatNoA + Char
Goto Next_Char
CatNoC = CatNoC + Char
Goto Next_Char
End If
CatNoAFlag = "Y"
CatNoBFlag = "Y"
CatNoB = CatNoB + Char
Goto Next_Char
End If

Next intIndex

' Pad the CatNoB field with leading zeroes.
CatNoB = Replicate("0", 10 - Len(Trim(CatNoB))) + Trim(CatNoB)

' Build the calculated Sort Key.
WrkCatNo = Trim(CatNoA) + Trim(CatNoB) + Trim(CatNoC)

traceln("Srt_Cat_No is "+ WrkCatNo)

obj_put("Srt_Cat_No", "text", WrkCatNo)

WrkCatNo = obj_get("Srt_Cat_No", "text")

traceln("Srt_Cat_No from the form is "+ WrkCatNo)


Any suggestions on how to update the field on the screen and the database?


09-18-2002, 12:20 PM
Make a global function out of your script.Then make a calculated field rule for Srt_Cat_No using the function.