Calculation Fields and Last Modification Date

[oakbridge]

I'm working on a new solution where I plan on hiding the status bar and creating a navigation system within the layout itself. The main reason for this will be to allow records to be viewed in either a view only layout, or a modification layout depending on whether the 'record_locked' field is set or not.

I've got the navigation worked out, but have come up with one problem. I've created two fields in the table, 'table_record_numb' (unstored calculation field with the value Get ( RecordNumber )) and 'table_found_count' (unstored calculation field with the value Get ( FoundCount )) which I'm using for displaying in a 'Record x of x' format on the layout. I also have a field for 'table_last_modified' which uses Modification Timestamp as it's default value.

The problem is that as someone navigates through the records, 'table_record_numb' is getting updated each time, which means that even by just viewing, 'table_last_modified' gets updated. This is defeating the purpose.

Is there some way around this?

Please let me know.

Thanks,

[Vaughan]

Just make an unstored calculation field:

"Record " & Get( RecordNumber ) & " of " & Get( FoundCount )

The "table last modified" bit worries me somewhat. The way you describe you currently have it, it will only show the modification of the current record.

You'd need to create an unstored calculation field with the function Max( ModifiedTimeStamp ) but I can tell you now, this is going to become *really* slow when the record count increases. Each time a user changes record it'll have to re-calculate and it might take a second or two (or more) to update. This does not look good.

I'd leave the "table last modified" information out completely because users probably don't know what a table is, and don;t really care when a record in it was last modified. They *might* care to see when the *current* record was modified, and who did it, but I probably wouldn't put this on prime screen real-estate.

[bcooney]

I'm sorry to hijack this thread, but I do have a question that is somewhat on topic. I never use Get(RecordNumber) because that's FM's internal record number and somehow I don't trust it (for no logical reason). The record number that I display is the primary key for the table; an auto-enter serial.

What's the advantage to Get(RecordNumber), if any?

Also, another thread has suggested that you should never show the user the key field at all. If they want an InvoiceID, get them one, but it's not the key. Any comments?

[LaRetta]

Nah, Get ( RecordNumber) isn't FM's internal number. Get (RecordID) is.

Get ( RecordNumber ) is the, umm, record number of the found set (or record set) which the cursor is presently on. You should NOT trust FM's RecordID!!

Anyway, it's okay to display the unique (developer-generated) ID, just never let it be changed (of course). Usually, our unique IDs are meaningless and long so why display them? They mean nothing to the User. Sometimes a business will want to re-start their Invoice numbers or something. So I don't use them (any more) to hold my relationships together.

Edited August 30, 2007 by Guest
Added sentence

[bcooney]

Gosh, can't even get my Get()s right! Thanks, LaRetta.

[LaRetta]

Invoice numbers are communicated over and over between staff, companies and accounting departments. It is always in plain sight and gets printed and sent to the customer so yes, it is always visible. But no, I no longer tie my relationships together by it. Much depends upon how many customers, widgets or invoices you are talking about.

I am STILL paying a price for using Invoice numbers as my keys and then later, after GREAT pressure from management, having to re-establish new keys through a solution because they didn't want the numbers any more (they were too long). As the Developer, it is my job to stomp and scream and insist they are wrong ... and it is also my job (at some point) to give up, give them a memo stating the problems with it, and then just GIVING IN. It is not pretty; particularly when accounting departments pay invoices based upon that number. Referencing back to older invoice numbers suddenly became a nightmare. I ended up having my new TRUE ID, having their new Invoice number AND ... and get this ... AND having a multikey with text prefix which ended up linking everything together for reference and lookup.

Never again. Never, ever again. My unique keys are now my private property and a business will not even know they exist. If they want shortcut keys (for customers) or invoice numbers or product numbers, that is fine. But they will never bind MY solutions again.

When calling out a product, it is clearer to say 12 oz. Ultra-stuff than to say P102A. The number can be mis-typed, mis-spoken, mis-interpreted, accidently read from the wrong line etc and it would never be caught. Most users would rather type ULTR ... and have the product jump onto the screen than having to look up the code for it. Just my experience with product codes, invoices and customers. But I'm still a work in progress and I change my mind as often as I change my underwear.

