Link Confusion

[faaslave]

I currently have a solution where tables are linked as such:

Area

Manager (belongs to an area) 6 Areas

Supervisor (belongs to an manager) 

Employee (belongs to a supervisor)

Certain files belong to an employee, such as notes and evaluations.

The same thing is true for supervisors

Now the problem.

Lets say the Chief goes to the Managers and says, I want supervisor Doug to work in Area B now instead of Area F.

Well thats easy enough, I go to Dougs record on the supervisor layout and select area B. All of Dougs files should go with him. Thats not going to work. Unfortunately the way I have it set up, the employees under Doug are going to go with him, which isn't the case.

So I need a relationship where, Everyone belongs to an area, and employees work for sups, sups for managers, and a manager is assigned to run an area.

There has to be an easy way to switch an employee, or sup, to another area.

I attached a copy of my file to look at.

Thanks Dave

SupTracking30.fp7.zip

[Ender]

If you want the Employees to be linked to the Area they work in (rather than the Supervisor), then assign them to an Area (or a Group, where a Group is assigned to an Area). Then have the Supervisor be in charge of a Group rather than the Employees.

[faaslave]

Thanks for your reply,

One problem is that there are seven crews in each area. One crew for each set of days off. While it would be great to have a supervisor for each crew, we usually have about 5 supervisors for seven crews.

So the supervisor on Mon/Tue off might have his crew, plus three people on Tue/Wed off. Then the supervisor on Wed/Thu off has his crew, and the rest of the people on Tue/Wed off.

So each employee record needs to have the capability to be assigned to any supervisor.

If I am not being specific enough, I will explain in detail.

Thanks Dave

[Ender]

I think that structure captures your example. The Mon/Tue Supervisor is assigned to the Mon/Tue Group and the Tue/Wed A Group. The Wed/Thu Supervisor is assigned to the Wed/Thu Group and the Tue/Wed B Group.

Edited August 29, 2006 by Guest

[faaslave]

sat/sun  sun/mon  mon/tue   tue/wed   wed/thu   thu/fri  fri/sat

Dave       Steve      -empty-        Paul        Ringo        John   empty-

emp

emp

etc.

So there is no supervisor on mon/tue and fri/sat.

Half the employees on on mon/tue belong to Steve and half to Paul.

Same thing for fri/sat. half to Dave and half to John.

So I can't base my relationship on the daysoff or crew.

I need to be able to pick the sup for the employee, and I need to be able to move someone from one area to another, then re-assign them a supervisor.

If Dave moves to Area A, then all of his employees would show -missing- for a supervisor. Then I would re-asign them to someone else.

And if I move Dave to another area, I want all of Dave's notes and evals that were writtem on him , to stay connected to him.

I just want to make sure I am being clear about the problem.

Thanks Dave

[Ender]

I still don't see the trouble. In my suggested structure, a Supervisor can supervise many Groups, an Area can have many Groups, and each Group can have many Employees. But a Group is not the same as a Crew. In this case a Group is a Group of Employees for supervisory purposes (the Employees that are Supervised by a particular Supervisor on a particular day, or whatever).

[faaslave]

I looked at your graphic. I guess I don't understand how that will work. The employees belong to a sup, the sup to an OM, and an OM runs an area. I can get all of that to work. The problem is when I move someone to a new area, I only want their records to go with them, and not their relationship to others.

Under your example, are you saying, make everyone belong to an area instead? And if so, how to I make the relationship work between employee, sup, and OM?

Sorry, maybe I am just not seeing what you are saying.

Thanks Dave

[faaslave]

To make my DB work correctly I need to have the employees linked to the supervisors, and the supervisors to the OM's. Can all those links still work with the group angle?

I had it all working, the only problem was when I wanted to move one employee or supervisor to a new area.

Under your way, I would just assign the employee to a new group. Then I would have a supervisor be assigned to run a group? The OM's run an area, so that one is not as difficult, I would think.

I am very confused

Thanks Dave

[bruceR]

An exact example of that setup is provided in the book "Advanced FileMaker Pro 5.5 Techniques for Developers" by Moyer/Bowers

[faaslave]

I don't have the book Bruce mentions, but I think I got it to work using Enders logic. I am not sure its the best way, but it sort of works. Thanks Ender

I do have another question.

I have a field where the crew number goes. There are 42 crew numbers.

1-7 are in Area A

8-14 are in Area B, and so on

When a user clicks in the crew field it pulls down a choice of crews. I have a value list that has the crew numbers.

