Reflecting data from one record into another, dynamically.

[niktemadur]

Posting here tentatively, as the topic may belong in Relationships or Portals, here goes:

Two tables joined by SerialID Number. Table One focuses on Employees By Name, Table Two on Tasks (Manager, Shift Supervisor, Shift Employee, etc).

Employees and Supervisors change shifts from time to time.

Here's the question: In Table One, is there any way to format a field (or portal) that will automatically show who's his/her latest Shift Supervisor?

In Table One, every record has a portal with value lists that modifies the records in Table Two, which has a Calc Field that spits out a number according to shift and duties.

Let's say in Table Two, due to his shift and duties, John Smith had a value of 4, his supervisor had a value of 3.

Can I make a field in John Smith's record in Table One that does "If Calc Field = 4, Show Name of guy with Calc Field = 3".

Now using the portal with value lists in Table One, John Smith has changed to a value of 6, his new supervisor is the guy with a value of 5.

Therefore now for John Smith it would be "If Calc Field = 6, Show Name of guy with Calc Field = 5".

Can this be done?

Hope I was succinct in this post, sometimes I tend to torment myself in circles when it comes to Filemaker!

Thank you in advance.

[comment]

I have a feeling you are making this more complicated than it needs to be. But perhaps I don't understand the entire issue.

It seems like you should have a table of Shifts, so that if John Smith is assigned as an employee to shift X, his shift supervisor is whoever is assigned to the same shift in the role of a supervisor.

I am also not sure why an employee can have multiple tasks.

[niktemadur]

Thanks for answering! Here's the thing, tackling one paragraph at a time.

The layout I'm trying to do is for a printable document, sort of like a report card for the employee, so it's gotta have his name and the supervisor's.

I DO have a table of shifts, I knew I'd screw up my explanation, sorry about that! Table Two organizes company personnel by BOTH shift and task.

Finally, the employee does not perform multiple tasks, he just switches shifts from time to time.

[comment]

the employee does not perform multiple tasks, he just switches shifts from time to time.

Thanks for clarifying. So do you keep a history of employee's assignments? If not, then I am not sure what exactly your "Table Two" does - it seems each employee would have only one related record in it?

[niktemadur]

Thanks for clarifying. So do you keep a history of employee's assignments? If not, then I am not sure what exactly your "Table Two" does - it seems each employee would have only one related record in it?

Table Two's original purpose is as a bridge to a third Table and Layout where, through portals, all employees are organized by department and shift, a panoramic view of all the company, taking from Table One a few fields like Employee Picture and Employee Name.

I neglected to mention this because it works seamlessly (thanks to you a couple of years ago, comment) within a database that has a ton and a half of Calcs and Scripts and Layouts, it was my intention to isolate the current issue to keep things relatively simple, hopefully it wasn't relevant info.

There's wheels within wheels within wheels in my clumsy but charming 54 Megabyte baby!

Let me show you what Table Three looks like, a quick, very lo-res screenshot with names blurred and other sensitive info cropped out. But like I said, it works beautifully, dynamically.

When you click on the yellow button below the picture, you are taken to the full Employee Info layout from Table One.

[comment]

Table Two's original purpose is as a bridge to a third Table and Layout where, through portals, all employees are organized by department and shift

I am afraid that doesn't mean anything to me. Please describe your tables by what a record represents in real life (for example, in the Employees table each record is a unique employee) and their relationships by cardinality (for example, one shift can have many employees but each employee has one shift only).

[niktemadur]

Right-o.

Table One - each record is an employee. Table Two - each record is a work-post, with three defining fields: Department, Shift and Function.

Both Tables are joined in relationship by a self-generating numeric ID field.

Table One contains a portal with three fields as value lists from Table Two: Department, Shift and Function. By filling these three fields in a new record in Table One, a new record is automatically generated in Table Two.

Table Two has a calc field where a number is assigned to each combination, say

Case ( Status = "Active" and Department = "A" and Shift = "A" and Function = "A" ; 1 ; Status = "Active" and Department = "A" and Shift = "A" and Function = "B" ; 2 ; ... ; Status = "Active" and Department = "C" and Shift = "C" and Function = "C" ; 9 ; 0 )

Table Three has one layout and only one record, populated by portals. Each portal has only two fields (from Table One): Employee Name and Employee Picture. Portals are sorted according to the calc field in Table Two. Tables Two and Three are joined in relationship by a numeric ID field, where on Table Two a calc spits out only two numbers: "1" if the employee is currently working at the company, "0" if he/she has resigned. The ID field in the only record in Table Three has the value of "1".

Hope that was more focused, useful answer.

[comment]

I believe you should do this with relationships instead of calculation. If you do a self-join of the WorkPosts table, matching on Shift and Department, you can then filter the relationship (or the portal) by Function.

[niktemadur]

Self join! Having never tried it, standard procedure - give it a go and in case of a snag, comb FM Forums. I've spent much more time here than my profile suggests, I simply don't notice the browser logged off until trying to post.

Thank you!