Modification Dates

[Lemur]

Hello,

I have - a series of join files for interrelated databases (using FM

5.5).

Here's the situation. One of the fields is an 'average' which averages

all the occurences that meet a certain criteria (specifically, the

person's name and the game name).

Consequently, all the occurences for that criteria have the same data

in that field.

For example, if we were playing Monopoly, and we were checking the % of

time Bob won the game, and he played 4 games and won one, then, each of

the 4 instances in the join file that show Bob PLAYING Monopoly, would

show Bob's win percentage as 25%.

OK - now, let's say Bob plays again, and loses. His new entry in the

join file shows his win percentage as 20%. And, in fact, all FIVE

entries show 20%.

However, the 'modification date' on those other entries does NOT change

to show this new calculation result...

Is there any way to track when a change, such as this, is made to a

record?

Thanks!

Chris

[mr_vodka]

Havent used FM5 in a really long time so I cant remember what all was and what wasnt in it anymore.

So if you have on your parent side a calculation for the average percentage, you can have a similar calculation for the Max (child::date)

On the child side, the field should be the creation date if the entry does not intend to be modified and you are just giong to enter additional entries for Win / Loss or if you do intend to modify old records and want to update the date then you can use the modify date.

[Lemur]

There's a lot more to it -- more than I think anyone would want to hear

Basically, I just need to be able to track calculation changes (as compared to DATA changes) in previous entries in the join file.

So, if I change something in the related file, and it updates a calculated field in the join file, I need to track that that update has happened.

Does that make sense? I know the modification date will update for the record I'm actually changing. I need to also update it for the other records that haven't had data change, but merely are reflecting the data changed in other records in their own calculation fields.

Chris

[mr_vodka]

Yes but you are asking for basically a date stamp on an unstored calculation. How can you put on a stamp on something that it doesnt know when the value changes? You would have to put the stamp on something that it will know the difference of when it changed verses not changed. So if you used a Max () function on a stored field of the modifcation date it will give you the latest update of when anything for Bob playing Monopoly is updated or a new entry for Bob & monopoly is created.

BTW again, I am going as far back in my memory as I can for FM5.5. I can only go back as far as FM6 as that is the last copy I have installed right now.

[Lemur]

Hi John,

OK - that does make sense, the way you put it there.

I'm still a little lost on what you're proposing as the fix, tho...can you spell it out a little more for me?

Thanks!

Chris

[mr_vodka]

Instead of me making a mock file and having to change it again if its not what you were looking for, do you think you can post a copy of the file?

[Lemur]

Sure thing...here it is.

Basically, I want to track whenever a calculation changes in "Gamer Rating" in Layout #2.

The problem is...if you create a new entry or edit a semi-related one that references an earlier entry, that calculation will change.

For example:

Go to Layout #1

Find #350 in Game_Id

Go to Layout #2

Look at Gamer Rating for record #1 (0.18)

Go to Layout #1

Go to record #5

Change the Score from 26 to 46

Go back to Layout #2

Gamer Rating is now 0.22 and the modification date is today's date

Now, go back to Record #1, and you'll see the Gamer Rating THERE is ALSO 0.22 (because it's an average of the self-relationship (which I need for other things).

But, there's no modification date listed -- I need to KNOW that that field changed...

Thanks!

Chris

PlayerPlayedJoin.zip

Edited October 31, 2006 by Guest

[mr_vodka]

I have a meeting that i have to attend. I will take a look at it when I get back from it.

[mr_vodka]

This is missing at least one file called GamesPlayed.fp5. Zip up all the files and reattach.

[Lemur]

Thanks John...

I've been going nuts today trying to figure out an easy fix -- seems like it should be fairly easy; but I guess I'm wrong

Chris

[mr_vodka]

You forgot to reattach.

[Lemur]

Hi John,

Actually, I didn't forget to reattach -- I just posted simultaneously

In any event - I can't reattach them all - the total is over the 1mb limit the site allows.

But, you don't actually need the other files (I don't think) - if you do the steps I mentioned, I'm 99% sure the information will all be in that one file I sent you.

It'll probably give you some error messages, because they are all relatable; but, essentially, the other files have no bearing on what I'm trying to accomplish here.

Chris

[Lemur]

Hi John,

I saw you had your email addy listed in your profile; so I did zip up all the files and sent them to you...

(be forewarned, in the playerplayedjoin file (the one I sent you earlier) the bottom two fields in creation order serve no purpose currently - I'm trying to figure this out, and those were my 'test' fields).

Chris

[mr_vodka]

That is exactly it, I cant do what you wanted me to do without those files especially the GamesPlayed.fp5 file. If you can make a copy and delete some of the unncessary records so its just a small basic model.

[mr_vodka]

Ok in your PlayerPlayJoined file, first change your field 'Modification' to a date type field. It is currently on number. Next you will create a new calculation field. That calculation field will be:

Max(GamePlayer::Modification)

To test it out, you can temporarily turn off the Auto-Enter of the modification date of the 'Modification' field and type in your own dates. It will always show the latest date that that player and game combo score was entered. The only draw back of this is that if anything else is changed on the record then it will update as well. It may be wise to seperate out the scores into its own seperate table if this is critical that the date only updates when the score and only the score changes.

[Lemur]

Yes! Yes! Yes!

That does work -- to test it, I set the clock on the computer ahead, and adjusted some sample records...worked great.

Can you explain how it works? I'm still a little lost on that - why does it know the modification date (even though it doesn't change) is changed for those fields that are calculation only?

Thanks again!

Chris

[mr_vodka]

It uses the same relationship as is used to do the average. In other words, for each game & player combo it looks for the maximum date of the matching records.

You are currently using a self join called 'GamePlayer' that is keyed on a concat of gameID&playername, i.e. 350Chris Palermo

It looks for all records that match that key. So when you have lets say 4 records of 350Chris Palermo that were modified on different dates of 7/4/06, 8/15/06, 9/19/06, & 10/10/06, the calculation looks for the highest date for all those 4 related records. If you end up creating a new record or modifying one of the old ones, the modification date will change for that record that you just changed or created. The calculation field will now change because there would be a new highest date for the 4 or 5 records and it would display today's date if changed today.

Hope that this clears it up a little for you.

[Lemur]

Ah!! Yes...

So, what's happening is NOT that it's noticing that, for example, that calculation field is changing -- it's saying, 'hey, since one of these records changed, we'll select ALL the records that have that same identifier (i.e., 350Chris).

OK - that's a good workaround - it's not checking for what I THOUGHT it needed to check; but it's accomplishing EXACTLY the same thing.

Very cool.....

Chris