Portals and relationships -- ugh!

[boston]

So I'm trying to create a skills inventory database for use at our non-profit in boston. I have four tables:

skills

skill_type (used to filter skill types based on skill area)

proficiency

user

the skill table has the following fields:

skill_id

skill_area

skill_name

skill_type

i have included screen shots of my entity-relationship graph and hope someone can help out!

i am trying to create a portal on the user layout to show all related proficiency records based on a specific skill_area and skill_type that is entered but it isn't showing anything related properly. actually i'm not sure how it is figuring out which records to show and which to not show.

thanks!

[Fenton]

This is a little tricky, even trickier to try and explain in words. The short answer is that you must target the Proficiency table, because it has the emp_id. You can target it fairly directly. But it will need to have either the skill_area and skill_type as local stored fields. Or you will need Skill_Areas and Skill_Types tables, with auto-enter serial IDs, and pull those in locally to Proficiency.

I started to answer this, using the "non id" method for area and type (below). This would work fine UNLESS you changed the wording for an area or type later. You might want to post a stripped-down example file, so we could implement this; a file is worth 1000 words.

Method 1 (somewhat clunky, as it uses text fields for area and type):

First, since you want to use this to filter a portal, the table occurrences (TO, boxes on the Relationship Graph), must be attached to the "user" TO, which is the TO of the layout where the portal appears. The way you did it, as a separate disconnected table occurrence group (TOG) would work for a filtered Value List, but not for a portal.

Second, filters using global fields are kind of funny. They attach more directly, you go more directly to the target. In this case you would attach both global fields at once in a relationship from the User TO.

kp_emp_id=filtered_proficiency::kf_emp_id

AND

g_skill_area=filtered_proficiency::skill_area

AND

g_Skill_type=filtered_proficiency::skill_type

You'd attach directly to "users". You don't need all those TOs in between. We can't see the full name of the last TO on the right is, but that's the one you'd use. It must have the employee_id as a foreign key (since that's what you want to see, for this employee).

You cannot use your c_skill_area or c_skill_type, which are likely relational calculations (therefore unstored fields) as targets of the relationship. You must auto-enter by Look Up (or auto-enter calculation) the real skill_are and skill_type fields into Proficiency.

[boston]

Fenton -- Thanks for the prompt reply. In hopes that you can help me out fully here, here's the stripped down DB. Once this is complete, I'm trying to make it searchable by skill proficiency level, etc.

skills_inventory_in_progress.zip

[Fenton]

Here is my take on it. I included a filtered portal AND an "all user proficiencies" portal.

skills_inventory_fej.fp7.zip

Edited August 6, 2008 by Guest

[boston]

Fenton --

How would you create the user-input screen? Would you have a script create blank proficiency records for each user upon user account creation?

Also, how would you handle the user interaction -- have it go to their record upon login.

Thanks!

[Fenton]

The portal has [x] Allow creation of related records. It creates the Proficiency record for the employee_id|skill_id|skill_area|skill_type when you choose the skill_id in the portal.

I don't know where they'd go on startup. Are they entering their own Proficiencies? If so, yeah, they'd go to their own record, by Get ( AccountName). They'd need their own Account. You may want to restrict Record-level Edit access privileges of their common Privilege Set to, Get ( AccountName ) = Users::UserName

[boston]

Fenton --

This is really helpful! I'm now off and running on this stuff, but have run into a little bit of a hole, trying to figure out how to "search" for users' proficiencies. So I'm trying to browse by Skill and choose a proficiency, but it doesn't seem to be working just right.

I have created a new global variable "g_skill" in the user table and created a new layout that is like the proficiencies layout. however, i cannot seem to show all users relating to a particular skill/proficiency level.

any idea how to do it?

[Fenton]

You just need another relationship like the existing with the 2 globals; but this one does not have the Employee ID. So it will show all Proficiency records matching the globals. Add another couple TOs, to show the names of the Employees and the names of the Skills.

Because the first relationship is entirely originating from global fields, this whole setup could be moved to any table with 2 globals like those. It does not have to originate from the Users table. Or, you could just put it on a layout of its own, still assigned to the User table. I don't know if the fields should be enterable; I made them not.

skills_inventory_fej_2.fp7.zip

[boston]

So I get what you're saying, but how can I make it a little more elegant... This is as far as I've taken it. (because there are a LOT of skills underneath each skill directory)