FM 8.5

Hello

I have a CONTACTS table with a series of contacts, as in a company
with a group of employees identified by fields, Employee1, Employee2
etc.. When I create an ORDER I want to select an employee as the
contact point from a list of fields but I am not sure how I can
reference a group of fields. Is this possible? If not, what would be a
better approach to creating the employee subset?

Thanks
Matthew

"Buckbuck" <buck.matthew74 (AT) yahoo (DOT) com> wrote

With multiple employee names stored in one company record you aren't going
to be able to get them displayed in a single Value List.

The best option would be to change the structure of the database so that the
employee names are in their own Table (related to the company record,
usually with an auto-entered ID). Then you could create one Value List for
the user to choose the company name and a second Value List to choose the
employee name - using a Relationship you can have the second Value List
retrieve only employee names for the chosen company.

Company Table
CompanyID Text, Auto-enter Serial (Start with C000001,
increment by 1)
CompanyName Text
etc.

Employee Table
CompanyID Text
EmployeeName Text
etc.

Order Table
OrderingCompany Text
OrderingEmployee Text
etc.


In the Order Table, you can define a Value List that retrieves its values
from the Company Table's CompanyName field. Put the OrderingComapny field on
a Layout and format it to use this Value List as either a Pop-up Menu or
Pop-up List.

Define a Relationship that links records in the Order Table with records in
the Employee Table, based on the OrderingCompany field.
e.g.
rel_Employee Match records in Ordering Table with records in
Employee Table
when OrderingCompany =
EmployeeTable::CompanyName

Then you can define a second Value List that retrieves its values from the
Employee Table via this Relationship. Put the OrderingEmployee field on a
Layout and format it to use this Value List as either a Pop-up Menu or
Pop-up List.

Once the user chooses an OrderingCompany, the OrderingEmployee Pop-up Menu
will display just the employees from that company. The user must enter an
OrderingCompany before the OrderingEmployee (otherwise the second Value List
doesn't know what values to display).

Note: This type of system does mean that performing Finds on the
OrderingEmployee field is not possible. If you need to do that you will have
to create a separate "Find" Layout where the OrderingEmployee field uses a
different Value List definition which retrieves ALL the employee names from
the Employees Table.


Helpful Harry )