how to show another field content on a different record based on some condition?

[emtau 0]

so on this record... I want to show another value from another record if that other record meet the requirement. please check picture and I'll explain more...

So the picture shows the record of John Wick... Under Admin Officer I want to put the name of the Admin Officer which is another record on the same table, but the record/user needs to be assigned a job title as Admin Officer (like John Wick job title: Assistant IT Specialist) in order to automatically put his/her name in the provided "Full Name:" spot.

I tried creating a new field calculation field to put there but I'm lost what to calculate... I'm thinking, if user = Admin officer, get his name.. that's what I'm thinking hehe sorry.

Would really appreciate your help on this

Thank you.

[comment 1,743]

If you want to show data from another record in the same table, Filemaker needs to know which record. For this, you need to define a self-join relationship (using another occurrence of the table on the relationship graph). Assuming "Admin Officer" means the same thing as "supervisor", the relationship would look something like:

Employees::AdminOfficerID = AdminOfficer::EmployeeID

where the AdminOfficerID field holds the unique ID of the employee's supervisor. Then just place fields from the AdminOfficer TO on your layout.

 

[emtau 0]

I think I get what you're trying to say... but I'm a bit confused, first of all I never knew about the self-join relationship so thanks for pointing this out.

15 hours ago, comment said:

Assuming "Admin Officer" means the same thing as "supervisor",

Admin officer is "Office Manager" to be exact and it is from a field called "Job Title", this field is also populated with other Employees like IT Specialist, Accountant, Registry Clerks, etc...

So if you look at the layout provided... John Wick is an Assistant IT Specialist (from "Job Title" field) and I want the Admin Officer to be the Office Manager also from the "Job Title" field.. but I don't want the job title to be shown I want the Employee's name.

I've created a simple small sample filemaker file of what I meant which I'm sure you understand already, I'm just a bit confused on how the same field will work on this... if you could please help me out just demonstrate on the sample file how that would work. I hope I will never bother anyone again about self join relationships.

Thank you.

 

selfjoin.fmp12

[comment 1,743]

Can there ever be more than one Office Manager? if not, how do you plan to enforce it?

 

[emtau 0]

29 minutes ago, comment said:

Can there ever be more than one Office Manager? if not, how do you plan to enforce it?

There can only be 1 Office manager at a time... but in time he/she might move to another office or resigns and be replaced by a new Office Manager. We don't want to delete records and so we mark them as ex-officers by putting another date field where he/she left office... this applies to all employees.

[comment 1,743]

I am not sure that's the ideal arrangement to have, but you could define a calculation field that returns some value, say "Office Manager", when the employee' job title is Office Manager AND the termination date field is empty. And another calculation field that returns the text constant  "Office Manager". Then you can locate the current office manager's record using a relationship that matches the two fields.

 

[emtau 0]

9 hours ago, comment said:

I am not sure that's the ideal arrangement to have,

You're right, I've decided to just put it blank, since the layout is gonna be printed anyway he/she (Office Manager) can fill in her name manually and sign it since she can also be temporarily replaced by other supervisors if he/she did not come to work that day.

Thank you for you help and I will also consider your suggestion (calculation etc..) for other requirements I might need.

Very much appreciated.