Value List in concetanated calc key

[LindaG]

I have searched and can't find anything on this. If it's been discussed before I apologize in advance.

I have field for Staff in my projects file. I want to make it so several staff can be assigned to a project so I changed it to a checkbox which pulls the StaffID from a value list in staff. So far so good. Several staff can be assigned to one project. I want to establish a relationship from projects to Activities but I want it to treat each staff (checked in the checkbox) as a separate key field. No prob if that's all the key is ... but it also has project and date.

My current key to activities is:

ProjectID & StaffID & MonthYear

MonthYear is a global

In activites, my key is ProjectID & StaffID & MonthYear

Unfortunately, it won't match up. Only the first line does. The next two lines display all staff records regardless of project or month year. The result is like this in Projects (if there are three staff in the checkbox)

ProjectIDStaffIDMonthYear

StaffID

StaffID

It seems to take the StaffID and repeat on each line because there are several of them, but it doesn't repeat the projectID or Monthyear. Is there a way to make it produce three lines like this:

ProjectIDStaffIDMonthYear

ProjectIDStaffIDMonthYear

ProjectIDStaffIDMonthYear

so when I use go to related it will grab the correct information? I'm been playing with Extend but I don't think that will work with checkboxes. Well, I can't make it work with them anyway. Maybe I should use repeating fields for this. Any suggestions?

Linda Gent

[BobWeaver]

Try this formula for your key field:

ProjectID & Substitute(StaffID,"

[LindaG]

Brilliant and perfect, Bob. Thank you so much.

Could you turn your formula into regular words for me so I can understand it? What exactly is it doing? Well, I mean, I know what it ends up doing but I don't understand why. I'm new to calculations and can't quite figure out why it is working as it does.

Linda

[LindaG]

I think Bob missed my last question - he always seems so willing to respond and help - so I'm making that assumption. I doubt he checks back this far for old messages. Or he's very busy. Can someone else explain this to me? I really want to understand why this silly thing works for me, as I may need to do something similar in the future and I hate to bother all you nice people unles I get really stuck. Thank you so much for any insight you can provide me.

Please talk in human terms and not programmer terms. I'm not very good at these things.

Linda

[BobWeaver]

Hi Linda, the site was down yesterday when I tried to check in, but I normally do check back through messages to see if there are any additional questions.

As for your calculation, When you set up a multi-key (or a value list), the values are placed on separate line (ie, separated by a linebreak character which is represented by a paragraph symbol

[LindaG]

Oh thank you so much Bob. Now I can truly say it's brilliant and actually know WHY! I think I'm going to spend part of my weekend playing with this. It seems like a powerful concept to learn. Thank you for helping all of us newbies with our silly questions. You are greatly appreciated.

Linda