Lookup Sorrows

[Nomad Suit]

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?

[Lee Smith]

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

[Nomad Suit]

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.

[Lee Smith]

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

[comment]

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, 2008 by Guest

[Nomad Suit]

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

[comment]

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

[Nomad Suit]

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

Thanks!!