Ooops ... I could have sworn there was quite a bit more to your post. Now I feel like I have split personality ... talking away to myself. Ha h! Tis okay, you can change your mind also!!

LaRetta

Edited August 30, 2007 by Guest

[bcooney]

Sorry, LaRetta, I reread your post and realized that you did state that you show key fields, so I edited my post, but you were already writing your reply.

Effort not wasted, though. I think I'll rethink my approach to displaying key fields (I like your idea of the "real" keys not ever being seen. I've also been debating using a SyncDekID as the key, and having my solutions "sync" ready. Additionally, a thread re-emphasized that key fields should be unique and meaningless. But, I do see meaning in a key as far as determining creation order. So, I'm breaking the rules! Therefore, I think what I need in each table is:

1. __kP_InvID (syncID)

2. CreatedBy

3. ModifiedBy

4. Created (TS)

5. Modified (TS)

6. ID (auto-enter serial, not modifiable) how to name it? It's not used as a key. Nice to have in a dev table view.

7. User's visible ID (for example, INV1000).

Think I covered all bases?

When you describe that users type ULTR and the product "jumps" on the screen, what do you mean. Are you matching on name and pulling in the ID? Or are you refering to a Find?

Edited August 30, 2007 by Guest

[LaRetta]

"I like your idea of the "real" keys not ever being seen."

It's like this ... we keep telling newbies that the unique IDs should NEVER EVER have meaning. But then we turn right around and use ProductIDs, Invoice Numbers etc as the keys which bind our solutions together!!

Then what happens? The business owner decides to change his product IDs. Or the Board of Directors decides to begin their invoice numbers again! We are missing our OWN point. Meaningless should be meaningless and anonymous so I am finally taking my own advice.

As for User typing part of product name or customer name, well, there are many roads to Rome and many roads to roam. Sometimes it's clairvoyance, or exploded permutation, filtered portal, popup menu or find. User selection is based upon number of records, specific layout and no one method is better than the rest. Each situation is different. The specific example I started to explain with products is based upon exploded permutation (by Mike Ender) which I like for product name selection (in many cases).

UPDATE: Your Item #6 above? A name for the field? serial

LaRetta

[bcooney]

Yeah, yeah, have to agree. OK, no more visible keys! A weight has been lifted, a flock of doves has been released.

serial it is.

[LaRetta]

Hey, Barbara, keep in mind I'm just one person. There might be a thousand Developers on here who would disagree totally with this stance of mine. But I've had to wallow in this kind of doo before and I didn't like it at all. So I won't step in it again. :wink2:

[bcooney]

LaRetta, we fm gals have to stick together, don't you think? Besides, you have to trust the opinion of someone who uses their real name.

Seriously, I don't think that the scenarios that you've described are uncommon, and I've experienced situations that come close.

Love to hear from the gallery, if there's a drawback I'm not seeing.

[oakbridge]

Just make an unstored calculation field:

"Record " & Get( RecordNumber ) & " of " & Get( FoundCount )

The "table last modified" bit worries me somewhat. The way you describe you currently have it, it will only show the modification of the current record.

You'd need to create an unstored calculation field with the function Max( ModifiedTimeStamp ) but I can tell you now, this is going to become *really* slow when the record count increases. Each time a user changes record it'll have to re-calculate and it might take a second or two (or more) to update. This does not look good.

I'd leave the "table last modified" information out completely because users probably don't know what a table is, and don;t really care when a record in it was last modified. They *might* care to see when the *current* record was modified, and who did it, but I probably wouldn't put this on prime screen real-estate.

Sorry that wasn't table_last_modified' but 'record_last_modified'. This is causing the problem because whenever the unstored calculations are 'recalculated' this field gets updated.

[comment]

A field with auto-entered modification timestamp will NOT update when an unstored calculation field recalculates, so look for something else that is causing your problem.