Auto-Lookup or Calculation?

[AlanP]

Hi everyone,

I'd like the supervisor field in our main layout/table ("2005 Guide Distribution 2" table) to show the supervisor of an employee based upon the employee's section which is listed in a seperate table ("Employment History" table).

Supervisor information (section and supervisor name) is located in a seperate table as well ("Supervisor" table).

I've attached an image showing current relationships and tables. Any code, calculations, relationship advice, etc. would be very helpful. This is the last part of the database and I just can't get it figured out!

[Dr. Evil]

Can you clear this up for me?..

From Guide table you want to see...

assigned supervisor

employees under assigned supervisor

:

If you like you can .zip an empty clone of your file and send it to me. I think I can help you with this.

ttyl

Edited August 17, 2005 by Guest

[AlanP]

Hi Dr Evil,

I'll send an image/file tommorow (I'm at home now) but here is what I'd like:

On a layout in the 'guide' table I'd like to see who the employee's supervisor is. It would base it's decision on who the employee's supervisor by looking at the most recent (last record) section the employee is in (in the 'assignment' table) then it would match that up with the supervisor information in the 'supervisor' table.

For example: The employee is in the "Research" section ('assignment' table). The calculation/etc would look in the 'supervisor' table to find the "Research" section record and see the supervisor is "John Doe". It would then populate the 'supervisor' field in the main layout ('guide' table).

Wow...hope that makes sense! Thanks for the help!

[Dr. Evil]

Hello

Here is my best "attempt" of understanding how your data should relate. Let me know if this model would work for all possibilities and needs in your solution.

attached is a .zip model file.

guide.fp7.zip

[AlanP]

Thanks Dr Evil!

I'm attaching what the main layout looks like that shows the bureau & section (this is in the main layout 'guide' and uses calculations that shows the last posted section/bureau from the 'assignment' table).

It *looks* like in the database you posted (looked really good!) that you have to specify the supervisor to the employee? Maybe I'm wrong. I just need it to populate the field with the supervisor based on the section for the employee in the 'assignment' table.

For example in the first image the bureau and section are a calculation

last(employment history::section)

that shows the last bureau and section the employee is in from the 'assignment' table. I'm wondering if there is a way to do this with the supervisor...the only difference is we have one record we will update based on the section (the section is the main records in the 'supervisor' table). So if it says "research" section it will look for 'research' section in the 'supervisor' table and return the name of the supervisor in that record.

hope that makes sense!

[AlanP]

Attached is an example database just showing the fields I am talking about...hopefully this will help better explain what I'm working with!

Thanks!

sample.zip

[comment]

See if the attached helps.

sample.fp7.zip

[AlanP]

wow thanks comment! I'm looking at it right now (and will try and figure this out) but let me ask you this:

We only need one 'section' record in the supervisor table and we will just change the supervisor when we are notified (so if Supervisor Bob leaves 'section 1' and Supervisor John replaces him, we will just update the 'section 1' record in the supervisor table). So we would like the most current supervisor to always be listed on the employees record when the supervisor changes.

Does this change dramatically how the database you created worked?

Also is there a way to show the most current supervisor for the section outside of the portal? (for example the 'personnel' page you removed the section and supervisor fields...is there a way to display that?).

Thank you so much for your help!!

[comment]

If you're sure you don't need a history of supervisors' assignments, then you can do with one table less.

Note the sorting on the self-join of Assignments - that is the key to the whole thing.

sample2.fp7.zip

[AlanP]

I think that is it!

I really appreciate the help and sample DB! How the heck do you guys get so smart like this??

[comment]

Actually, this one was plain hard work. I thought it could be significantly simpler, but I ran into serious refreshing issues trying to get the latest assignment into People and base a relationship directly from there.

Of course this all starts with a bug; if FMP would respect the sort order of the child relationship when referring to grandchildren, this would be a piece of cake.

[AlanP]

Everyone here at the office thanks you big time for your help...you have no idea how much this will help us!

Can you recommend a book or website to learn more about relationships?

[comment]

Ehm.. just be aware that's just a sketch. That means I hope it works, but I haven't tested it, and I haven't had time yet to think about it again. It's an interesting problem, actually, and I plan to re-examine it, when I'm more fresh. I'd appreciate it if you post any problems you run into.

I cannot speak about books from personal experience. As for websites, this is probably the best one. Check out also http://www.filemakermagazine.com/ - some of the free videos there are quite good.

[comment]

OK, here's a MUCH simpler approach. All it takes is another instance of the same portal. Note the portal sort orders.

showBossSimpler.fp7.zip

[Dr. Evil]

WOW! Miss a day, miss a lot!

I was having troubles seeing what the existing relationships where in your database and how you the creation/relation was set up.

I am happy to hear you have a solution to your problem!

GREAT job Comment! I love the simple solution!

[AlanP]

cool! that relationship is a lot simpler! I actually implimented your first design and it works great (the only thing that happens is if an employee moves to an area where a supervisor is NOT listed, the last supervisor is displayed in the main layout).

In the simpler layout I see you have the 'from date'...if I leave that blank it doesn't sort descending....if we have the from date field to be an creation date would that work?

[comment]

the only thing that happens is if an employee moves to an area where a supervisor is NOT listed, the last supervisor is displayed in the main layout

That is strange... it doesn't happen in my test file, nor in the uploaded sample. Check your implementation again?

if I leave that blank it doesn't sort descending.

Oh, it sorts OK, but an empty date is considered smaller than any date. You shouldn't allow this to be empty. You can re-apply auto-entering creation date - I have removed it for testing with various dates.

[AlanP]

Hi everyone, I need to ask a followup question on this relationship:

Is there a way to include the BUREAU of assignment along with the section? Right now it's relating to the section only, but because some sections are named the same I was thinking this might lock it down more.

Do I just include the BUREAU field in the relationship?

I should mention I'm using the original version that comment created and not the simpler version he attached later on.

Much thanks

Edited May 10, 2006 by Guest

[comment]

I am not too clear on the hierarchy between Section and Bureau. The best thing would be to add a unique SectionID field (auto entered serial number), and base the relationships on that, rather than on the section name.