4 Replies Latest reply on Mar 6, 2009 6:22 AM by bivalve

"Set Field" With Criteria

Title

"Set Field" With Criteria

Post

Hi -

Here is what I have:

Table 1: "Employees", which includes the fields "Name" and "Salary".

Table 2: "Salary History", which includes the fields "Name", "Salary" and "Active".

(In Table 2, "Active" is a 0/1 binary field that indicates which record in "Salary" is the employee's present salary.)

***

And here what I would like:

I would like to set Table1:Salary equal to Table2:Salary, where Table2:Active = 1

The part that is giving me trouble is setting the "where" criteria; I'm new to Filemaker, and this step sems like it ought to be pretty simple, but I am having trouble figuring out how to do it.

***

Many thanks for the assistance; it will be greatly appreciated!

• 1. Re: "Set Field" With Criteria

Howdy bivalve,

Welcome to the forum and thanks for the post.

Rather than post a "Script fix", may I ask a "why"?

If you already have the current salary in the Dbase, tagged with an Active Flag.  Why store it again in another place?

If Employee Table has a global field "Status" set to "1"

and the Salary history T.O. is linked to Employee Table T.O. by BOTH Employee::Name=SalaryHistory::Name AND Employee::Status = SalaryHistory::Active, then you have a one to one relationship and can use the SalaryHistory::Salary field without having to duplicate it.

Did I make sense or was I confusing?

PS. You aren't using the "Name" as a unique employee identifier are you?  This is bad juju...the relationship I typed above should be UniqueID=UniqueID instead of Name=Name.

• 2. Re: "Set Field" With Criteria

Thank you, Ninja, for your help.

One of my tables is just a list of every employee and their present salary. But each employee's salary history might have ten or so records, and I thought it would be inefficient to expand my main table ten-fold just to store historical data. But maybe I'm wrong, and it would be better to just keep it all in one place.

I used to use Access, and in this case, I would create a select query (the historical data where "Active Salary" = 1) and link that to my main table.

I find it hard to accept the Filemaker does not have a simple equivalent of "Select Where" query.

So, even if my DB design might not be optimal, I would still be very interested in how to perform such a query.

Also, I'm afraid I'm unfamiliar with the abbreviation "T.O.", so, well yes, I am confused.

Thank you again for your time and patience; if you can provide more assistance, I would be quite happy.

Thanks!

• 3. Re: "Set Field" With Criteria

Howdy bivalve,

Sorry: T.O. = Table Occurrence (in the relationship chart)

I'm not so familiar with Access, but I'm assuing what a select Where is from context.  In my apps needing something like this, I've had great success simply using a relationship as a filter so I don't have to copy fields and double store data.

Your employee table is already related to the salary table by Name.

Relate your employee table again to another occurrence of the salary table (SALARYTABLE2) by both Name AND Active Flag.  Any fields you now put onto the Employee layout will only populate from records in the salary table that are matched to both criteria.  Now when you give them a raise, you change the active flag in the Salary Table as you do already, and the Employee Layout updates already...no muss, no fuss...as long as you've setup your global Employee::ActiveFlag = 1.

Or you can go to the salary table, find the active record with that employee name, and SetField back into your employee table in a new field if that's how you want to do it.  This would be a script that you would have to remember to launch when you change thier salary...that's why I'm leaning against it.  It is possible, and pretty direct to script it in.  If you need to remember to launch a script, why not just copy and paste manually...its only one number, right?

Maybe I'm missing your point, if so I'm sorry and I'll try harder to get on your page.  I understand you to be proposing higher maintenance things and I'm trying to give you lower maintenance ones instead.  Am I missing your goal?

• 4. Re: "Set Field" With Criteria

Thank you, Ninja; you've been a great help.

Not only was your solution ideal, but it taught me more about the nature of Filemaker than all the books combined. This will take a bit of getting used to...