I wish that there were a spreadsheet-like function like Lookup ( =lookup(data-range,search-criteria,result-range )

I'm finding that the lookup function and lookup action in text and number fields is a lot more complicated.

Can anyone direct a novice to some basic information on how to do a lookup as prescribed above?

The Table View was created for Spreadsheet view. However, it isn't a spreadsheet. Once you understand that you are not using a Spreadsheet, you will then understand that things are a lot different.

I believe what you want isn't lookups, but is auto complete. You will find Lookup (Pages 71 - 80), and then look at the Auto-Complete (page 62) in your user Manual.

HTH

Lee

Thanks for the reply, Lee.

I'm learning that treating FM like a spreadsheet will only get me in trouble. )

Here's the deal, in a very small nutshell:

Two tables: 'Global,' and 'Department'

• In a secondary layout, 'Global' shows a list of people (records) that pertain to a certain department (e.g., Human Resources is chosen, and then only people in Human Resources show in the results)

• 'Department' has a field called "HeadCount" (How many people are supposed to be in the Human Resources department , not how many there are). Each department entry is a record.

• I'm trying to have a field in 'Global' that finds what department is being searched, and then pulls the respective head count amount from the correct record

I'm stumped. Hopefully my nutshell makes sense.

Do search of the Forum for Conditional Value Lists, and I think you will find what you are wanting.

This is actually simpler in Filemaker than in Excel, because all you need is a relationship. However I don't fully understand your description. Shouldn't there should be a table of People somewhere?

P.S. I don't think conditional value lists are relevant to your issue.

Edited May 15, 200817 yr by Guest

The 'Global' table is where each record is a person who works at company x.

Here's what I need:

- Each person belongs to a department

- When I search by department, I need to retrieve, from the 'Department' table, that department's head-count

(each department in the 'Department' table is it's own record, with a corresponding field called "HeadCount")

I don't know how to configure the relationship so that I can make this work. Any suggestions?

Thanks

The 'Global' table is where each record is a person who works at company x.

Why not call it "People" or "Employees", then? Not to mention that the word "global" has a special meaning in Filemaker.

Anyway, when you say that each person belongs to a department, that means there is (or should be) a relationship between the two tables.

When I search by department, I need to retrieve, from the 'Department' table, that department's head-count

There are several ways to achieve that. Take a look at the attached file for the simplest one (I think), and if that's not enough then clarify some more.

EmpDep.fp7.zip

I got it. I swear I was doing that earlier.

Thanks!!