Select from drop-down listing calc results

[btmeacham]

I'm trying to come up with a simple way to add people to projects. Since a Person can be related to many Projects, and a Project can have many people connected to it, I've created a Role table to avoid the many-to-many problem.

 

On the Project layout, I have a portal bringing in the Role data, making a nice list of all of the people related to that Project, and a button triggering a script to add a new role. The table I'm using to catalog the people has fields for First Name, Last Name, a unique identifying number, as well as a calculation field that joins First Name and Last Name. The portal in the Project layout displays the unique identifier and the First Name Last Name calculation field in a drop-down list.

 

This is where I'm running into a couple of problems. I'd like to be able to choose the person I want to add to a project by typing a few letters of the name into the drop-down list (it has about 20,000 names in it, so this seems like the best way to narrow it down quickly) and then selecting the correct one. I'm finding two issues here: first, I can't select from the drop-down list until I've entered a number into the unique identifier field. I have a dummy record set up with no data in the name fields, so it looks like a blank new row, and I have the script that creates a new record paste the unique identifier for that blank record in, so the user can then select from the drop-down menu. Just typing it here makes me realize how inelegant this is, and it probably means I'm doing something else wrong along the way. I'm never going to add a Person by their unique identifier, since I'll have no idea what it is, but I can't figure out any other way to get the drop-down list to show up without already having a number in the Unique ID field.

 

The second problem has to do with the drop-down list: since it is displaying the result of a calculation field, when I choose a person's name from the list, I get the "This action cannot be performed because this field is not modifiable" error. At least I presume that's why I'm getting the error... I'm not trying to modify the field, but just want to select a Person in it, have the corresponding unique identifier be populated into the Unique ID field, and create an association through the Role table between this Person and this Project.

 

I'd love to hear any suggestions as to how I might get around these problems. Thanks.

[eos]

While your idea of using a join table is laudable, it seems like you're trying to do everything within the three initial TOs (one per table) of your database.

 

The second problem you describe sounds like it's caused by your not trying to set the foreign key field in the join record to display the full name (and store the ID), but instead “looking through” into the actual Person record and working on its fields. So there's something definitely not right with your structure! Read up on what a relationship actually is and does, and why you can and should use multiple occurrences of the same base table for different purposes.

 

Forget about using a popup menu for choosing from 20,000 entries! The solution is: don't create a blank join record to fill with a Person ID, then choose the person, but do it the other way round: select the name to create the entry with, by utilizing a selection portal.

 

First, create a new global text field in your Projects table. Then, create a new TO for Persons, and connect it to your Projects TO with a Cartesian relationship (the x operator, using an arbitrary field on both sides). Put a portal for this new TO on your data entry layout, place the new global field next to it. Now filter the new portal with the new field, using an expression like

 

Case ( isEmpty ( myFilterField ) ; 1 ; PatternCount ( peopleTO::cFullNameField ; myGlobalSearchField ) )

 

This is for filtering down an initially full list; to build up an initially empty list, don't use the entire Case construct, but just the PatternCount() expression.

 

Now put your button into the portal row, and alter your script to capture the ID of the clicked person and use it to set the foreign personID field when creating a new join record.

You have a foreign personID field in your join table, right?

 

Here's a quick and dirty example of portal filtering; despite what the filename says, not done by me.

filter_eos.fp7.zip

[btmeacham]

Thanks so much for your response. I'm going to dig into this now and see how it goes.

[btmeacham]

So, I've made a little progress, but have a few more questions. I really appreciate your help that has gotten me this far.

 

Taking the example database you attached as a model, I was able to create the portal listing the records in the Person table and filter it with a new field. I'm having trouble figuring out why it didn't work connecting the Person TO with the Projects TO; when I couldn't get that filter to work, I followed the model in the sample you sent, and made two occurrences of the same Person table and connected them with a Cartesian relationship. Connecting it to the Project table only displayed people related with that Project in the portal list, rather than displaying all People and giving me the choice of who to add to that Project.

 

So, my name filtering works, but it only works in the context of the Person table, when I need it to work in the context of the Role and thus the Project. Rather than have the Person portal show up on each Project record for a user to filter and select from, the user would view the record for a Project and would click a button to add a new Person to that Project. They'd be taken to a new Role record, with the Project number captured and populated, and then they'd filter the list in the portal to arrive at the chosen person. They'd click the name, which would complete that Role record and return the user to the Project table where the new person would be seen in the list of those associated with that Project (at least that's how I see it working, to avoid always having a portal list of people present on the Project layout, whether or not the user is interested in adding any more people to the Project. I'm open to other, more logical or efficient suggestions).

At this point, my relationship graph is as follows: a TO of my Person table, connected via a Cartesian relationship to another TO of the Person table, allowing filtering of the names in the Person table. The PersonID field in the Person TO is then connected to the foreign key field for PersonID in the Role table, and the ProjectID foreign key field in the Role table is connected to the ProjectID field in the Project table. I'm trying to figure out why the Person list and filtering only works in the context of the Person table, though, and not the Role table, where I think it needs to live. I have a feeling it's obvious, but it's not obvious to me... Thanks again.

[btmeacham]

I think my observation that the filtering was only working in one context and not another was a misinterpretation of what was going on. Everything seems to be working now. Thanks again.