Modification date

[tlyon]

Alpha Five stores the last date/time that a table was modified. Is there a function that will allow one to probe that stored value?

Tom Lyon

[Stan Mathews]

RE: Modification date

Tom,

I'm working on something similar for Bill. Involves sys_shell() to DOS, dir to a text file, import the text file, choose the date you want.

I'll post another reply if I can figure it out.

Stan

[tlyon]

RE: Modification date

I'd thought about that, but then figured it was too time/processor intensive for this particular application. I know that your method will work. I just think that there is something stored somewhere that ought to give us that data.

Tom Lyon

[whanigsberg]

RE: Modification date

Stan, Tom, Robert,

Funny, Tom and I posted the same question. Should have looked first.

After I posted I recalled that I had copied something off the old message board. Even wilder, I found it, dated the 19th of May 1998.

Stan, it's the same method you and Robert suggested. Since we know the file we are interested in we can shell out to dos and do a dir command that gets only the one file. Then pipe the command's output to a text file which we then open up and read using the xbasic file.read_line() method. Then we have to delete the file.

It's a bit roundabout but still I was going to do it, and, Tom, I don't think it would be that slow to execute. It would only be the disk access that was slow and there's not much of it.

But I decided against, because the file date is a processing artifact rather than data and using the one as the other may lead to problems when someone besides the developer works the application.

So after all that, I have decided to prompt the user for the effective date.

I am also going to write the dos routine because it's neat.

Tom, have you considered a calculated field with the expression date(). That would tell you when a record was last saved and the most record with the largest (latest) value would indicate when the table was last revised, wouldn't it?

Regards to all,
Bill

[tlyon]

RE: Modification date

I understand that the method you suggest will indeed give the desired information. Let me explain why I do not want to approach it like that:

My application is one of real-time interaction across workstations. The application is a Call Tracking system. The best way to update the individual workstations is to rerun/re-apply the query. Now, that's easy enough to do, but I would like the data to be current at all times. I can run that query continuously, say, like every 10 seconds, or more often, if need be. That's time/processor consuming. Or, alternatively, I was hoping to be able to look at the last time modified and run the query based on the need in light of the modification time.

Now that I think about what you said, Bill, your thoughts are well worth pursuing. I could create a dummy table to store the modification time. I could update that any time modification occured or records were added/deleted. Reading that would be painless, maybe even better than what i originally had in mind. Thanks! I'm quite certain that will solve my problem!

Tom Lyon

[tlyon]

RE: Modification date

Bill,

Your idea, which I have now implemented, works fantastically. Now, I am able to refresh instantaneously when needed, rather than perform needless refreshes.
I'll be instituting it on all of my apps. Very slick.

Tom Lyon

[whanigsberg]

RE: Modification date

Well Tom,

Seems like we're on the same wavelength, regarding both problems and solutions.

I wonder if you would be kind enough to give a bit more detail on how you set up the date check etc.

I find it easy to get into tunnel vision as to means which leads me to forget what I was trying to achieve in the first place. When I'm smart I force myself to put a problem aside for a day. I often come back to it in a new way.

Of course, most of the time I not that smart

All the best,
Bill

[csda1]

RE: Modification date

Tom,

A couple of issues with your methodology that you might want to explore for faster and more reliable operation.

1. You could use an index to return the desired records for that user. The index is much more efficient for dynamic operation. However, it will reorder the records dynamically too. This will happen also if you are constantly running a query. Nevertheless, the query should be set up to run so that LQO (Lightning Query Optimization) occurs with the defined index. This will increase your query operation and speed the network as well.

2. If you are posting a date/time of the last modification, you should instead post a date/time of the last modification (as it affects a specific user) for each user (into a database) and then check that for each user. This way, a change in date/time will be specific for a user rather than all users and minimize unneeded runs of the query.

3. A query will not rerun (unless forced in XBasic) unless it detects that the database (overall) has been modified/changed. It will use the last query table. Thus if you run it will not actually do anything in this case.

4. The date/time clocks of all systems may not be synchronized. If that is the case, the values posted by 1 user modifying a record may not correspond with the date/time of the user who needs to use that info. Thus you may be refreshing your query at the wrong times

5. It may be possible to set a flag in every record when created or modified, then use an index based upon that flag and the user name. If exist() returns the existence of at least one of those, then you could rerun the query, and then clear the flag (which unfortunately means modifying the record again!). If I were doing it this way, I probably would put the flag in a 1-1 linked child record to minimize changes to the key data.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[tlyon]

RE: Modification date

I'm not real sure you are doing exactly what I am here, Bill, but let's assume you are.

I created a separate table that has one record and a few fields which are flags to contain a character time value. Every time my table/form/etc. is edited, that flag is set to the time of the edit.

On the workstations, the OnTimer event of another form reads that flag upon startup, then re-reads it with each cycle of the OnTimer event. If it changes, then the form does its thing, which, in this case, is to refresh a browse on the form (that includes running a query, resynching, etc.). By using multiple flags, I can refresh various parts of various forms. I use several live, interactive forms that depend on data being fresh, even up to the nearest second. To flag the edits, I placed code in the OnChange event at each point in the form where an edit was possible. This was simple for me, because the form has few editable fields.

It's working beautifully.

Hope that helps explain how I am doing things here.

Tom Lyon

