Jump to content
Sign in to follow this  
DanBrill

Value lists and Duplicate (?) Values

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Dan,

"How am I screwing up what you suggested?"

You didn't, I did frown.gif

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.