Relationship question

[skearton]

I have a Quote table with a field on it named "_kf_ContactID_OrderedBy".

On the Quote_Detail Layout I have a portal "quote_project_PROJECTCONTACTS" and within the ProjectContacts table there is a field called "Type", where the used can select from 1 of 4 types: Bill To, Ordered By, Ship To and Install To.

The user can add as many project contacts as needed and designate each contact a "type" multiple times. So, a user could add two different contacts and make both "Ordered By".

I want the field "_kf_ContactID_OrderedBy" on the quote table, auto set the ID of the contact if there is only one "Ordered By" type, and if there is more than one, let the user select, via a drop down list, the one they want to use on the Quote form.

I'm having trouble setting up the filtering relationship to do this. Right now I can see all project contacts in the drop down list, but need to narrow it down to just that projectID and the project contact type of "Ordered By".

Any suggestions on how to handle this would be appreciated.

[skearton]

OK, so I got it to filter finally, but now I'm wondering how to auto populate the Quote ATTN drop down value list IF there is only one contact with the type of "Ordered By". Any smart ways to do this?

[Tom R.]

I'm not sure I fully grasp your situation, but it sounds like you might find the following helpful. There's a post at FilemakerHacks regarding dwindling value lists - http://www.filemakerhacks.com/?p=5219. Also, FileMakerMagazine.com has a video tutorial on the topic, which they refer to as Cascading Value Lists - http://www.filemakermagazine.com/videos/cascading-value-lists-learning-executesql.html. You can preview the video but the full version requires a subscription. (The site is very helpful and well worth the subscription imho.) If the links are not helpful, my apologies.

Good luck,

Tom