value list and string to match

[rivet]

Here is the challenge:

I have a project db and teams are assigned to each project. I will assign names to teams through a simple portal, but I would like the project report to summarize with a text calc. So the result is one long string showing only the teams that where build. Also the name of the teams and people are not predefined.

i.e.

ProjectA

BLUE TEAM: John, Sandy, David RED TEAM: Daniel, Sam, Tes, Frank, Betty

[The Bridge]

You could define a valuelist, say, vl_Team_Members, which draws names based on a relationship matching Team to itself.

Then you could define a calculated text field, e.g.

Team_Members = Substitute(ValueListItems(Status(CurrentFileName), "vl_Team_Members"), "

[rivet]

that does work except the value list has to be indexed and will not dynamicly update. Can and force update be scripted some how, if not how would you create a scrpt to create that string?

[The Bridge]

Yes, I find the ValueListItems() function a little finnicky sometimes, especially in list view. It seems to work most reliably with hard-coded valuelists or, in the case of related valuelists, related valuelists in which the relationship is based on a global field matching an indexed field in the related file.

If the comma-separated list of team members is only needed for printed reports it would probably be best to set a global text field using the Set Field script step. This global field would form the left-side of the relationship that the valuelist is based on.

I hope this makes sense. Good luck!

[rivet]

so if my global field holds the teams (red, blue, green), and that pulls all the names I can see a result such as:

red: david, red: steve, blue: chirs, green: jean, green: trish

can you think of a cal that would clean that result so I get:

red: david, steve

blue: chirs

green: jean, trish

or have I misunderstood your solution

[BobWeaver]

I found that you can force the ValueListItems function to update by adding an aggregate function based on the same relationship to your formula. For example, if the valuelist ValLst is based on relationship MyRel, then a formula would be:

Valulistitems("MyFile","ValLst") & Left("",Count(MyRel::AnyField))

[The Bridge]

How 'bout I just put my money where my mouth is?

Here's a sample file using the data you've supplied.

rivetvaluelist.zip

[The Bridge]

Thanks for this tip, Bob -- this will come in very handy.

[rivet]

amazing that for all your help guys, and bob that is a slick fix