track changes log "Audit Logging"

[cobrak69]

OK, I did some looking around, but ended up more confused than when I started. I have a database with close to 100 fields and I use a lot of daily import features to keep the info up to date. The problem is, from time to time new information is incorrect and I need to review previous phone numbers, or contact names, but can't because they were over written. I have about 20 layouts, 100 fields and about 6000 records.

I am trying to figure out the simplest solution to achieve a change log. I was thinking of adding a log tab and having something along the lines of Nightwing's UltraLog in that tab for each record.

Problems are, I haven't got a clue how to implement Ultra Log in my CRM, and worse, I am borderline retarded when it comes to scripts, triggers, and other FileMaker logic. I built the whole CRM using a contacts template and use multiple layouts instead of portals, cause I lack the knowledge of how to do all that.

I bought a book, but I need an answer to this problem faster than I can read this book. PLEASE HELP.

[cobrak69]

I have built a CRM in FileMaker which is a miracle in itself seeing as how I don't understand scripts, portals, relationships or anything else. I simply modified a contact template and added about 20 layouts and 100 fields. I did manage to figure out how to update records via importing by an automatic script from my desktop and it updates existing records and ads new records (thanks to this forum). I have about 6000 records in this program, and it is working pretty good, but it has one major flaw.

When I import say 50 customers from my excel file, it will overwrite all my current info on that customer in certain fields (phone, name, etc.) based on matching Customer ID. 99% of the time this is a good thing, but every once in a while it will put bad info on top of good info, and the good stuff is gone forever.

I would like to create a tab that would track all changes to the record regardless of which field out of 100 they occurred in. If I could revert any given field that would be a bonus, but at least access to original info would be WAY better than what I got now. I have seen NightWing's ULTRALOG and it looks like that would be about what i need, but I have no idea how to implement it in my CRM. I tried emailing him, but got no response, its been 30 days.

Sorry for writing the book, and sorry for my tall order, but any help would be much appreciated. I love FileMaker, but have much to learn.

[comment]

I think that the best way to keep existing data is to keep it - rather than duplicate it elsewhere, then overwrite it.

IOW, keep your data in a related table (child of Contacts), and import into this table by adding new records, instead of updating matching ones.

[Lee Smith]

Hi cobrak, and welcome to the Forum,

Let me start by saying that, although FileMaker has a reputation for being easy to use and learn, and it is for simple things such as a rolodex file, this reputations is really a Marketing Ploy by FileMaker. Implementing the more advance features really depends on your working knowledge of FileMaker and its tools. The more you want to provide for, is directly tied to your understanding and knowledge of FileMaker and database design.

FileMaker has grown over the years, and continues to add new features with the release of each version that are meant to help us add needed functions to our solutions. A lot of what you see under the hood today is a result of the user's asking for them and then waiting an eon for them to be added. Lots of the features you see today, are replacing workarounds we had to do in earlier versions in order to provide a nice interface, etc to our end users. The list is too great to list them all, however, text functions, custom functions, conditional formatting, script triggers come to mind, as the text functions were my first request to FileMaker back in the mid 1990s.

The continue improvements with FileMaker require that we know how-to use them, and you can not do it without the knowledge of how FileMaker can be used. IMO, FMP Advance 11 is the best FileMaker version so far. Although I always want for more, I like what we have today when I think back to the days when I was using the older versions such as v3, 4, 5, 6, although I still use 6 today for some things.

My point being: The more features and functions you want to include in your solutions, is dependent on your knowledge and experience in FileMaker, and database structure and design in general.

I have the feeling that you might be a little over your head with this project, or getting frustrated and missing something obvious.

Is this an external deadline, or self imposed? If self imposed, I would suggest that you learn more about FileMaker first. This probably means buying a good book or two on FileMaker. The books are out there, and I can recommend a couple of them, as well as others that provide help here.

Just do a search on any of the online book stores or ebay and you will see what is available. I searched for "FileMaker", and I noticed that Ray's book is selling for $24 Used. Ray Cologon, authored "Ultra Log", and the title of his book is "FileMaker Pro 10 Bible [Paperback]".

You could also try attaching a copy of your files to a Reply here, so that we can see what's happening or what you might be missing.

If you don't have the time for learning, then you can also try to hire someone to do this for you, either by contacting a developer in your area, or posting a request for help in the Services Wanted. If you do this, be sure to give a method of contact such as Private Mail, Email address, or site information.

My guess is that you want to do this project on your own, so the investment in a good book or two would be the way I would go.

HTH

Lee

[cobrak69]

I think that the best way to keep existing data is to keep it - rather than duplicate it elsewhere, then overwrite it.

IOW, keep your data in a related table (child of Contacts), and import into this table by adding new records, instead of updating matching ones.

What is IOW? And to sound a step dumber, how would I go about establishing a child of contacts? The two problems I keep facing with my Database is the duplicate information (thus the reason for creating this solution) and bad information, usually from trying to merge records. Both are evil, and it seems one leads to the other, which is where the log idea came in. Please pardon my ignorance.

[cobrak69]

Hi cobrak, and welcome to the Forum,

Let me start by saying that, although FileMaker has a reputation for being easy to use and learn, and it is for simple things such as a rolodex file, this reputations is really a Marketing Ploy by FileMaker. Implementing the more advance features really depends on your working knowledge of FileMaker and its tools. The more you want to provide for, is directly tied to your understanding and knowledge of FileMaker and database design.

