I have a question about the SYS_ROWVER. I have looked at this construct in the past but have not really used; my impression is that was used only in conjunction with Oterro. I am now working with an external database SCONNECTed to my database and with 9 tables SATTACHed. Basically, an external application runs and generates budget prediction models and the data is stored in the attached database and I have an MDI form that monitors the attached database and when requested, generates very long and complex reports up to 800 pages long using data from the attached database. For speed considerations, I project temporary tables from the attached tables, add indices and create temporary views and I can typically create the longer reports in between 2-3 minutes where using the un-indexed foreign tables would easily take 20-30 times as long. Obviously, I have to monitor the foreign database to see if a new budget model has been created or an existing budget model modified so I can refresh my form to display the updated data. Currently, I use the SUM OF the budget model cost stored in a small summary table and it does the checking pretty quickly and if the value changed the temporary tables are updated accordingly. I am thinking that using the SYS_ROWVER might be a better option if available. I am not sure how to use this framework since I am not using Oterro and I cannot modify the structure of the attached tables. Any suggestions? On a separate note, I want to display the external xxx.exe application side bay side with the MDI form used to generate reports so it is somewhat transparent to the end user. I can use a PROPERTY command to locate the form exactly where I want it but there does not seem to be an option to specify a startup location for the external xxx.exe application which right now opens as a maximized window. I am working with the developer of that application so the external xxx.exe application does not open maximized; any thought on how to achieve this? What I would really like is to have a virtual windows inside the form where the external application runs, pretty much like the web browser object works. Javier, Javier Valencia, PE 913-915-3137 From: [email protected] <[email protected]> On Behalf Of [email protected] Sent: Saturday, October 2, 2021 9:26 AM To: RBASE-L <[email protected]> Subject: Re: [RBASE-L] - Automatic DateTime for each row inserted No. I was just suggesting the proven architecture of SYSROWVER as a framework for your task. On Friday, October 1, 2021 at 3:51:54 PM UTC-4 Doug Hamilton wrote: Thanks Mike. As I understand it, SYS_ROWVER is an integer that increments by one for each change to a row. I often create temp tables with a SYS_ROWVER column when editing using a form. if SYS_ROWVER is > 0, then I can ask user if they want to save their changes or not.
I'm not sure how to get SYS_ROWVER to increment by DateTime; and it would update DateTime any time the row is changed. I want to keep the initial DateTime the row was added. Doug On 9/30/2021 6:41 AM, [email protected] <mailto:[email protected]> wrote: Remember Oterro databases use AUTOROWVER and the SYS_ROWVER column https://www.rbase.com/support/rsyntax/rbgx/operating_condition.htm Why wouldn't the system used work for you by using the datetime instead of an Integer? On Wednesday, September 29, 2021 at 11:12:51 PM UTC-4 Doug Hamilton wrote: Javier - For the reasons you cited, I'm going to use an After Insert trigger with your suggestion of WHERE TrackingDateTimeAdded IS NULL.. That appears to be the most straight-forward and foolproof. I haven't worked with COUNT= LAST or COUNT = INSERT enough to be comfortable with them. Default values and computed columns may work, but all it would take is some errant user (me) to overlook the NOCALC setting and clobber all the data. Thank you for your suggestion. Doug On 9/29/2021 11:30 AM, [email protected] <mailto:[email protected]> wrote: Karen, I have only used the NOCALC switch to unload and reload data without any of the computed columns recalculating. By design, the computed columns kicks in any time one of the components of the computations changes, for example, if I have a columns “FullName” which is defined as “FirstName& LastName” then, whenever one of the two components changes, the computed column also changes, as expected. When you unload and load data using the NOCALC switch, the update of the computed column is temporarily disabled ONLY during the unload and load operation. However, If you edit one of the components before or after, the computed column would update as expected. For this reason, I would never use the variable #NOW as part of a computed columns because I am not sure how often the computed column with the #NOW component would update. Using UPDATE in the INSERT trigger would update the columns only once, when the trigger kicks in, and the value would be retained afterwards unless specifically and manually or programmatically changed. It was suggested to use #NOW as the default value for the column at insert time when no value is entered for the column in question. This look like a nice solution; however, I have not used this approach myself before so I cannot attest how and if it works. In theory, sounds like it should work and it is something I will definitely try in the future. Hopefully this explains my comment based on my understanding on how the NOCALC switch work and my own experience. Javier, Javier Valencia, PE 913-915-3137 <tel:(913)%20915-3137> From: 'Karen Tellef' via RBASE-L <[email protected] <mailto:[email protected]> > Sent: Wednesday, September 29, 2021 8:12 AM To: [email protected] <mailto:[email protected]> Subject: Re: [RBASE-L] - Automatic DateTime for each row inserted Javier: Just confirm for me cuz I haven't played around with NOCALC yet. That prevents a recalculation of computed columns if you do an unload, but the computed column will still change if you make any change to the row, correct? If so, then a computed date or datetime column is good only as a "date last edited" but not to indicate when a record was inserted or another type of change. In that case, like you demonstrate, I either update a regular column in code or by using triggers. My triggers look at the before and after value, and then update a date column if a change was made, or populate a change logging table. Also to be considered is work at the R> prompt and whether you would want a computed column to be changed if, for example, a consultant is fiddling around with data on the backend and wouldn't want to have a user-generated computed column changing. Karen -----Original Message----- From: [email protected] <mailto:[email protected]> To: [email protected] <mailto:[email protected]> Sent: Tue, Sep 28, 2021 5:56 pm Subject: RE: [RBASE-L] - Automatic DateTime for each row inserted Doug, I have used the NOCALC switch before and works as advertised. Having said that, I would be hesitant to use a computed field that uses the #NOW variable since it is dynamic and I would rather have that columns locked on one date and not take a chance of it updating when not wanted. Have you consider using the After Insert trigger with something like this: UPDATE ShipmentTracking + SET TrackingDateTimeAdded = .#NOW + WHERE TrackingDateTimeAdded IS NULL It would automatically update anything that does not have data which most of the time would be only the record just inserted. Jests a though and I have not looked into any undesirable side effects. Javier, Javier Valencia, PE 913-915-3137 <tel:(913)%20915-3137> -----Original Message----- From: [email protected] <mailto:[email protected]> <[email protected] <mailto:[email protected]> > On Behalf Of Doug Hamilton Sent: Tuesday, September 28, 2021 10:43 AM To: R:Base List <[email protected] <mailto:[email protected]> > Subject: [RBASE-L] - Automatic DateTime for each row inserted I have a 7 column table, one column is named TrackingDateTimeAdded, type DateTime. What's the best way to update TrackingDateTimeAdded when a row is inserted into the table? The DateTime needs to persist through an UNLOAD and INPUT. Some of these rows will be inserted by a third party via SATTACH, so it needs to be automated via a trigger or computed column. I tried an After Insert trigger with the Stored Procedure: UPDATE ShipmentTracking + SET TrackingDateTimeAdded = .#NOW + WHERE COUNT=INSERT This updated the DateTime of the previously inserted row. In a multi-user environment, COUNT = LAST may not update the row inserted by the user. The results using a trigger were too unpredictable to be used in production. A computed column using (IFNULL(TrackingDateTimeAdded,.#NOW,TrackingDateTimeAdded)) works until the database is UNOADED and INPUTted - the column then gets set to the DateTime of the input. TIA Doug RB X.5E 20812 -- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/0add1034-f377-c9e3-2183-d0027a6e6859%40wi.rr.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAEH4%2BR0fIvFMtLO8fsISh/8BAAAAAA%3D%3D%40vtgonline.com. -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/1612567935.142843.1632921106073%40mail.yahoo.com <https://groups.google.com/d/msgid/rbase-l/1612567935.142843.1632921106073%40mail.yahoo.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAIiY8xumEHFHtyWHdwg1kCYBAAAAAA%3D%3D%40vtgonline.com <https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAIiY8xumEHFHtyWHdwg1kCYBAAAAAA%3D%3D%40vtgonline.com?utm_medium=email&utm_source=footer> . _____ <https://www.avast.com/antivirus> This email has been checked for viruses by Avast antivirus software. www.avast.com <https://www.avast.com/antivirus> -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/16ada5e3-f132-4cb9-b3d4-55013adf8129n%40googlegroups.com <https://groups.google.com/d/msgid/rbase-l/16ada5e3-f132-4cb9-b3d4-55013adf8129n%40googlegroups.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/2b8fda5b-c6b7-4402-be2e-83df012f40bdn%40googlegroups.com <https://groups.google.com/d/msgid/rbase-l/2b8fda5b-c6b7-4402-be2e-83df012f40bdn%40googlegroups.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAA38iDAM%2BFBKstDTMIoor%2BQBAAAAAA%3D%3D%40vtgonline.com.
