Hi,

Here's the picture, I have a database with a table invoice and a table employee.

Each invoice is related to an employee who created it. 1-to-many relationship.

Invoice (pkInvoiceID, ..., fkEmployeeID)

Employee (pkEmployeeID, ..., isActive)

Some employees don't work anymore for the store, there's a field named isActive which is a boolean (number).

In the invoice layout, I'm trying to make a value list with only the active employee, but without success.

Is there a way to do value list which represent this query "Select * from Employe where isActive=true"?

Thank you

If you create a calc of 1 and you use in a join to a new TO for your Employee table then you should be able to get this filtered value list. The relationship should be keyed:

cOne = isActive

I would be inclined to have a field in Employee called ActiveID which would be Auto-Enter (Replace) of:

Case ( IsActive ; EmployeeID )

Or just use a calculation ( but don't make it unstored) ... and then basing a new value list called Active Employees on that instead. It's just another perspective and not necessarily a better technique ...

LaRetta :wink2:

Edited May 29, 200817 yr by Guest
Changed a sentence

It seems like a good workaround.

Is there a way to do a SQL query in FM?

Of course a value list and search are not the same thing. A value list cannot be based upon a found set of records. So I'm a bit confused on why you want to query (find) in FM. It would really help if you provided the context and purpose. If you want a drop-down field to display active employees (so it would be based upon field values) then you must use a relationship. If you want to just find active Employees in the Employee table, you would perform a Find script similar to:

Go to Layout [ layout based upon Employee table ]

Set Error Capture [ On ]

Perform Find [ no dialog ; Find Criteria: isActive (and then type just the value in that field*)

If [ not Get ( FoundCount ) ]

Show Custom Dialog [ OK ; "No active Employees. Ha haha!" ]

Show All Records

Halt Script

End If

... now you have your found set of active Employees

* according to your background and the fact that you typed 'true' in the IsActive field, I will assume that you understand boolean and the value in the field is probably a 1. If so, type just a 1 as the criteria in the stored find. If you have actually inserted the words 'true' then type true.

But again, you are mixing sugar and salt here and value lists and searches just don't go together.

LaRetta