FileMaker has grown over the years, and continues to add new features with the release of each version that are meant to help us add needed functions to our solutions. A lot of what you see under the hood today is a result of the user's asking for them and then waiting an eon for them to be added. Lots of the features you see today, are replacing workarounds we had to do in earlier versions in order to provide a nice interface, etc to our end users. The list is too great to list them all, however, text functions, custom functions, conditional formatting, script triggers come to mind, as the text functions were my first request to FileMaker back in the mid 1990s.

The continue improvements with FileMaker require that we know how-to use them, and you can not do it without the knowledge of how FileMaker can be used. IMO, FMP Advance 11 is the best FileMaker version so far. Although I always want for more, I like what we have today when I think back to the days when I was using the older versions such as v3, 4, 5, 6, although I still use 6 today for some things.

My point being: The more features and functions you want to include in your solutions, is dependent on your knowledge and experience in FileMaker, and database structure and design in general.

I have the feeling that you might be a little over your head with this project, or getting frustrated and missing something obvious.

Is this an external deadline, or self imposed? If self imposed, I would suggest that you learn more about FileMaker first. This probably means buying a good book or two on FileMaker. The books are out there, and I can recommend a couple of them, as well as others that provide help here.

Just do a search on any of the online book stores or ebay and you will see what is available. I searched for "FileMaker", and I noticed that Ray's book is selling for $24 Used. Ray Cologon, authored "Ultra Log", and the title of his book is "FileMaker Pro 10 Bible [Paperback]".

You could also try attaching a copy of your files to a Reply here, so that we can see what's happening or what you might be missing.

If you don't have the time for learning, then you can also try to hire someone to do this for you, either by contacting a developer in your area, or posting a request for help in the Services Wanted. If you do this, be sure to give a method of contact such as Private Mail, Email address, or site information.

My guess is that you want to do this project on your own, so the investment in a good book or two would be the way I would go.

HTH

Lee

Yes I would love nothing more than to do this whole thing on my own, the problem is I am kind of on a deadline to make a working solution so I can present it at a sales conference I need to attend in a few weeks. I have some books; FileMaker Training Series (Master the essentials of FM 11 w/DVD) and FileMaker Pro 11 (The missing manual). I have read less than 60 pages between the two books, I am not much of a reader and I have a hard time following along. But I do have well over 100 hours using FM and experimenting, most of my major achievements have been from the help of the forums, or other help sites. But I do stumble upon some answers on my own along the way. I am a very hands on person and do best with OJT, rather than books. My problem is, I don't' have access to someone who can train me. There are no FileMaker people in my area that I know of. I would like to think maybe one day, I maybe that go to guy in these parts. (Maybe not) I used Filemaker since version 6, but never really LEARNED the program outside of creating fields and layouts. Calculations, scripts, field relationships all make my head hurt. I hope I am not hopeless.

[comment]

What is IOW?

"In other words"

how would I go about establishing a child of contacts?

Define a new table, say ContactHistory, with the fields that change + ContactID. Define a relationship between Contacts and ContactHistory, matching on ContactID.

To view the history of changes, place a portal to ContactHistory on the layout of Contacts.

[comment]

P.S. About the level of help you can expect from forums: we can provide a bottle of milk, but we can't teach you how to suckle. Some things are more complicated than others, and if they seem too complicated to you, then perhaps they are. The thing to do is to put them aside and come back to them later, when you are more familiar with less complex stuff. We have all walked that path - I don't believe there is a shortcut for anyone.

[bruceR]

Yes I would love nothing more than to do this whole thing on my own, the problem is I am kind of on a deadline to make a working solution so I can present it at a sales conference I need to attend in a few weeks.

Given all the other things you're saying, this doesn't seem like a realistic expectation.

Have you placed an ad looking for help? Your profile says you're in Charleston, SC, there must be some FileMaker developers nearby, have you done a Google search or placed an ad?

My quick Google search led me to this: http://fmforums.com/forum/topic/74620-fm-developer-charleston-sc/

[cobrak69]

Given all the other things you're saying, this doesn't seem like a realistic expectation.

Have you placed an ad looking for help? Your profile says you're in Charleston, SC, there must be some FileMaker developers nearby, have you done a Google search or placed an ad?

My quick Google search led me to this: http://fmforums.com/forum/topic/74620-fm-developer-charleston-sc/

I was hoping for something a little more optimistic. "YOU CAN DO IT" or something. Correct me if I am wrong, but isn't ULTRA LOG already doing what I need to do? Isn't there simply away to merge it with my Database?

[Vaughan]

I was hoping for something a little more optimistic. "YOU CAN DO IT" or something.

Sure you can do it. Just not in a couple of weeks.

You actually have 3 jobs ahead of you:

1) learn how to use FileMaker Pro;

2) learn how to build databases;

3) build the database.

How much time do you reckon each of these jobs will take?

You can probably learn FileMaker as you go along, but job 2 is the bit that takes time.

(There is also a job 4 which is "learn how to be a developer" which is things like project management, implementation and version control, but for an in-house developer 3 is probably enough.)

I'm currently working with a client that has a huge database built by an enthusiastic employee over a long period of time (something like 4 years). They reached the point where they realised it just wasn't working any more and didn't know how to fix it. I'll be here at least 6 months full-time re-writing it from scratch. That's after they had somebody very good map their business processes before I walked in, which was a huge job and took a couple of months.

Sorry there is no unrestrained optimism for you, but the reality is that some tasks like building databases that work are quite hard. The good news is that if you really want to get good at this you can, but you'll need to give yourself some time.