Finding Contacts who are Customers

IN CONTACTS table
I have a field that lists of contacts - (NameCompany).
Some NameCompany might be noted through a field drop down list as a
Customer.

On my Invoice Layout I want to create a Customer selection tool that
presents through a drop down Value Listl NameCompany that is Customers
only.

Presently my Customer selection tool is a NameCompany selection tool.
Invoice and Contacts are related through a LINES table. The
relationship between Contact and Invoice is manage accordingly:

INVOICE
kf_Contact_ID

LINES
kf_Contact_ID

CONTACTS
kp_Contact_ID

i imagine this is enough for you to understand my intention. How do I
handle this?

Thanks

Re: Finding Contacts who are Customers

On Jan 3, 11:24 pm, buck.matthe...@yahoo.com wrote:
> IN CONTACTS table
> I have a field that lists of contacts - (NameCompany).
> Some NameCompany might be noted through a field drop down list as a
> Customer.
>
> On my Invoice Layout I want to create a Customer selection tool that
> presents through a drop down Value Listl NameCompany that is Customers
> only.
>
> Presently my Customer selection tool is a NameCompany selection tool.
> Invoice and Contacts are related through a LINES table. The
> relationship between Contact and Invoice is manage accordingly:
>
> INVOICE
> kf_Contact_ID
>
> LINES
> kf_Contact_ID
>
> CONTACTS
> kp_Contact_ID
>
> i imagine this is enough for you to understand my intention. How do I
> handle this?
>
> Thanks

Let me see if I understand. You want a user to able to create a new
Invoice and select a Contact record to relate to it. The choice of
Contact should be limited to those Contacts labelled "Customers". Is
that correct?

If so, and you're using FM7+ (you don't say), you're looking for a
filtered value list. You want a list of values of NameCompany from
the Contacts table. So you need to make a new field in the Contacts
table that is a global calc with a text result = "Customer". Create a
self join (relate one Table Occurance of Contacts to another TO of
Contacts relating the ContactType field to the new global field. Then
create a value list that picks the key field from the new "child" (non-
global) TO starting from the parent/global TO. That will produce a
list of ids of the records marked "Customer".

Good luck.
G

Re: Finding Contacts who are Customers

buck.matthew74@yahoo.com wrote:
> Presently my Customer selection tool is a NameCompany selection tool.
> Invoice and Contacts are related through a LINES table. The
> relationship between Contact and Invoice is manage accordingly:

Grip is correct as one good way of doing it. An alternate way is to
create a new calc field in the Contacts table that is something like:

case(ContactType = "Customer", NameCompany)

Then set your value list to be based on this field instead of the
NameCompany field. This field will only include names for those
companies that are customers.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Howard Schlossberg
FM Professional Solutions, Inc. Los Angeles

FileMaker 8 Certified Developer
Member, FileMaker Business Alliance

Re: Finding Contacts who are Customers

On Jan 4, 11:27 am, Howard Schlossberg
wrote:
> buck.matthe...@yahoo.com wrote:
> > Presently my Customer selection tool is a NameCompany selection tool.
> > Invoice and Contacts are related through a LINES table. The
> > relationship between Contact and Invoice is manage accordingly:
>
> Grip is correct as one good way of doing it. An alternate way is to
> create a new calc field in the Contacts table that is something like:
>
> case(ContactType = "Customer", NameCompany)
>
> Then set your value list to be based on this field instead of the
> NameCompany field. This field will only include names for those
> companies that are customers.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Howard Schlossberg
> FM Professional Solutions, Inc. Los Angeles
>
> FileMaker 8 Certified Developer
> Member, FileMaker Business Alliance

I created a new field in the CONTACTS table called cCustomer.
CCustomer is a calc field returns Text , Case ( Vendor = "Customer";
NameCompany). The field Vendor on the Contacts form is a drop down
value list with one choice - Customer.

I created a new field on the Invoice Form (Invoice Form based off the
Table Occurrence INVOICES) assigned ::cCustomer (CONTACTS), made it a
drop down list related to a new value list Customers_Vend which uses
'Value from field" CONTACTS/cCustomers.

It does not work
Can you help?
thanks

Re: Finding Contacts who are Customers

buck.matthew74@yahoo.com wrote:
> On Jan 4, 11:27 am, Howard Schlossberg
> wrote:
>> buck.matthe...@yahoo.com wrote:
>>> Presently my Customer selection tool is a NameCompany selection tool.
>>> Invoice and Contacts are related through a LINES table. The
>>> relationship between Contact and Invoice is manage accordingly:
>> Grip is correct as one good way of doing it. An alternate way is to
>> create a new calc field in the Contacts table that is something like:
>>
>> case(ContactType = "Customer", NameCompany)
>>
>> Then set your value list to be based on this field instead of the
>> NameCompany field. This field will only include names for those
>> companies that are customers.
>>

>
> I created a new field in the CONTACTS table called cCustomer.
> CCustomer is a calc field returns Text , Case ( Vendor = "Customer";
> NameCompany). The field Vendor on the Contacts form is a drop down
> value list with one choice - Customer.
>
> I created a new field on the Invoice Form (Invoice Form based off the
> Table Occurrence INVOICES) assigned ::cCustomer (CONTACTS), made it a
> drop down list related to a new value list Customers_Vend which uses
> 'Value from field" CONTACTS/cCustomers.
>
> It does not work
> Can you help?

You're not clear on what is not working.

The field on your Invoice form should just be the same customer name
field that you had before. Otherwise, with the cCustomer calc field,
you will be unable to set it. The field you want to set from the value
list on the invoice layout is the original Invoice::CustomerName field.

The dropdown value list itself should work. If it appears blank, then
you forgot to set the cCustomer field as stored and indexed.