Calculated field that prefixes each value from another checkbox field

[RWX]

I'm trying to create a filter field that works as follows. The relationship is between a User record and multiple Project records. Each Project record contains a text field listing all users and their respective project roles. When a user is added to or deleted from a Project record, a script is triggered to update this field. Every project user name is listed followed by the user name, a dash, and the project role assigned. So the field might look like this:

John Smith

John Smith - Sales

Jane Doe

Jane Doe - Technical Support

On the User record side, I am creating a "dashboard" interface for each user that will display a bunch of different information on several tabs. No editing of related data happens here. Display only. Buttons link to various records (ie: open the Project record or a Trouble ticket).

There is a UserName field in the User record. There is also a gProjectRole field, defined as a global, using a value list of Project Roles, displayed as a checkbox set. Finally, there is my global calculated filter field, gcProjectRole, that uses both of these fields to generate the filter.

If no gProjectRole checkbox is clicked, the value of gcProjectRole is the UserName. If a project role is clicked, the value is the UserName followed by a dash and the checkbox selection. This works fine if only one project role is selected. However, it does not work if multiple roles are selected. In that case, I want to see the UserName - Project Role format repeated for each checkbox that is clicked. I used a simple Case statement which yielded the following result:

UserName - Project Role 1

Project Role 2

Project Role 3

Project Role 4

So it worked for the first checkbox selection, but didn't prefix the UserName on the other selections. I didn't really expect it to work, but I'm sure there's a way to achieve what I want. I just don't know how. I'd appreciate any suggestions or references. I can always use a script trigger to generate the field every time the user selects/unselects a checkbox, but this seemed like the kind of problem that could be handled by a function?

Thanks for your help!

[mr_vodka]

I would suggest you change your structure. There should be an additional table here that stores the Project ID, User ID, and role.

[RWX]

Thanks for the reply John. That is my structure. Thanks for pointing out the obvious. I was coming at this all wrong. It's been many years since I last built a Filemaker database and I'm beginning to realize just how rusty I am! Anyway, it's working like a charm now. Thanks again.

Best,

Rob

[RWX]

Ok, I take that last post back. It's not working like a charm. It works great as long as the only filter criteria is the Project Role. I created a relationship between my User record's UserName field and ProjectRoleFilter field and my ProjectRoleJoin table's UserName and ProjectRole fields. I then created a relationship between the ProjectRoleJoin table and my Projects table.

The trouble is, I can't filter on things like Project Status or the Manufacturer list, which are both stored in the Project record. So I go back to my original idea, which is to create a relationship between my User record and the Project record. Project Roles will still be stored in a join file. A script is triggered every time a project role is created, modified, or deleted in a project record that updates a filter field in the project record.

My only issue is creating the filter field on the User side. In short, I want to take the contents of one field (the project role checkboxes) and create a second calculated field that adds the UserName each line of text.

Let's assume the UserName is "JohnSmith". JohnSmith has clicked the following project roles in the filter field (he wants to see a list of projects where he plays one of the following roles):

Planner

Designer

Sales

The calculated field adds his UserName, so it looks like:

JohnSmith-Planner

JohnSmith-Designer

JohnSmith-Sales

I can make it work via scripting, but that's clunky. Another option would be individual checkbox filter fields for each project role, but that's ugly. And it means that adding or removing a role from the value list will result in having to make edits to the database layout and structure. I figure there has to be a way to make this work?

Thanks!

[eos]

Like mr_vodka suggested, use - in addition to a Projects table and a User table - a Role table and a ProjectUserRole join table with foreign keys for Project, User and Role,

instead of a value list and text fields for roles and calculated text values.

This lets you set up selection portals - by “looking” from the user perspective through ProjectUserRole into Projects and Roles - to display only those Roles and Projects that apply to a given user.

Selecting one or more roles or projects sets/resets global fields which control another, filtered relationship into ProjectUserRole.

This is “basically” the same approach, but IDs are much more robust than calculated text, portals look nicer than check boxes and are more versatile than pop-ups,

conditional formatting makes it easy to highlight the current selection, in short, it makes a prettier dashboard and is more flexible.

[RWX]

Thanks eos. I understand that. And that is how things are set up for defining project roles. However, for the purpose of this filtered interface, I need to filter more than just project role and user. I also need to filter project status, customer, and the manufacturer(s) involved in each project. Since none of that information resides in the join file, I can't access it. I therefore need to build the filter directly between the user record and project record. Everything works great except for the user filter. If someone can help me do what I'm asking in my question, the whole thing will function as I intend.

[RWX]

Someone helped me out on another forum. Super easy solution using Substitute. Feeling rusty. Should have figured this one out myself! Thanks to both of you for your advice!

[mr_vodka]

I wouldn't sacrifice structure for this. This is a many to many situation as a person can be on many projects and a project can have many people. Why couldn't this just be a simple find? Or you have FMP12, use ExecuteSQL.

[eos]

John is right, you should either use his suggestions, or to accommodate the method I suggested, you can simply define a field for project status in the ProjectRoleUser table. Perform a triggered update whenever the status in the project parent record changes. Same goes for other data from Projects or Users you may want to filter on.

Think of it this way: should you ever need to change the title/name/description of a role or a user name, and be it just to fix a typo, you'd have to update this change in all project records to have working user-role key pairs (to say nothing of valid data). This would require the same effort to maintain a structurally unsound system.

[RWX]

Thank you both for your input. I feel that maybe I'm not explaining myself well? My structure is just as you suspect. There is a Users table, a Projects table, and a ProjectRoles table joining them. If I change a User Name in the Users table, the User Name will be updated for all Project Roles.

My issue is simply filtering. To answer John's question, the client wants a single window "dashboard" style interface with multiple portals that pull data from different aspects of the project. Filter down your project list, click on a project, and see relevant data appear in the "dashboard" portals. A find won't work. Since all of the filterable project data (ie: status, manufacturer list, customer) resides in the project record, building a filter field for the user and project role seemed the best approach. I'm open to hearing other approaches, but I don't really understand what you both think I ought to do differently to accomplish my goal. And like I said, maybe I haven't explained myself well?

I'm using a triggered script to update the UserNameProjectRole filter key in the project record each time a project role is added/deleted/modified. The only issue I can really see is changing the User Name in the Users table. In that case, I would need to execute a script that runs through all of that user's projects and executes the script that updates the filter key. Seems simple enough. From a structural standpoint, I'm using a join table. Anyone who performs a Find in the projects table will be able to search the join table as expected. The filter key will only be used for one view, the dashboard, and will never be used for reporting, searching, or anything else.

Thanks again to you both for your input and help.

[mr_vodka]

Perhaps something like this?

project_filter.zip