solutions wanted Data Change Management

[Carri]

Need a solution to track and manage record changes. We developed a solution for an organisation that books and manages all the logistics for tour groups coming to Israel. The system has many end users all wording with the same data. If one person changes something other users have no record of the previous info. We need an idea on how to manage these changes.

[David McQueen]

...have some confusion here.

It sounds from your post that this is set up as a series of single user databases and the data gets out of synch. If this is the case - put it on a network.Then changes are updated to all in real time.

If you have users in several different locations, consider a good WAN or a web segment to the solution.

The other possibility that I see from the post is that you are really looking for a change log to know who did what. There are several plug ins out there that will capture where and what is happening to the database and allow you to record those changes to a table.

Maybe add to your original post a bit and it will help people help you.

regards

Dave McQueen

[comment]

If you'll search the forums for "audit log" - you'll find several approaches, e.g.:

http://www.worldsync.com/fmDataGuard/index.html

http://www.nightwing.com.au/FileMaker/demos8/demo809.html

http://fmforums.com/forum/showtopic.php?tid/153729/

[Carri]

Thanks for your reply.

To clarify the DB is on a network with everyone working off the same data.

Here's the problem in real terms.

I have a program coordinator (PC) in the office building tour schedules, dates, times activities etc.

Then I have bookers ( in the office, one for transport, different B one for tours etc.

Originally the PC books a specific tour for 15:00 the booker sees that and goes ahead and books that request.

Now if the PC decides it needs to be at 15:30 and chnages that time, the booker has no record of the old time as the record he sees is the new one.

WIthout the use of a log I'm looking for a method of tracking those changes and keeping the old record for refrence purposes.

Any suggestions?

[David McQueen]

This sounds like a problem with business rules and procedures as much as anything else.

A couple of things that could be done depending on how the "real world" works there:

a.) If anything is booked by a booker - bar changing the tour time - this could be a no go in real world terms

b.) If anything is booked by a booker and the time is being changed by the PC, set an original time or last time field that will show and have all applicable people ie bookers that have entered times, emailed from the program to notify them of changes.

c.) If anything is booked by a booker, have changing the time also create a change record in another table and show a history of changes in a booking portal for each booker.

I think that emailing the bookers that have placed information in the program depending on the original data from PC would probably be a good business idea.

There are also plug ins that do alarms. You could look at a booker getting an alarm if he/she has entered data into that record and the tour time changes.

They are not log suggestions, but hopefully of some help in getting a grasp on the problem.

HTH

Dave McQueen

[Carri]

Thanks soo soo much for your ideas, you've definately given me some things to think about.

Question:

How do you commit a record to another table

[David McQueen]

Thanks soo soo much for your ideas, you've definately given me some things to think about.

Question:

How do you commit a record to another table

Here is one way of setting up a change capture in another table.

Lets say that all you want to do is capture the tour time and date prior to the change.

1. It is assumed that in the "Tour" table (what you have now) there is what is referred to as a Prime Key - an indexed unique identifier for that record. Usually it is a serial number or a text displayed as serial number.

2. Create a new table, call it capture. Make it have fields:

a. Time - for time of tour

b. Date - for date of tour

c. Date stamp - an auto entered field that puts the current time and date in the form of a date stamp when a record is created

d. Tour Key - will be used to relate to the Tour table. This must be of the same form ( text or number) as the Prime Key and it must be indexed.

Have a layout in the table Capture with those fields on it.

3. Create a relationship between the Tour and Capture tables

Prime Key <-> Tour Key

4. Go back into the field definitions for Capture and make the fields Time and Date auto enter values based on the relationship between Prime Key and Tour Key from the Time and Date values in Tour. You want the current value for the related record in Tour to be sucked in as soon as a relationship is formed. Then you do not have to worry about entering the values in some other way.

5. Back in the Tour table, you set up a "change time an date" layout that PC has to go to to make this change. Have this layout have two Global fields into which he will enter the new time and date. Then have a button for him to click - "Change Record" or something like that.

6. When he clicks the button you want the following to happen:

a. the screen freezes ( You really don't want anyone to see what happens)

b. you go to a layout on which Prime Key (Table Tour) is located for that record in Tour and that value is copied

c. you go to the layout where the fields in the table Capture are and:

-create a new record

-Paste Prime Key into the field Tour Key

-Use the script step Commit Record

d. go back to the original layout in Tour and:

- use set field steps to set the new time and date from the values that have been entered into the global fields

- commit record step

What should have happend was:

a. A record is created in Capture

b. The current time and date in Tour for that record is sucked into the new record in Capture via the relationship as soon as Tour Key is filled and the record is committed in the table Capture

c. The new time and date are then set in the table tour.

You can then display this in a portal sorted by the Date Stamp with most recent change to the top in the screens the bookers use and it will show the last known tour date and time at the top and previous ones below.

There are other ways of doing this, probably more elegant, but that is one way to capture a record change and then display it to the bookers.

HTH

Dave McQueen