Set Field plus Email

[phunkidude]

I am making a Contacts database consisting of:

1. Contacts: People's names and contact details (just phone and email) plus the company they work for.

2. Companies: Address, phone, website and a portal showing people who work for this company (related to the 'Contacts' table).

I'm trying to do two things but failing spectacularly.

1. On the 'Companies' table (with the portal) I have a button for people to add a new 'Contact'. It's just a script that goes to the 'Contacts' layout and makes a new record. However I want it to set the field on the 'Contacts' table to show the company name from which I clicked the 'Add Contact' button. Does that makes sense?

2. On the 'Companies' table (with the portal) I have a button next to each contact which opens the user's email client and pastes in the name and email address. However if there is more than one name in the portal it always copies and pastes the top name regardless and not the name you click next to.

Thanks for any help offered. I realize these are probably ridiculously simple things but I'm still new at all this.

[phunkidude]

Anybody help a noob out?

:)

[phunkidude]

Hi all, I figured out my first issue but I'm still struggling with my second problem (plus a new one). I guess given the lack of responses I might not have been clear enough so I thought I would include some screenshots and review my problems:

This is my 'Contacts' table showing details of people:

This is my 'Companies' table showing company details and related 'Contacts' displaying in a portal:

1. My first problem is that when I click on the blue email symbol on the 'Companies' table (in the portal) it always loads an email with the address of the first person listed regardless of whether I click on the first, second, third or whichever listing in the portal. So in the example shown, if I click on the email link for 'Ron Manager' it persists in loading an email with Bob Fleming's address details.

2. My second problem is that on the 'Contacts' I have a checkbox which assigns a person as the primary or default contact for a particular company. When this box is checked I want that person to be listed first in the portal on the 'Companies' table. My specific problems with this are:

a.) I'm struggling with calculations and scripts so I'm not sure which calculation recognizes whether a checkbox is 'checked' or not.

b.) I'm not sure how to dictate the order of entries in a portal or even if it's possible.

c.) When a checkbox is checked by the user how do I have any previously checked boxes on other 'Contacts' entries be unchecked if they are no longer going to be the primary contact. Hope that makes sense.

I'm finding this pretty tough but I'm keen to learn so would appreciate some insight from you experts.

Many thanks

Sean

[mr_vodka]

1. My first problem is that when I click on the blue email symbol on the 'Companies' table (in the portal) it always loads an email with the address of the first person listed regardless of whether I click on the first, second, third or whichever listing in the portal. So in the example shown, if I click on the email link for 'Ron Manager' it persists in loading an email with Bob Fleming's address details.

Since you are on the parent record, it is looking at the first related record when you run your Send Mail [] script step.

Try sending the email address from the same relationship as your portal for your contacts via a script parameter. Then in your Send Mail script step, use specify calculation and set it via Get (ScriptParameter)

2. My second problem is that on the 'Contacts' I have a checkbox which assigns a person as the primary or default contact for a particular company. When this box is checked I want that person to be listed first in the portal on the 'Companies' table. My specific problems with this are:

a.) I'm struggling with calculations and scripts so I'm not sure which calculation recognizes whether a checkbox is 'checked' or not.

b.) I'm not sure how to dictate the order of entries in a portal or even if it's possible.

c.) When a checkbox is checked by the user how do I have any previously checked boxes on other 'Contacts' entries be unchecked if they are no longer going to be the primary contact. Hope that makes sense.

My suggestion is that you have a field in the Company table that stores the related main Contact ID. You dont have to do it this way, but I prefer it this way.

The script for your checkbox button that is on the portal would set this maincontactID with the selected contact ID.

Then on your contacts table, you could have a calc that is simply ( maincontactID = pkContactID ) when the contact ID matches the set maincontactID the field will return 1.

Finally, set your portal to sort by descending order based off this new calc.

[color:black]

[Fenton]

It is somewhat difficult to answer. There are more ways that things can go wrong (i.e., be done wrong; it is, contrary to popular belief, seldom FileMaker's fault that things don't work as expected :-) than ways that they can be done right (though there's often more than one).

1. Two likely guesses. What you want is for the email address of the person you clicked on to be the one in the Send Mail step. So, is that true? Is the email specified via the same relationship as the portal is? Because when you click on a portal row you are specifying it via its relationship.

Another possibility is that you do have the email in the Send Mail step specified, but you are using a script, and somewhere before the Send Mail you are doing something which loses focus to the portal row. The Commit Records always exits the portal, so it's the most likely suspect.

It is also possible to set the email into a Variable or global field immediately, and use that in the Send Mail. Then you don't have to worry about losing focus afterwards.

2. A "checkbox" just means that you've entered a value matching the Value List attached to the checkbox field.

a. So your calculation would normally be looking to see if a value is one of the possible multiple lines of the field. In this case however you should be using a Boolean value in your value list. I almost always have a value list with only possible value, 1. It's either 1 or nothing. I often name such fields like this: "Primary_flag." Then I can test via:

Primary_flag = 1

b. You could then sort the portal descending by Primary_flag

c. This one is more difficult. There are two choices. The first is easier for you, using validation, but more difficult for the users. The second is a little more work, but (much) more user-friendly, so I'll describe it.

What you want to do is much what you'd do manually. You'd check the Primary_flag on the record you're on. Then you'd do a Find for the Company ID in Contacts (where you are). Then you'd look at each of the ones you found (likely few), and uncheck Primary_flag, other than on the one you just checked.

So you'd do the same, but via a script attached to the Primary_flag checkbox field. But there are problems.

1) You don't really want to lose the user's found set just so you can do this. Which the Find (or self-relationship) would do. My solution would be to save the found set, process, then restore the found set. There is a clever technique to do this (by others). But I'll have to show you in an example file.

2) You don't want to process the above if they're in Find mode already, just trying to do a Find on Primary_flag. You just want to set the field to the opposite of what it is (in Find mode). So you can trap for that, with: Get (WindowMode) = 0 for Browse mode (or conversely, Get (WindowMode) = 1, for Find mode.

3) You don't want to uncheck the one you just checked. But how can you tell, when you're looping thru the records. You'd set its ID into a script parameter, via the button (the field itself) running the script.

Easy breezy :-] I'll (or other) will have to make an example file. Or, better, you could post yours. It looks like you've only got a few records. If not, Save a Copy as Clone, then zip and post that.

[Fenton]

RE: 2.c. David's method is certainly simpler. Though there may be times when having the Primary_flag in Contacts is useful (though I can't think of any). So I'd probably still do it my way. But then I know how to do it :-]

[phunkidude]

John,

thank you so much for replying!

I struggled to make sense of what you wrote in regard to my first problem as I was not familiar with Script Parameters but I did some reading up and I think I've cracked it.

Now I'm trying to understand your solution to my second problem...

Thanks for your help.

Sean

Edit:

Fenton, thanks for your reply, I hadn't seen your post when I replied above so I will take you up on your offer to look at the files. Any opportunity to be critiqued on my work so far would I'm sure make things easier down the road.

MyDatabase

Edited February 25, 2008 by Guest

[mr_vodka]

BTW. I did forget to mention about how to display the check for the checkbox. As Fenton pointed out, create a value list with the value of '1'. The calc field attributes shoul dbe setup with a checkbox using that new value list that you created. Now when the calc returns a true result the checkbox will be flagged.

[phunkidude]

Easy breezy :-] I'll (or other) will have to make an example file. Or, better, you could post yours. It looks like you've only got a few records. If not, Save a Copy as Clone, then zip and post that.

Fenton, did you get a chance to look over my database, by any chance? I'm eager to see your solution.