Date fields and searching for date range

[bobatly]

I'm using 4.1 and am very new to this (about 1 week - boss gave me the book, Filemaker for Dummies and said "go"). I am setting up a database at work with our client profile and contacts. I need to log in every time a client contacts our agency so I put in a date field (entitled "date") and made about 50 copies of it (ie. "date copy", "date copy2", etc) for up to 50 contacts per client. 1) I don't even know if that is correct or not.

2)Now I am trying to figure out how I can do a search to find out which clients contacted us, for example, in December 2000. Since I have 50 separate date fields, I have no idea how to go about writing a script that will do this.

Help! I'm failing miserably!! Thanks in advance.

[yafreax]

oh boy. . . the joys of a boss thrusting something in your face. . . don't worry you'll get the hang of it.

THE FOLLOWING HAS A BUNCH OF INFORMATION THAT MAY NEED TO BE READ, RE-READ, THEN SORTED OUT IN YOUR MIND. I TEND TO GO BACKWARDS SOMETIMES, FORGIVE ME.

anyway on to your problem. It sounds as if you will need two databases. The whole date copy, 1, 2, etc. is not the way to do it.

Have one database that keeps track of all your contacts and profiles. Have a second database that is to keep track of each time they contact your agency. In each database make sure you have a field that will directly relate to the other. For example, if you have different clients have multiple contacts and you just need to keep track of when the client contacted you (no matter which contact), than in the second database, make sure you have a field for that client. Do the same if it's per contact rather than client. Then set up a relationship (File/Define Relationships) based on those key fields.

Then when you are contacted, simply go into the second database, input the correct information and then input the date and time into fields within that database. I would suggest that in the second database make the Client field (or contact field) a pulldown menu based on the Client/Contact field in the original database. This will insure accuracy for the relationship.

To get this information to view in the original DB, you must use a portal. I was well beyond 1 or 2 weeks before i started using portals or even really understanding them. I would suggest you don't wait. Basically create a portal on the desired layout in the original database and define it to the relationship with a however many records viewable as you would like.

Now with a portal, you can take that paragraph two above this one and throw it away. (the one that says to go into the second database and enter information). Because when defining a relationship, you can click that little wonderful box that says "allow creation of related records" or whatever it says. When you do this, then your portal will automatically produce an extra line so that you can simply click into the field and enter information, then the record is added to the second database.

ahh, isn't life grand. I done confusing you. just make sure you put the right fields in the portal or it won't work right anyway.

jeremy

------------------

Life's great when you're a millionaire. . . well, ya know, i'm assuming.

[Vaughan]

Try planning the database a bit before you start building it.

Db planning is an art in itself, but basically write down all of the bits of data to be stored and look at the inter-relationships between them.

Basically you have two: client, contact. Client has name address etc. Contact has date time message etc.

What is the relationship between client and contact? One client can have more than one contact, but one contact has only one client. This is a one-to-many relationship which is easy to implement inm FMP.

Create one db, call it Client and put in all the client data. Give each record an autoentered serial number and make it not modifyable. Create another db for contact, define fields for all its data. Make a field in the database to hold the clientID number.

In the client db, make a relationship to the contact db with the clientId field as the match. Check the "allow creation of related records option." Make a portal on a layout with this relationship and put the related fields in it.

Now make a new client record. In the portal will be a blank row for the contact. Add a contact record. Another new reow appears underneath automagically.

Keep adding clients and contacts.

To solve the date search problem, make a calculation field in the contact db (since this is where the contacts are held) that takes the contact date and converts it into the text that you want.

Year(date) & " " & monthName(date)

Now perform the search upon this field. To make it easier, define a value list on this field and make a pop-up menu to ease the selection of the year/date.

[This message has been edited by Vaughan (edited February 14, 2001).]