[tlyon]

RE: Modification date

"1. You could use an index to return the desired records for that user. The index is much more efficient for dynamic operation. However, it will reorder the records dynamically too. This will happen also if you are constantly running a query. Nevertheless, the query should be set up to run so that LQO (Lightning Query Optimization) occurs with the defined index. This will increase your query operation and speed the network as well."

I tried indexes earlier, but they were not nearly as reliable as queries on my workstations running NT. It could be a factor of network traffic as this application is interacting with the server heavily due to its nature.

"2. If you are posting a date/time of the last modification, you should instead post a date/time of the last modification (as it affects a specific user) for each user (into a database) and then check that for each user. This way, a change in date/time will be specific for a user rather than all users and minimize unneeded runs of the query."

Don't quite understand that one, but what happens now is that if one user makes a change to the table, all users see the change immediately. Their workstations only refresh once for that one change. Because it is my desire for each workstation to be synced at all times, having just one flag to deal with seemed to make sense.

"3. A query will not rerun (unless forced in XBasic) unless it detects that the database (overall) has been modified/changed. It will use the last query table. Thus if you run it will not actually do anything in this case. "

Right. I'm appreciative of the fact that the query does sense the need to refresh as needed. It seems to be very efficient in that respect.

"4. The date/time clocks of all systems may not be synchronized. If that is the case, the values posted by 1 user modifying a record may not correspond with the date/time of the user who needs to use that info. Thus you may be refreshing your query at the wrong times "

Heh, automachron is one of the first utilities I always install on a workstation. I'm a stickler for correct time on every machine :)

"5. It may be possible to set a flag in every record when created or modified, then use an index based upon that flag and the user name. If exist() returns the existence of at least one of those, then you could rerun the query, and then clear the flag (which unfortunately means modifying the record again!). If I were doing it this way, I probably would put the flag in a 1-1 linked child record to minimize changes to the key data. "

Not sure that applies in this situation, but I certainly do appreciate any and all comments.

Tom Lyon

[csda1]

RE: Modification date

Tom,

On the previously numbered items;

1. I believe the correct index that matches your queries for LQO would decrease network traffic. I don't have index reliability issues, and would say that you have a network problem if you do.

2. Let's say I just got a call from customer X for you. I update his record and indicate that you need to return his call. This would force everyone to update his callback list the way you are doing it. If, on the other hand, the query only refreshed based upon a modified (or new) record for you on your workstation and all the other workstations did not run the query refresh because it was not a change that is directed to them, you would then have cut down the number of refreshes on the network by magnitudes.

4. Heh, automachron is one of the first utilities I always install on a workstation. I'm a stickler for correct time on every machine :)

Did you take into account the distance to the workstations from the time server and correct for the speed-of-light propagation delays? And what if the workstation is in a different acceleration frame (such as on an airplane) and thus time passes at a different rate? What happens to the time if the workstation falls into a black hole (many cubicles fall into this category!)? Did you think about that? Well did ya? Did Ya? :?)

5. This methodology works better if you can't/don't have synchronized times.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[tlyon]

RE: Modification date

"Did you take into account the distance to the workstations from the time server and correct for the speed-of-light propagation delays? And what if the workstation is in a different acceleration frame (such as on an airplane) and thus time passes at a different rate? What happens to the time if the workstation falls into a black hole (many cubicles fall into this category!)? Did you think about that? Well did ya? Did Ya? :?) "

ROTFL!!!

I disabled reverse on all the workstations. What a pain. Some employees just can't stand moving forward. Also, chairs are automatically positioned in front of the monitor to ensure precise hand-eye coordination. No room for errors here. Nope. Not gonna do it, wouldn't be prudent.

Seriously, cat5 cables are twice as fast as cat3 cables. Betcha din't know that :) We actually had to slow the machines down!

With tongue firmly embedded in cheek,

Tom Lyon

[Stan Mathews]

RE: Modification date

I with draw my previous comment about your not evidencing your sense of humor. I guess I just wasn't paying attention.

[csda1]

RE: Modification date

Tom,

Also, chairs are automatically positioned in front of the monitor to ensure precise hand-eye coordination. No room for errors here.

Not a bad idea, and I think it would work for the more advanced users.

However, for the typical user, I think you'd be better served if you clamp (or nail) down their shoes, clamp their heads into position, and have one of those hats that feeds you liquid refreshment (Cola, Perrier, Coffee, tea whatever) to keep them Juiced up while working.

It also may help to feed 120 VAC (or 220 VAC in the UK) into their metal chairs every few minutes, just to keep users from nodding off (if you do this, be sure to use a UPS and surge protection on your workstation - Don't worry about a GFI (Ground Fault Interruptor) for the user -- They're replaceable, the computer's data is not!). If you do do this consult your company lawyer for liability issues -- you don't want to void any warranties on the equipment!



By the way (and this part is true), when my son was 18 months old he found the reset and BRS (Big Red Switch - an old acronoym for the early PC's power control -- he's never seen an actual BRS though) rather quickly. I had to disable both of them to keep losing data. Surprisingly, by 2 years he no longer did that, preferring to mouse and click and type instead. Now why is it that many users are more like my son at 18 months than at 2 years?

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[tlyon]

RE: Modification date

They might think we're kidding, Ira.

We're not.

Tom Lyon