Jump to content

I have a newbie question


Nickrez

This topic is 6675 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi, I'm just getting into fmp. I have a database started for my company...it's basically a contact database...names, #'s, emails, URLs, etc. etc.

All this info is in one main table.

I want to change the layout, so, for each company or contact, there's a new table that exists, where I can enter all information following an actual contact....e.g. date of contact, who I spoke to, what was talked about. I guess I'm getting confused, because there are going to be multiple contacts for some of these original records. I can't figure out a way to logically connect this to the original table, because of the instance of multiple contacts.

Do I need to create a whole new table for each original contact (or record), or can I make a new table that will just contain the information individually for each record. I think I'm lacking some understanding of table relationship, and I'm wondering if anyone has a simple way of describing how I would do this. I apologize if this is a ridiculous question, but like I said, I'm NEW. :

Link to comment
Share on other sites

It is called a one to many relationship. open the sample file

You have clients A,B,C,D

Client A has contact 5,6,7

Client B has contact 1

Client D has contact 2,3,4

Make a table for clients

Make a table for contacts and create a relationship between clientid (this is also know as primary and foreign key)

Also search for "one to many relationship" on the forum and on google.com.

Notice that the contact layout shows the clients name as a,b,c,d. This is taken from the other table. Try this. Change client 1 name from A to E and look back at the contact list. Contact 5,6,7 should now show E as the client name.

many.fp7.zip

Link to comment
Share on other sites

So, I've been trying to figure this out for quite some time....I think a big problem I'm having is understanding what to relate and how to display it.

I was given the startings of this database from a new web master, but he's no whiz with filemaker....I only have client id #'s for some of the records and I'm not sure how to fix it so every contact has one. I'm going to attach the actual database...if someone has the time to look at the fields and explain how to create and define a new table for it, I'd be really appreciative...I'm a bit lost right now.

Link to comment
Share on other sites

So, all I want for this new table is a field for the Date, and the actual notes of the contact (a simple text field that explains what we talked about). 2 new fileds, and I guess the rest would be common relationships

I can't figure out how to connect the similar fields correctly and relate them well.

Thanks so much for the divine one out there that has the time to guide my rookie ass through this....

P.S. Feel free to keep this file if you're lookin for some multimedia contacts in NYC....

CNS_Contact_Manager.fp7.zip

Link to comment
Share on other sites

I did some google searching also....It looks like I really need to have client id numbers for every record....is there a way to do this now that I'm 500+ records in?

The relationship seems simple enough now...do I have to manually enter a client id # for all of them or can I get around that somehow?

Link to comment
Share on other sites

lol no, theres no way around it... on the upside though you'll learn a lesson and you should be happy its only 500 records... i had to shift the info from over 1500 records manually into a completely restructured relational database :... but on the upside ive never made the mistake again...

Before you do anything though, take a look at the attached file, it shows you the most basic relational structure as well as how you could possibly introduce a contact history portion into your database.

Basic_Structure.zip

Link to comment
Share on other sites

