Jump to content
Sign in to follow this  
phuebs

Relationships Killing Me!

Recommended Posts

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!

Share this post


Link to post
Share on other sites

See an example here:

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

Prospects (Contacts)

Callers (Organizations)

Calls (Affiliations)

Share this post


Link to post
Share on other sites

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....

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!!

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 by Guest

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

  • Who Viewed the Topic

    1 member has viewed this topic:
    Will Xu 
×

Important Information

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