Value lists and Duplicate (?) Values

[DanBrill]

Hi,

I have a database called People, with contact info on members of my group. I also have a database called Organizations, with info on orgs. that we works with, such as businesses, churches, etc. Each person in People has a unique ID number, and each Organization in Organizations has a unique ID number.

I want to be able to relate People to Organizations. So I have a pop-up list so that the church they attend can be entered. The pop-up list is populated with the Organizations.

The problem is if I have different organizations with the same name. For example, we have 4 organizaitons called St. Mary's Church in the Organizations database. I might expect problems if I were basing the value list on the Organizations' names. But I'm not -- I'm basing it on the unique ID numbers. The value list is set to display by the Organization ID number, and also display the Organization name, and is set to sort by the second value, Organization name.

When the list pops up, only the 1st org. called St. Mary's Church is present. The others aren't there, despite their unique ID numbers.

What am I doing wrong, or what is a work-around for this?

Thanks,

Dan

[Ugo DI LUCA]

Hi Dan,

Nothing wrong in your settings apart that you are sorting it by the second text field.

Make the Name of the Oganization a unstored calculation using a global field that you will populate with a 1.

c_newOrgName = Case(global = 1, Organization Name, " ").

Use this calc as the sort field.

[Leb i Sol]

hello Dan!

I have bugged these good people about simmilar issue that might help:

old post

hope it helps

[DanBrill]

Hi Ugo,

When I try to set this up it tells me the value list won't work because c_newOrgName cannot be indexed. It can't be indexed becasue it refers to a global field. How does turning the OrgName (text) into a calculation field help?

The closest I've gotten is to create a calc

c_OrgName = OrgName & " (" & ID & ")"

And set this to be indexed.

This forces St. Mary's with ID 1 to be different than St. Mary's with ID 3. So the user clicks on the pop-up list and sees:

4 St. John's (4)

1 St. Mary's (1)

3 St. Mary's (3)

2 St. Xavier's (2)

The field saves the ID and it is sorted by the name. It works, but it is ugly. I guess I'll live with it if I have to.

How am I screwing up what you suggested?

Thanks,

Dan

[Ugo DI LUCA]

Dan,

"How am I screwing up what you suggested?"

You didn't, I did

The concanation is the best you can have, right you are.