Also, is there a way to make my new Contact ID field (the one I'm manually entering numbers into now)....is there a way to make it "safe"...so only one number can be used once in the whole database, and maybe an error message can come up if one tried to put a number in there for a second time. (Trying to avoid giving two contacts the same number by accident)

Link to comment
Share on other sites

Oh boy, well, i'm going to manually start entering numbers.....Is there a way to automatically designate a new record with a new contact id number anytime a new record is created?

In the Auto-Enter section of the primary ID's field definition, click the option to use serial numbers.

I did some google searching also....It looks like I really need to have client id numbers for every record....is there a way to do this now that I'm 500+ records in?

Find those records that don't have an ID number yet, then use the Replace command under the Records menu. In there you can reserialize the found set.

Link to comment
Share on other sites

Why are you entering 500 Client ID numbers by hand.

I have read this thread a couple of time now, and unless I'm missing something big, I can not understand the need for a manual entry.

Client Id can be made up with any combination of text and numbers, and incremented by using the Replace.

Why not post your file and let us take a look at it before you go through this.

Lee

Link to comment
Share on other sites

Since you hadn't yet used your ContactID for anything, it was pretty easy to reserialize your field. (I just added 530 to the existing, so "1" become "531", in case you need to know.)

Then I Imported the existing Notes into the Contact_Notes table. Then used a calculation to pull the beginning date out: Filter ( LeftWords(Notes; 1); "0123456789-/" ). It missed 53 though, some of which have a date you can pull out manually; some have no date.

You can now delete the Notes field in Contacts, as all the existing notes have been moved and are related. But check first.

The New Note is created with a script, that just goes over and creates it in Contact_Notes. The ContactID is passed via a Script Parameter, accessed in the notes table with Get ( ScriptParameter ). This is important to remember. If you change the text button that runs that script you MUST add the Script Parameter (at the bottom of the attach script dialog). It's the modern way to pass values. The portal is sorted descending by date, so the new one is always on top.

CNS_Contact_Manager2.zip

Link to comment
Share on other sites

Nick,

Sorry I didn't get to your post before you entered the numbers manually, I guess there is a Lesson to be learned here somewhere. :

Before I set down and entered something manually more than a few times (certainly for anything more than 25, I would be spending my time researching out a way using a feature of FileMaker to automate it. This list is deep with experts on FileMaker, and there are often many ways to accomplish the same thing,

Sorry Ender, I didn't see your post ??

At least I know that I was reading it right.

Lee

Link to comment
Share on other sites

Wow, you guys are goin way over my head at this point. Fenton, thanks so much for actually doing this for me, but I need to sort of put my hands on it to get to learn it better, and unfortunately, this program is a slow going process to me. I can write music for an orchestra with no problems, but a script with 2 parameters at this point is HELL. I'm going to attach my file so anyone can check out where I'm at with it.

Here are my current issues:

1-I started making a new table for the actual contacts..the one-to-many relationship. I hope I have connected the right fields to the other table, but I'mm not sure. If someone could take a glance, it woud be great.

2-I just made my first portal, which is going to contain the contacts info (from the second table), but I can't figure out how to actually type in the thing. It looks like it's there, but it's of no use to me now.

3-I also want to make a new tab so I can view just the contact records (2nd table again) in the spreadsheet mode. I'm not sure how to create a new tab, but I'm going to look it up. I guess I'm just wondering if I can view a second table at all.

Link to comment
Share on other sites

Sorry to go over your head. The file I sent is really the simplest way to add sorted rows to a portal. The Script Parameter method is simpler than using other methods, such as global fields; though it is perhaps more cryptic to a beginner.

There is another simpler method, which is to just turn on the "Allow creation of related records" checkbox, in the relationship dialog, for the table where new records would be created. But this doesn't work well for Notes, because you quickly run out of room in the portal. And it's not that useful to see the notes in order of creation; much better to see the latest note at the top; which requires a script for creation.

First, big question, what is the related table?? I assumed it was for Notes. I'm afraid I did really read your original post carefully enough; because it raises some serious questions. I'm going to repeat it.

I want to change the layout, so, for each company or contact, there's a new table that exists, where I can enter all information following an actual contact....e.g. date of contact, who I spoke to, what was talked about. I guess I'm getting confused, because there are going to be multiple contacts for some of these original records. I can't figure out a way to logically connect this to the original table, because of the instance of multiple contacts

In FileMaker (or any other database) a table is for a particular thing ("entity" is the term often used). In the FileMaker template you have the entity for the main table is a "contact." There is also a Company field. But that is just another field for the Contact (an "attribute" of the contact). This Contact table is NOT a "company" table.

FileMaker has faked some functionality for seeing multiple contacts for a company by including a self-relationship on the Company field, as one of its "similar" self-relationships. But this is a weak method, and may not allow to do what you want.

By saying "multiple contacts for some of these original records" you are stepping outside the relational structure of that template. To that you will probably need a separate "Companies" table, with multiple contacts. Certain fields belong to Company, some to Contacts. I would think something like this:

Company:

CompanyID (auto-enter)

Company Name

Address

Contacts:

ContactID (auto-enter)

CompanyID (foreign key)

Phone

Notes:

ContactID

CompanyID

Note

You create a new Note from Contacts, because communication happens with a particular person. You can also get the CompanyID at that time.* That way you can see Notes from either Company or Contacts. Each sees their own, i.e., Company sees all notes with its CompanyID, but a Contact sees only those with its ContactID.

I cannot tell from your data whether you need a separate Company table or not. You have only a few people who are not attached to a company. But you also have NO duplicates in the Company field. So as it is you do not have multiple contacts, yet. Perhaps you can get by without a Company table.

This is a question that we often face when building a new solution.

*Unless you have contacts who are associated with more than 1 company at one time, which makes more trouble.

Link to comment
Share on other sites

FileMaker won't let you enter data now because there is no record in the related table, hence nowhere to put the data.

You could enter data in if you turned on "Allow creation of related records" in the ContactID relationship. Double-click on the white box in the middle of the line; that's where the details of the relationship are shown. Be sure to do it for the related side only (not the main table).

But I think you'll find that the method I used in my example file is better for related notes, as it shows the latest on top, hence can handle "more or less unlimited" notes.

Link to comment
Share on other sites

Great. Thanks again. I gotta tell ya, I really want to use the system you set up, but b4 I do, I want to clunk through my own thing that I started just for learning. I'm keeping the example you sent for later on down the road. Obviously, you've got a better handle on this stuff than most, I hope I'm not insulting you...

Link to comment
Share on other sites

It's good you want to learn how to do it. And you will definitely use the "Allow creation" method often also, for many portals. It's just not the best way for portals that potentially could have many entries, and which have dates.

You will eventually need to learn the scripted method of passing the primary ID via a Script Parameter attached to a button to create a related record. It's a pretty basic FileMaker method. Between these 2 methods you can handle most related record creation situations.

The scripted method also illustrates the importance of being on the correct layout when you create a record, which you might otherwise ignore if you always use "Allow creation."

There are other ways to use "Allow creation" which are more complex (temporary keys). You don't wanna know :-]

Link to comment
Share on other sites

This topic is 6675 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.