There is a way around this problem.
Before the solution, first the problem.
Problem...
Whenever you have a computed column based on a variable such
as date, time, name, user, etc.,
ANYthing that does a mass update will reset the values if you
don't take steps to prevent it.
Things that will overwrite your computed (containing a
variable) column values include:
1. Making a change to the table structure such as adding a
column, deleting a column, changing a column, etc. After you're done, your
data is now set to the current date and time, etc. and you've lost your original
values.
2. The only way to restore the data is to first change the
column to a NON-computed column since you can't load data into a computed
column. But when you change the column back to a computed column you run
into the problem listed in #1 (changing the column to computed causes all values
to be overwritten)
3. I'm sure there are more, but I can't think of them right
now....
The Solution...
The solution is actually rather easy and works rather
well. So easy in fact that your biggest problem will probably be
remembering that you have to turn the feature ON and OFF.
I setup the following before the advent of datetime, but using
a datetime field would work the same way. The value I use for lineend to
compare to and set to tell RBase to leave the field alone is the checkmark
character (Ascii 251) although it looks like an underscore below. When I'm
through I set the lineend back to the default (Ascii 254).
R>list column aud_dat2
Column Name Table Name Attributes
------------------ ------------------ ---------------------------------------
AUD_DAT2 BILL_XTN Type : DATE
Index : SINGLE-COLUMN
Compute: (ifeq(cval('lineend'),'_',AUD_DAT2,((.#DATE))))
Column Name Table Name Attributes
------------------ ------------------ ---------------------------------------
AUD_DAT2 BILL_XTN Type : DATE
Index : SINGLE-COLUMN
Compute: (ifeq(cval('lineend'),'_',AUD_DAT2,((.#DATE))))
R>list column aud_tim2
Column Name Table Name Attributes
------------------ ------------------ ---------------------------------------
AUD_TIM2 BILL_XTN Type : TIME
Compute: (ifeq(cval('lineend'),'_',AUD_TIM2,((.#TIME))))
Column Name Table Name Attributes
------------------ ------------------ ---------------------------------------
AUD_TIM2 BILL_XTN Type : TIME
Compute: (ifeq(cval('lineend'),'_',AUD_TIM2,((.#TIME))))
R>list column aud_usr2
Column Name Table Name Attributes
------------------ ------------------ ---------------------------------------
AUD_USR2 BILL_XTN Type : TEXT 8
Compute: (ifeq(cval('lineend'),'_',AUD_USR2,((CVAL('NAME')))))
The way it works...
Column Name Table Name Attributes
------------------ ------------------ ---------------------------------------
AUD_USR2 BILL_XTN Type : TEXT 8
Compute: (ifeq(cval('lineend'),'_',AUD_USR2,((CVAL('NAME')))))
The way it works...
As long as the lineend character is NOT set to a checkmark (or
whatever character you use for comparison), the fields' value will always be
updated to the computed value (like a regular computed column).
When you want to change the table structure (such as after a
reload containing these type of computed columns and you want to change them
back to computed columns), just change the lineend character to the checkmark
(or your comparison value). As long as the lineend character is set to
this value, you can make ANY type of updates you want and preserve the original
values. This includes using the UPDATE command, structure changes,
reloads, batch update procedures, etc. Just remember to change the lineend
value back or all changes made by users won't update these fields
either!!!
Thank You,
-Bryan Andrews
----- Original Message -----
From: "randyp" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 13, 2002 11:47 PM
Subject: Re: DateTime Stamp on Update
> One problem with this procedure is that when you unload
and load
> a table you will lose all those calculated dates and times because they
> will be replaced by the date and time of the load.
>
> One approach we used was to have two separate columns, a date entered
> and a dated updated. We also recorded the personNumber of the user
> when the original entry or change was made. An insert command or a
> form variable put the current date in the column for date updated if the
> current date differs from the date of entry:
>
> dateUpdated = (IFEQ(DateEntered,.#DATE, NULL, .#Date))
>
> Randy Peterson
>
> Troy Sosamon wrote:
>
> > An easy way to do it is to put 2 computed columns on the table, one with a
> > date = .#date the other w/ time = .#time. This will update anytime the row is
> > changed.
> >
> > Troy
> >
> > >===== Original Message From [EMAIL PROTECTED] =====
> > >I need to place a DateTime stamp in a column when a row has been updated.
> > >
> > >Seems simple enough until you consider the following:
> > >
> > >Lets say the user accesses a Customer/Transaction form
> > >(with the transactions being defined as a region)
> > >
> > >Initially, the focus is on the transactions.
> > >If they click back to the top (Customer Side) and make a change
> > >how can I detect a change to that specific table and update the
> > >DateTime stamp?
> > >
> > >I've experimented with the following technics without success:
> > >
> > >- Triggers (too constrictive in forms with regions)
> > >- Variables in Form (get evaluated regardless of change to data)
> > >- Using an EEP on Entry/Exit to table <or> after Leaving Section
> > > (Form_Dirty_Flag detects changes made to ANY table)
> > >
> > >Recommendations Anyone?
> > >
> > >TIA
> > >
> > >- Rob Vincent
> >
> > Troy Sosamon
> > Denver Co
> > [EMAIL PROTECTED]
> >
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > ================================================
> > TO SEARCH ARCHIVES:
> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
> a table you will lose all those calculated dates and times because they
> will be replaced by the date and time of the load.
>
> One approach we used was to have two separate columns, a date entered
> and a dated updated. We also recorded the personNumber of the user
> when the original entry or change was made. An insert command or a
> form variable put the current date in the column for date updated if the
> current date differs from the date of entry:
>
> dateUpdated = (IFEQ(DateEntered,.#DATE, NULL, .#Date))
>
> Randy Peterson
>
> Troy Sosamon wrote:
>
> > An easy way to do it is to put 2 computed columns on the table, one with a
> > date = .#date the other w/ time = .#time. This will update anytime the row is
> > changed.
> >
> > Troy
> >
> > >===== Original Message From [EMAIL PROTECTED] =====
> > >I need to place a DateTime stamp in a column when a row has been updated.
> > >
> > >Seems simple enough until you consider the following:
> > >
> > >Lets say the user accesses a Customer/Transaction form
> > >(with the transactions being defined as a region)
> > >
> > >Initially, the focus is on the transactions.
> > >If they click back to the top (Customer Side) and make a change
> > >how can I detect a change to that specific table and update the
> > >DateTime stamp?
> > >
> > >I've experimented with the following technics without success:
> > >
> > >- Triggers (too constrictive in forms with regions)
> > >- Variables in Form (get evaluated regardless of change to data)
> > >- Using an EEP on Entry/Exit to table <or> after Leaving Section
> > > (Form_Dirty_Flag detects changes made to ANY table)
> > >
> > >Recommendations Anyone?
> > >
> > >TIA
> > >
> > >- Rob Vincent
> >
> > Troy Sosamon
> > Denver Co
> > [EMAIL PROTECTED]
> >
> > ================================================
> > TO SEE MESSAGE POSTING GUIDELINES:
> > Send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: INTRO rbase-l
> > ================================================
> > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> > In the message body, put just two words: UNSUBSCRIBE rbase-l
> > ================================================
> > TO SEARCH ARCHIVES:
> > http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
> ================================================
> TO SEE MESSAGE POSTING GUIDELINES:
> Send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: INTRO rbase-l
> ================================================
> TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
> In the message body, put just two words: UNSUBSCRIBE rbase-l
> ================================================
> TO SEARCH ARCHIVES:
> http://www.mail-archive.com/rbase-l%40sonetmail.com/
