portal and join file (value list display problem)

[mbeall]

This really has to be simple to do, I'm missing something....

I have two files: Organizations and Individuals and a join file OrgInd. In the Organizations file each organization has a serial number (OrgSN) and in the Individuals file each person has a serial number (IndSN). So the join file just is a list of OrgSn and IndSN.

There is a portal in the Organization file that lists all of the people associated with it and one in the Individuals file that lists all of the Organizations that the person belongs to (and the obvious relationships between the SN's in each file so that the portal works).

I want the user to be able to add a person to an organization via a pop-up menu in the portal row that shows the organization names (not the serial numbers). So I have a value list that holds the organization names (looked up from the Organization file).

Here's the problem: I don't see how to get this to enter the OrgSN in the join file instead of the Organization Name. I don't want the serial numbers to show anywhere since they are meaningless to the user. The first column in the portal needs to be the organization name not the serial number, but if I do that (and format it with the value list), then it enters the organization name into the join file for that record rather than the OrgSN.

I have a hack that sort of works, I define the value list to be the OrgSn + Name and then overlay another field on that that looks up the Organization Name, but if you're editing the record, this then shows the OrgSN rather than the name.

It seems that I need a value list that displays one thing, but has the value of another, but I don't get how to do this.

[LiveOak]

You're almost there. You need a value list with for the Organization Number showing BOTH the numbers and names. What is missing is to define calculated fields in the join file to display the organization name corresponding to the number selected. In the join file define:

OrgName (calculation, text) = OrgNumRelationship::Orgainzation Name.

-bd

[mbeall]

Thanks for the reply, I thought I had done that once and it didn't quite work (what showed up in the portal was stll the OrganizationSN). But now I'm trying it again I still don't get the results I want, here's what I've tried:

I define a field OrgName as a calculation (result text) that = Organization::Name. (note, this is an unstored calculation, which will be relevant below. If I try to make it stored I get an error "The calculation "OrgName" cannot be stored or indexed because it references a related field, a Global field, a Summary field or an unstored Calculation in its formula)

Back in Individuals I define a value list Organizations to "use values from field: "SN" and "Name" from Organization".

In my portal in Individuals, I specify the first field in the portal to be OrgName from the Organization relationship (which is to the OrgInd file).

This correctly show existing records in the portal, but if I try to add a new record in the portal or change an existing one I get an error message "This field is not modifiable" This error is related to the OrgName field being a calculation (if I change it to a regular text field the error goes away, but then it puts the OrganizationSN in that field).

Also I don't like the fact that the pop-up menu shows the OrganizationSN in addition to the name, but I could live with that if I had to (although it doesn't seem like I should have to).

So I tried changing the first field in the portal to be the OrganizationSN from the Organization relationship, now it correctly updates the portal, but the OrganizationSN is shown in the portal (not both the SN and the name which is how the value list is defined, just the SN).

If I try this trick of overlaying an nonentry field that displays the OrgName, then it sometimes works (it seems to randomly show either the OrgSN or the OrgName depending on if I'm editing records in the layout).

So I'm pretty sure at this point I've gone around in a circle and tried everything again and apparently missed the solution.