Custom Function that queries table

[Rodd]

I would like to be able to create a function that accepts as it's input parameter an Employee No (EmpNo) then have the custom function query a table (ADMIN_EMPLOYEES) to return the related Employee ID (EmpID). I'm doing an upgrade/conversion from FM5.5 and the EmpID is a auto-generated field.

My problem is that I don't seem to see a way to process a query/find within a custom function, or am I missing something?

[David Jondreau]

Why do you want to do this? It's an unconventional use of custom functions, like using a crossbow to fish. You could do it this way if you wanted, but there are other, easier, ways.

I'd say to query a table strictly by using a custom function, you should have a separate TO. Call it cfAdmin_Employees so that you'd always have all the records as a found set. Then you'd write a recursive function that uses GetNthRecord to compare the record value to the parameter.

But why not use the right tools for the job? Like a relationship based lookup or auto enter calc or even a scripted find?

It really depends on WHY you're doing this, but you could, for example:

Create a couple fields in a one record table, EmpID and EmpName. Create relationship OneTable::EmpID = Admin_Employees::EmpID. For OneTable::EmpName, have it auto enter from the Admin_Emp table. Through in an Evaluate to refresh when OneTable::EmpID changes and you're good to go.

[Rodd]

Thanks for the reply.

Here's why I was considering this. (Keep in mind I come from the Oracle/PHP perspective so my outlook may be wrong for Filemaker in this case). I had to create a proper schema for this system I'm converting. By this, I mean I created separate tables within one file that have the base employee data, a universal email address table (there are customers, etc too), an address table, an employee notes table and a phone number table. Since the data in the original FM5.5 table is basically flat, I use an import function and only bring in the items that make sense for new base table then go back and requery this base table to use as a driver to populate the associated tables via lookups. Since employee notes are unique to employees, no big deal, I used the existing EmpNo. But since the other tables are shared by other areas, I need to use the serial number id field (EmpID) which is only generated during the import. I was hoping the custom function would be more like an Oracle function whereby I had full control with in it to do whatever I wanted, i.e. process a query when passed the EmpNo and return the EmpID. Then I could use a set field and save out the related data.

[David Jondreau]

But since the other tables are shared by other areas, I need to use the serial number id field (EmpID) which is only generated during the import.

I'm not sure what you mean by this? How are the other tables shared? Why can't you use the EmpNo here? Is it because you have an Employee table and a Customer table and the two numbers might conflict?

[Rodd]

Correct. My intention, good or otherwise, was to create a single tables for addresses, emails, phone numbers for employees, vendors, customers so that I could manage them in one place (Oracle mentality).

I did manage to add a lookup post import, inside the main loop where I push out notes, addresses, etc. So I guess my idea of a custom function is not all that necessary. But I do want to ask your opinion about related tables. At this point, I'm thinking that instead of driving a unique auto-generate at the address table, lets say, I'm thinking I should just only require it (the address key) to be unique, then push in the EmpID since I formatted it with "EMP000001", and then customers would be "CUS000001", and so on. Or should I instead auto create the key at the Address table with a getnextserial?

Thanks for all the help

[David Jondreau]

I don't know Oracle, so I don't know the context you're coming form, but it can be a great idea to separate addresses, notes, contact numbers, etc into different tables.

You can have the auto enter serial as a field unto itself, then create a calc field that = "EMP" & EmpID and ensure unique ids that way. You can also define your relationhip between phone numbers and employees with two criteria, the ID, and a text value, like "Employee" which in the Employye table would be a constant, but in the Phone Number table would depend on the entity that it was related to.

But, personally, I'd keep employees/customers/vendors in the in the same table. They're of the same class no? Same with phone numbers and e-mail addresses.

[T-Square]

Thinking out loud here...

What about putting all the people in one file, and designating whether they are employees or customers by using a flag field? Then, you build your interface with an initial filtration based on the flag value. That is, if I am going to be handling employee functions, select the employee flag and go to the employee layouts (with buttons to perform employee functions, etc.).

This direction reminds me of the discussion a while back about using a single table for everything, but perhaps you could use a piece of the concept here.

David