Relationships Killing Me!

[phuebs]

HELP! Self-taught Filemaker user. I'm doing really well so far...but struggling on grasping the relationships. I understand what they do and how they work, but I'm missing something when I set them up.

I have a database of prospects for our school/church. We make "calls" on these prospects--either by phone, E-mail, or personal visits.

I established three entity tables: 1)Prospects, 2)Calls, 3)Caller (person making the contact).

I want to have one of my tabs on my Prospects layout contain a portal that shows all calls made on that contact, and who it was that made the call. I don't really need to see the layouts and records for calls or callers, just in reports.

I have a primary key for each table. I have a foreign key for prospects in calls and callers, for calls in prospects and callers, and for callers in prospects and calls.

BUT I JUST DON'T GET WHICH ONES TO TIE TOGETHER TO MAKE IT DISPLAY RIGHT!

Thanks for your help!

[comment]

See an example here:

http://www.fmforums.com/forum/showpost.php?post/246136/

Prospects (Contacts)

Callers (Organizations)

Calls (Affiliations)

[phuebs]

Thanks. I tried it, but something just was not working yet. I'm attaching an empty copy of my work so far. What I want is for the tab under Prospects called "New Call Activity" to display what I had previous done with the tab "Call Activity" in the past using just repeating fields.

You can see in the relationships how I tried to set it up, but it isn't working. I add new "calls" in the table, but the wrong prospect ID shows up, so I know I'm off to a bad start right there.

Thanks for your help someone!

If my fp7 file didn't attach right let me know....

[comment]

Letting you know... (you must zip your file before you can post it here).

[phuebs]

Let's try this

PM_Test.fp7.zip

[comment]

You just need to place a portal to the Calls table where the repeating fields are now.

However, your foreign keys in the Calls table CANNOT be global!!

[phuebs]

That's strange about the global thing, because studying the FM Training Series book and samples, it appears their kf_'s are all global! That's probably where I went wrong. It seems to almost do what I want now.

I've reattached the file with some sample records. The portal I'm using and refining is under the tab "New Call Activity". It does what I want for "calls" and creates records appropriately in the related table.

However, I also want to be able to choose the person (caller) who made the "call" within this portal, and then appropriately create a related record for the call that person made.

Is it just going to be better to have a drop down list field in the "Calls" table named "callers" from which I can select (or add) a name? Or is that going to make it wicked hard to do reports then for who made what calls on what prospects? (I think that answer is yes)

I would prefer to have the "Callers" be a separate table because they might not only do these "outreach" calls on prospective school or church people, but they might also do "inreach" calls on current school or church people....which is a table to be made later.

Attachment.....Thanks for the help!

PM_Test.fp7.zip

[comment]

studying the FM Training Series book and samples, it appears their kf_'s are all global!

I should hope not!

I also want to be able to choose the person (caller) who made the "call" within this portal, and then appropriately create a related record for the call that person made.

Have you taken a look at the file I linked to earlier? That's exactly what it does.

You can select the caller by name only (provided the names are unique) - if you define the value list to show only the "second" field, and use a pop-up rather than a drop-down. There are other methods of selection, e.g. have a script open a new window with a list to choose from. But you should always STORE the caller's ID, not name.

I would prefer to have the "Callers" be a separate table

I am afraid I don't follow: you DO have a separate table of Callers - as you well should.

[phuebs]

Ok, I am so close I can taste it...but want to pound my screen in!

I don't know why this is giving me such problems. Anyways,

Now I have things happening like when I enter the "Caller" into the table, chosen by drop down ID, phantom "Caller" records are created in that table. Also, it seems like when I change records some are being deleted for some reason. It's just not quite behaving right. I've tried to model as much as possible off that demo.....

Oh, and I would prefer to see who the "Caller" was, not just the ID. Then I can know right away who it was that made the phone call, etc. How might I do that? I tried to flip flop the first and second categories (CallerID and FullName) and then things really got messed up.

I've attached the file again with the changes I've made to show where I'm at.

PM_Test.fp7_2.zip

[bruceR]

You have some unnecesary fields.

You put an incorrect field in the portal.

You defined combined name as giving a number result.

PM_Test_2.zip

Edited May 28, 2009 by Guest