Create a database with viewable history.

[shaneferguson]

[color:blue]

Hello all. :-) I'm new to filemaker - and the forums, this is my 1st post so please forgive me if it's in the wrong place.

I have been tasked to create a membership database for the organisation i work for.

I have created tables/forms and can now enter information.

However, i need to be able to view 'history' data for each member, so that if details (address, place of work etc) change - the users of the database can still view the members 'past'.

Could any of you please help a new user out by guiding me in the right direction to be able to do this.

I'm using Filemaker Pro 7, on WinXP Pro.

I can send the database file (200KB) if need be/if i'm allowed.

Many thanks, and kindest regards.

Shane :-)

[SlimJim]

The details that you need to keep as history will need their own tables. An address table, a workplace table etc. and when you want to view the history use an appropriate portal.

[shaneferguson]

Hi,

Many thanks for the reply.

How do i add a portal? When i go it add one to an existing form, and try to show data from an existing table - nothing happens.

I have attached my database (20KB).

As i mentioned, i'm a newbie at Filemaker.

Kindest Regards,

Shane.

unitydatabase.zip

[Inky Phil]

Hello Shane

I have had a quick look at your file and I think we need to take a few steps back in order to simplify things a lot for you so that you can move forward more quickly.

I will come up with a sample file this afternoon based loosely around yours and you tell me if it is going in the right direction. If so I will go through it step by step with you and then you should be away

Regards

Phil

[SlimJim]

I have had a brief look at your database - sorry I can't spend more time today - my first reaction is that you have a significant amount of data duplication between your tables. The five fields which you use for joining should only occur in one table. Add an ID to the membership table (I like a serial number but anything with unique values will do) and then remove the five fields from each of the other tables and replace them with a membershipID and use that for joining. You should consider splitting the membership table into two, say Member and MemberDetails. Putting in one half the five fields and any other unchangeable information about the member (date of birth sex(?) NI number possibly) and all the possibly changeable information in the other half, add a memberID and join it to the member table. This will provide you with your history table.

Currently portals into Lodge.., Factory etc will only show records when all five fields match. I rather suspect that some of the five fields may be empty and that makes them unsuitable for matching. If you replace the joins by ID numbers then you should have no problem. One further point and then I am out of time - You have set up your relationships in a heirarchy

Membership, Lodge, Factory, Benefit

Does this match the heirarchy that you have? For example a member will only have a benefit if they are in a Member-Lodge-Factory-Benefit chain of related records.

[shaneferguson]

Hi Phil,

Many thanks for taking the time to look at my problem.

Any help would be vastly appreicated, so a sample file and possiby a walkthrough would be really great.

Kindest regards,

Shane

[shaneferguson]

Many thanks for taking the time to look at my problem.

I shall print out the suggestions in your post/reply, and have a look this afternoon.

I'll let you know how i get on/try to answer the queries you raised.

I'm surprised in a good way, how helpful you guys are in responding to a newbie's question.

As i said to Phil, above - any help would be really appreicated.

Hopefully once i use Filemaker more (and learn), perhaps in the future i will be able to help soneone too.

Kindest Regards,

Shane.

[Inky Phil]

Shane

Have a look at the attached file to see if it gives you any pointers.

I have taken the liberty of simplifying your relationships down to Membership No only in order to keep the relationship structure simple enough for a beginner to have a chance at seeing how the related information is displayed both through portals and a simple second table occurence of the membership file.

I think that once you grasp the concept then you will see slimjim's point about having data duplicated everywhere as being unnecessary.

Get back if I can help

Phil

Simple_Unity_.zip

[shaneferguson]

Many thanks Phil.

I'll have a look at it now, and let you know how i get on.

Kindest Regards,

Shane.

[shaneferguson]

Hi all,

I just wanted to try to explain more clearly what i'm trying to create/achieve.

Hopefully, the following will help you to help me help myself more. I'm sure that the grateful advice that has been offered to me is still relevant - i just want to make sure.

Here goes:

I need a membership database that has a main screen. This screen/layout/whatever will list various fields such as membership number, name, address, d.o.b, current factory (place of work), current benefits received,etc. 39 fields in all.

The main non-changable/unique piece of data would be 'Membership number' - though fields such as Sex (Male/Female), Forename, etc wouldn't change.

I have simplified the database that i previusly attached, to one that just has one layout - the most important.

I need 'History' for all of the fields - 39 in total.

What is required is that when a user updates any of this information,a screen showing all the previous changes to that members data can be accessed - with each member obviously able to have more than one change to their data.

i.e: perhaps at the bottom of the 'Member/Factory Details' screen would be a button called 'History' - this could open a new screen/window with tabbed screens each showing a different change/past states of data. Or the 'History' appearing at the bottom of the same screen would do. As long as it's neat and tidy, and can be used easily.

Is this possible...?

I understand what both SlimJim, and Phil are saying - in that the best way to achieve a 'History' is to not repeat data fields on different layouts (the database now only has one, though obviously your suggestions may increase this amount again)and to make sure that it's all tied together via a unique reference (i can use the Membership Number..? ) - and that portals could be used to do this.

However, i can't seem to get my head around portals - will i need 39 portals, because i need 39 fields in order to have a full history for each member..?

If so, can all these portals appear on the same layout..?

And if that's all 'do-able' - can i browse between different layouts (full of portals) showing the changes to that particular members data...?

Forgive, if these queries are stupid - and this text is rambling.

Again, many thanks to any help/guidance that you can provide.

Kindest Regards,

Shane.

unitydatabasecopy.zip

[DukeS]

Hi Shane,

I think that you are trying to do some kind of logging sistem that tracks every change in fields across the solution.

I would recommend you to take a look of Audit Logging technique at www.filemakermagazine.com under subscribers area. If you are not subscriber I would recommend you to become one if you want to stay in in bussiness.

Regards

Dejan

[shaneferguson]

Hi Dejan,

Thanks for the tip - i'll go have a look now.

Regards,

Shane

[Inky Phil]

Shane,

Have a look at this example. Use it purely to see if it does what you want - use 001 and 002 as the member nos as before and scroll the portal to see the history.

Please do not think that this is a good working example, I have dashed it off v. quickly and it is well flawed but if it is what you are aiming at then we can do it properly

Regards

Phil

39fieldportal.zip

[shaneferguson]

Hi Phil,

Sorry - what have i got to look at..?

Your previously attached file, or is there a new one...?

Many thanks for yuor help.

Regards,

Shane

SORRY - Got the file now, cheers - i'll have a look. (i swear it wasn't there a second ago - though it has been a long day...) :-)

Edited September 7, 2005 by Guest

[shaneferguson]

Hi Phil,

Yes!! That's the type of thing i am aiming for. It is certainly on the right track.

As a History - all the fields are there - so, no matter if one bit of data is changed or 10 bits, a 'snapshot' of all 39 fields is available to view by the user. :-)

Then over time, all these 'snapshots' are viewable.

Layout-wise - can a 'Input' form (with all 39 fields), and the scrolling 'History' be placed side by side on the screen (or above each other) ?

I appreicate all this help that you are providing.

I look forward to your reply.

Kindest Regards,

Shane.

[Inky Phil]

OK Shane

Glad that helped.

I have to go now but I will post again tomorrow cos there are quite a few issues that I think we ought to discuss before we progress any further but I will be in the office all day and I will post again before 9 in the morning

Regards

Phil