One record, one calc, four results

[niktemadur]

Hello there. Thanks to an excellent nudge from [color:blue]comment, I've figured out how to make portals work in my database.

However, I'm stuck with a Calculation Field I'm trying to use as an ID#, since some records may occupy up to four values, and I can't get FM to read beyond the first one.

My database has a Table-1 of up to 200 records (employees) and a Table-2 of 71 records (tasks), selected on Table-1 by 5 pairs of Value Lists via Relationships. Not everyone performs tasks from Table-2, and I want to automatically assign them all with an ID# of 0 (zero). Whoever performs a specific task, receives an ID# specific to that task (from 1 to 71). Then comes the portal thingys with a Table-3, but that's another story.

Here's the problem: An employee may perform up to four specific tasks, giving him ID#s of say 32, 35, 36 and 38, yet the only value FM seems to acknowledge is 32. I can't seem to make it work with Repeating Fields nor using Portals with four rows on Table-3. How can I make FM identify all four values, not just the first one?

If this can be accomplished, my database will be one step closer to automatically monitoring some processes that are very painstaking when done by hand. :(

[comment]

An employee may perform up to four specific tasks

Can the same task be assigned to more than one employee?

[niktemadur]

Thank you for answering, and good question, I missed pointing that out in my post. The short answer is NO, each task is performed by only one employee, even as most tasks are repeated in all six areas of the organization by different employees.

Task ID#s in Area One go from 1 to 16, Area Two from 17 to 27, Area Three from 28 to 38, Area Four from 39 to 49, Area Five from 50 to 60 and Area Six from 61 to 71.

For example, tasks with ID#s 11, 22, 33, 44, 55 and 66 are virtually identical, except that they're performed in different areas of the organization. Same with ID#s 12, 23, 34, 45, 56 and 67. And so on.

If I can align ID#s in both Tables to go 1-1, 2-2, 3-3, 4-4 ... 71-71, that perfect symmetry will go a long way in keeping things tidy, as the Task Table stays fixed and the Employee Table is dynamic, due to the Calculation Function ID#.

[comment]

If each task is performed by only one employee, then your relationship is on backwards: you should have ONE EmployeeID field in the Tasks table - not many TaskID fields/values in the Employees table.

I am not sure I follow the business with the areas and task ID#s. IMHO, your primary ID's in any table should be meaningless auto-entered serial numbers.

[niktemadur]

If each task is performed by only one employee, then your relationship is on backwards: you should have ONE EmployeeID field in the Tasks table - not many TaskID fields/values in the Employees table.

Slapping hand on forehead, it dawned on me that I haven't described what my ultimate goal here is:

A graphical display, sort of like a map, of the organization. At a glance, you can see who's doing what and where, including a photograph of the employees, all very visual, which is how my boss prefers it. Whenever an employee quits or retires, the function (or functions) he was performing should now appear as vacant, blank spaces on the organizational map. Just by manually changing employee status from "active" to "inactive" on his/her record, the ID# changes to 0 (zero) and everything else becomes automated, or so I believe.

Why am I using Value Lists on the Employee Table? For easy toggling of task assignment, considering there's 71 of them across six areas! I've got four sets of two fields each with Value Lists, so there's a maximum of four options to choose from when you click on each field. I designed it this way before coming across the current challenge, and my database already contains over 300 records, 200 of them "inactive". So far, I've managed to accomplish everything I've done with all kinds of Scripts and Sorts, but this... (whew)

I am not sure I follow the business with the areas and task ID#s. IMHO, your primary ID's in any table should be meaningless auto-entered serial numbers.

That's the thing, I'd love to take those meaningless auto-entered serial numbers and make them actually meaningful. I've already managed to get the Calc Function to assign an ID# value of 0 (zero) when any employee becomes "inactive", his/her former tasks automatically showing up as "vacant" in the Tasks Table. Likewise, many employees do not perform any of these tasks and would just take up space on the Tasks Table, so on two fronts, many employees with an ID# value of zero very neatly performs the trick I'm trying to achieve.

With a Calc Function, it would be so much tidier to have parallel, symmetrical values on both Tables. But like I said, I haven't been able to make the Calc Field acknowledge multiple results within a single Employee record!

Oh BTW, here's the basic idea of the infamous Calc, a long string of conditional criteria that I've abbreviated here, hope you get the general idea:

[color:gray]Case ( Status = "Active" and Area = "(area1)" and Task = "(task1)" ; 1 ;

Status = "Active" and Area = "(area1)" and Task = "(task2)" ; 2 ;

[color:blue]... (and so on) ...

[color:gray]Status = "Active" and Area = "(area2) and Task = "(task10)" ; 26 ;

[color:blue]... (until, finally) ...

[color:gray]Status = "Active" and Area = "(area6)" and Task = 11 ; 71 ;

0 )

Edited April 17, 2009 by Guest

[comment]

I still think the relationship should be implemented as a standard one-to-many - which means that the task record holds the employee's id, and not the other way around.

How would you handle an employee that remains active, but quits one task out of the 2 assigned to them? It's not impossible to do it your way, but it's unduly complicated, IMHO.

OTOH, retiring an employee needs to be handled by a script that goes to the related tasks and sets their EmployeeID field to empty (you should also set validation so that status cannot be "inactive" while there are related tasks).

I'd love to take those meaningless auto-entered serial numbers and make them actually meaningful.

I don't see why this would be necessary. A task knows it's vacant, because its EmployeeID is empty. Even if you have it the other way around, a task would know it's vacant because the related employee's record status is "inactive".

[niktemadur]

I still think the relationship should be implemented as a standard one-to-many - which means that the task record holds the employee's id, and not the other way around.

Well after fumbling in the dark for a couple of months, thanks to your guidance, [color:blue]comment, I've already implemented a way to do what needed to be done, and it works.

However, I'm trying to achieve a higher level of automation, and it seems to me that I'm implementing what you're saying - create an Employee ID# (by means of Value Lists) to take back to the Tasks Table via Portal. It's the inability for the Tasks Table to acknowledge multiple ID#s for a single Employee performing several tasks that's got me down.

I can see a way to automatically assign a single value to every employee performing tasks via Calc, but it looks to me like a nightmare permutation of 71 times 70 times 69 times 68 and so on, out of the question!

How would you handle an employee that remains active, but quits one task out of the 2 assigned to them?

Not difficult at all, the way I see it, a two-step process:

1. Go to the still active employee record, toggle the Value List from his task to "no task", and thanks to my infamous Calc, that particular ID# goes to 0 (zero).

2. Go to another employee record, toggle the Value List to assign him the former's task, and his ID# now reflects his new task.

I find it very easy and intuitive to deal with and modify only records in the Employee Table, letting the rest do itself via as much automation as I can possibly achieve.

Still, there's the matter of using four sets of identical Value Lists in four sets of two Fields, not terribly elegant, possibly the source of all this weirdness. Another way may be to integrate a Task Value Lists in Portal in the Employees Table, but I can't make it repeat and handle different values in the second, third and fourth row.

OTOH, retiring an employee needs to be handled by a script that goes to the related tasks and sets their EmployeeID field to empty (you should also set validation so that status cannot be "inactive" while there are related tasks).

That sounds exactly right, I haven't yet gotten that far, if I have to do this without my infamous Calc ID# thingy.

It's not impossible to do it your way, but it's unduly complicated, IMHO.

That's the weird thing, I visualize it being more complicated the other way around, once the system is up and running.

Maybe I'm playing with some quirky boundaries here. When I was a straight A student in math and computing, my teachers repeatedly commented that nobody could cheat with my papers or exams, as there's a peculiar yet logical method to my madness, my imprint would immediately be detected in any cheaters. The teachers even went as far as flunking me, until I appealed and made them understand my logic process, changing the grade to A+. Go figure. :laugh2:

[comment]

thanks to your guidance, comment, I've already implemented a way to do what needed to be done

This is the second time you mention this, but I have no recollection of providing a "guidance" that would lead to storing child ID's in the parent.

I can see a way to automatically assign a single value to every employee performing tasks via Calc, but it looks to me like a nightmare permutation of 71 times 70 times 69 times 68 and so on, out of the question!

I have no idea what you mean by that. If there are 71 tasks and 200 employees, then a user needs to select one employee out of 200, and do this (up to) 71 times - and that's all there is to it, IMHO.

In any case, I am not out to convince you.

[niktemadur]

The task record holds the employee's id, and not the other way around.

Things just went "click!" last night. Doh. Sorry for being dense and my slow learning curve. You were right, I was going about it backwards. Now everything is coming out exactly as it should in the Tasks Table, where I'm using the Calc to sort records in the specific order I need (always the same).

Thank you for your patience and apologies if I exasperated you.