can I filter records using a self-join?

[Owen Mathews]

OK... I have an employee table that stores first and last names, hire date, and termination date. It's good for keeping a historical record of all employees, past and present. What I want to be able to do is filter by whether the employee is current. More often than not, a user of this database will want to only view the current employees. I thought I could do this using a self-join, so I set up two additional fields:

CurrentlyEmployed(Calculation) = if(isempty(TerminationDate); "Yes"; "No")

CurrentlyEmployedMatch(Text; Global) = "Yes"

Now I created a second TO of Employee and joined using the criterion CurrentlyEmployedMatch = CurrentlyEmployed. (I may also be missing an ID field and an ID=ID match here, but that's beside the point for this discussion.)

This didn't work as I expected. How can I go about filtering out employees that are no longer current? To me it seems wrong to put them in a separate table, for many reasons (the main one being that I lose the ability to easily see information from related tables).

My attached file contains this table, plus a few other experiments in the same vein.

testing.fp7.zip

[RalphL]

Your relationship is backwards. I didn't see a layout for current employees, so I added one. Added button to layout employee which goes to related record show only related records using new layout.

Added button on new layout to go to layout employee.

[Owen Mathews]

This sounds like a good start for a solution... Thanks!

I still have some questions though. What if you want to do a find from the current employees view? Will it not be the case that a find will pull in employees regardless of their status? (For example, if I search for LastName = Smith, wouldn't it find all Smiths, including those who no longer were employed?)

[Ender]

If you use scripted finds, you can have your scripts add and omit a request for CurrentlyEmployed= No. Though what I usually do is have the find script populate the status field with the Active text before turning the find over to the user. This way, with the status field on the Find screen, the user can override the default find to find a record that's no longer active.

So it might be

Enter Find Mode []

Set Field [ CurrentlyEmployed ; "Yes" ]

Pause Script []

Perform Find []

...

[Owen Mathews]

Thanks, Ender, for your additional comments. Scripted finds make good sense, and I and can definitely do it without too much trouble, but I just want to make sure...

Is there no way to do it completely automatically, solely through the proper relationship graph, without having to rely on scripts and other tweaks?

[Ender]

Well, you asked about finds.

To simluate finds through relationships, you need to add the filter criteria to the relationship, then use the Go to Related Records [ Show ] script step or button definition to jump to the records defined by that relationship.

In general, using a relationship to jump to related records is a good way to go if the criteria for the "find" is static. Like jumping to related contact notes or something. But it's trickier to design if the user may want to find records by a variety of criteria. Finding Active employees may be done either way, but I like the flexibilty that a Find screen with a traditional Find offers. This way user can find for a specific name, a department, a range of hire dates, or whatever other criteria may come up. Using relationships, it takes more thought (and fields) to make the filters work together.

[Owen Mathews]

Maybe I haven't made myself clear. I agree that you can use GTRR and a proper relationship between TOs to do a static find. I also agree that a solution involving scripted finds works, and that it's more flexible. Here's the scenario I'm envisioning:

1. User is viewing the "all employees" layout.

2. User enters Find mode (from the menu) and types "Smith" in the Last Name field, performs find and sees all current and former employees named Smith.

3. User switches to the "current employees" layout. (If we used a scripted button with GTRR here we'd get the proper set of people. If we just used the layout menu we'd get whatever found set was last in that layout.)

4. User enters Find mode and types "Smith" in the Last Name field. (Again, not using a scripted find here, just straight from the menu.)

What happens when the user performs this find? Is there any way to prevent non-current employees from showing up in the results besides disallowing menu access and using scripted finds?

In essence, I want to filter *by default* all non-current employees, because rarely is a user of the database going to want to look for them. He should not have to remember to add anything to his find requests to make sure that they are eliminated. As far as I can tell, this is only possible by using scripted finds.

Basically I'm asking whether I'll have to restrict access to the layout menu and other menus and go to a button-driven interface rather than leveraging the built-in functions of the FM application.

[Ender]

Yes, you should restrict access to the menus and use scripted finds and navigation if you want to have this level of control in your solution.

[Peter Lehrack]

Your relationship is backwards. I didn't see a layout for current employees, so I added one. Added button to layout employee which goes to related record show only related records using new layout.

Added button on new layout to go to layout employee.

Can this solution be posted?

Thanks,

Peter

[Peter Lehrack]

I figured out what Ender did, is there no way to make this self join dynamic sans portals? Having to go to related records is problematic at best.

Thanks,

Peter