Is there a way to make this a calc field that would do the following. If Area = A then use this value list. and so on?

I would have six different value lists , and based on what the area is selected in the Area field, it would give the applicable selections?

Thanks Dave

SupTracking31.fp7.zip

Edited September 11, 2006 by Guest

[Ender]

This sounds like a good place for a conditional value list.

[Genx]

: Ender, always beating me to things.

Ohwell, here are two links to two threads discussing the topic, there's sample files on both, just use whichever is easier to follow.

http://www.fmforums.com/forum/showtopic.php?tid/177787

http://www.fmforums.com/forum/showtopic.php?tid/177731

OR EVEN BETTER YET:

http://fmforums.com/forum/showtopic.php?tid/177620/

Edited September 11, 2006 by Guest

[faaslave]

I did try using a conditional value list. As you can see by the Red Fields on the employee layout.

The problem is that this would require a user to pick an area, and pick it again in the red field, so that the crew filed will function as desired.

What I want is a conditional value list based on the field that I am already using. That field is fk_area_ID.

Since that field is used in the structure of making everything connect correctly, i.e. employee records show up for the correct sup, sup records are tied to the correct area, I can't change that.

Isn't there a way to say, if field (sample) = "whatever" then use this value list, and so on?

If not, is there a way to filter the value list?

if field (sample) = "whatever" only show "36" "37" and so on. The value list would have all the variables, then based on what the other field had in it, it would just filter the choices available?

I attached the latest file, since there is no better way to explain it.

Thanks Dave

suptracking31b.zip

[Genx]

Well no, not exactly, you would have to build it over a relationship. You would only require one additional table:

Value List Table

-PrimaryIdentifier

-Entry

Just trust me conditional / filtered value lists are what you are after. Have a quick look at the links i posted above.

[faaslave]

I did look at them. And if you look at the file I attached you will see that I applied that technique.

Employee Layout

There is a field named crew which has the value list.

The other field in question is Area. This field is based on fk_area_ID. But I can't figure out how to incorporate that field into this technique.

If you could look at my latest file I attached, you will see the field I had to use, and the extra table I made for this.

But I want it so that the user does not have to pick what area they are in twice.

Thanks Dave

[faaslave]

I guess what I am looking for is not possible. Since the field I am using for the condition, can't be used for this technique. I would think that this would be a common need. Maybe in the next version of Filemaker'

Dave

[Ender]

Since the field I am using for the condition, can't be used for this technique.

Why not?

And if you look at the file I attached you will see that I applied that technique.

Employee Layout

There is a field named crew which has the value list.

The other field in question is Area.

I'm afraid your file wasn't any help. I don't see an Area field on the Employee layout. If "Area" is supposed to be the conditional value list, it should be showing related values instead of All Values.

[faaslave]

The red field I used is called area_crew.

The new table used for this is Area_Crew.

If I select Area B in the area_crew field, the crew field (under the stats tab) works just like I want.

The problem is the field fk_Area_ID , is the field already being used on this layout to pick an employees area. It is also the field used to link the employees to the area table via a relationship.

So what I did to field area_crew can not be done to field fk_Area_ID.

What I want is to use field fk_Area_ID and not have an extra field (area_crew) on the layout.

I hope I am clear, and I appreciate everyone trying to help.

Dave

[Ender]

I don't know what to tell you. Try as I might, I can't seem to locate a field called "area_crew" or even an "Area_Crew" table within the file you posted.

With conditional value lists, the proper context and relationships are essential for making things work. Without a clear description of your context and relationships, all we have to go on is the file you posted.

Now maybe it's gremlins getting into the forums and changing your file so it no longer has what you say it does, but I think it's more likely that you've been changing your copy and not telling us.

:qwery:

[faaslave]

Lets try it again

Here it is

suptracking31b.zip

[faaslave]

Genx solved this one. When I have another question I will start a new thread.

Thanks to All!

Dave

p.s. I don't see an option to select for closing a thread.

[Lee Smith]

There isn't an option to do that, all you need to do is to Reply to your Original Post, like you did, that you got an answer that helped. The thread will just lie there, ready for others needing answers to the same problem.

Lee

PS, If your new question is a continuation to this same problem, just add a Reply to this thread, it isn't necessary to create new Topics unless the Topic is truly New. A thread will often take a few twist and turns before the poster receives an answer that helps. Also, that way, all of the pertinent history to your problem is all in the same place, and not scattered around the Forum.

[faaslave]

Excellent, and Ender you were right as well. I just couldn't understand that where I had my relationship tied to was incorrect.

Dave