Cross dressing contacts

[hillborne]

Hi All,

The general information:

Someone requests a sample and I need to track who will be sending the sample, and who will be receiving it...

The problem I can't figure out, is that the entity that sends and receives is the same: a Contact. How can a contact be 2 different things at the same time?

Specifics: (looking at contacts.jpg)

I have a Sample_Request table where I generate the request and setup the relationship through 2 portals into the green join table (Sample_To_Contacts) between the request and the contact.

I kinda have it working but I know it's not the correct way to do it. (Notice I said 2 portals...looking into the same table with the same relationship...this seems bad) *see weirdportals.jpg

On the Sample_Request table I have 1 portal set to display 1 record which the user will mark as Sender.

Right next to that I have another portal set to diplay 1 record (starting on row 2) which the user marks as Receiver.

The 'Sender' and 'Receiver' fields are in the join table.

It's kinda working now, but the design is poor.

Relying on the user to mark them correctly is too risky and I don't like it. I want 2 unique portals clearly marked as Sender and Receiver so that any contact they pull up in the Sender Portal, is really a sender...and same for the Receiver.

I'm guessing that I need 2 TO's of the join table as well as the Contacts table? I tried doing that but wasn't sure how to setup the relationships.

What further confused me, is that I'd like to use a dynamic value list so the user can just start typing a contacts name and it fills in all the rest of the details. So do I need 2 value lists as well?

Please help me...I can't take anymore advil for 4 hours...

Thanks!

[Dr. Evil]

Hello,

I "think" you are making it more complicated then it is.

In your Sample Request Table Occurrence Group you need to set up a relationship to contacts for both receiver and sender.

I have attached a small sample, let me know if you have any questions, and I will try to help.

Good Luck!

sample_contacts.fp7.zip

[hillborne]

I think I get it, now.

So what you're saying is I don't even need the join table between the Request and the Contacts.....

oh...wow...

I guess I did over-complicate it. I'm still getting used to figure out what is truly a many-to-many relationship.

I incorrectly reasoned that since the request would hold more than 1 contact (A sender + A receiver) and that 1 contact could be associated with many requests...hmmm.

I'm still rolling it around in my head but I think you're right!

Thanks!

[Fenton]

Because a Sample only has 1 sender and 1 receiver you do not need a join table. The challenge here is more how to make a decent interface for choosing. Because you can only be on either the "sender" or the "receiver" record (or neither) at any one time.

I think the best interface would be to be on neither Contact record,* but have 2 tallish narrow filtered portals, in the Samples table, potential senders on the left, receivers on the right, with your 2 smaller cards in between. Clicking a name in the portals sets their ID into one of the two foreign keys, which the other data either just shown relationally, or looked up, depending on whether you need an absolute historical reference or not.

*Unless you naturally start on one of the Contact records. In which case you only need a portal for the other, after creating a new record for them (as sender?) in Samples.

[hillborne]

Hi Fenton,

Yeah, I'm working on the UI now and came to a problem that maybe your way solves but I'm not sure.

When you set up a Value List, you naturally choose as the first value the arbitrary foreign key and then the second value is some identifier which you normally choose to display only. In my case, I created a calc field Full_Name that combines the names together.

What sucks is that a user has to scroll through the pop up list (or a portal) of all the names when all I really want them to is start typing the name and have it fill in automatically. But that isn't an option because you have to choose the ID# instead.

The only way I figured out is to create a value list based on Full_Name and use that as the foreign key...but of course that is bad because there might be 2 people with the same first and last name.

Is there a way to have the ease of a fill-as-you-type data entry method but with the security of having truly unique keys?

[Dr. Evil]

Hello voyeur,

I am interested in the answer to your question as well.

At the moment, I have my value list set:

to display only second field and sort by second field

Option: Drop-Down List

I mention this because, once user clicks or tabs to field w/ an assigned value list, user simply may begin typing first letters of client or whatever and curser scrolls to values w/ the same beginning letters. For example, user would tab to client field and type "ry" and "Ryan and Co" would become highlighted. If user does not pause in typing, user can type out the entire name and pin point the value selection.

This works on the Mac platform, I'm sure it is the same for PC.

I look forward to hearing what others have to say on this topic.

I'm guessing a work-around would be more trouble then it is worth, sense most solutions have quite a few value list throughout solution.

You mention you would like to use a dynamic value list?

I myself don't fully understand what you need this dynamic value list to do. But here is a stab.

Can your contacts be sender, receiver or both?

I ponder the thought, you could have a field in your contacts table with a check-list w/ values of

- Sender

- Receiver

- Both

and then in your parent table have a global field you could set with radio button of same values

- Sender

- Receiver

- Both

then set up a relationship using the global sort field in parent table and sender check_list field in child.

Edited August 1, 2006 by Guest
Had an extra thought

[hillborne]

Yes, I can only get it to work when I use the name as the primary key...

I wish FMP would allow the auto fill in to work when you choose the "Only show values from second field" instead of thinking you are typing the ID#. (It obviously can't guess what number you are typing)

The only other way I can think of doing it would be to just have a dummy field for the user to enter data in. The dummy field would fill in with auto-type-complete and then the user can press a button to start a script that sets the key values and fills in the rest of the data. But then again, if there ARE two names that are the same, how will the user know which is which anyway?

Or are we missing some other way?

****

The contacts could be either sender or receiver. That was what confused me in the beginning since there wasn't an attribute I could filter them by.

[hillborne]

Well...Dr.Evil pointed out the reason why I couldn't autocomplete the value lists when having the primary key be autogenerated...

All my key fields were set to be numbers. (I saw the options under auto enter -> serial NUMBER and made them numbers)

When they are set to text, they allow auto-complete by the second field in the value list!!! Thanks Dr.Evil!

How you are supposed to figure that out is beyond me, especially at 1am, but now I know and that is supposedly half the battle.

Go Joe!

[Dr. Evil]

setting your key/matchfield to a number or text does not matter.

What I explained above works regardless, sense you are displaying and sorting by second field which is a text field.