Create text from field in found records

[millmaine]

We want to summarise data from all projects by a particular client by date range. I'm quite happy with the way I've managed to set that up. What I'm having trouble with is adding a list of the projects as one piece of text.

 

I can set up a script that finds all the relevant projects and exports the Project Name(s). And I think with a bit of messing about I could figure out how to import that to as one piece of text. (I can do it manually so I must be able to automate it!)

 

I'm pretty sure though, that there's a simpler way. I've played around with Merge Variables but I really don't think I'm getting it.

 

Any tips?

[bcooney]

Not sure where you're going with this, but take a look at the List ( ). Another way is to loop thru the found set and build a list in a global (field or variable). Why do you need all the Project Names in one field?

[doughemi]

You have a client table and a project table, right?

 

Create a global field StartDate, and another EndDate in clients. Create a relationship your client table and a TO of your project table:

clients::StartDate ≤ projectsTO::Date

clients::EndDate ≥ projectsTO::Date

clients::id = projectsTO::ClientID

 

Create a calculation field in clients  = List(projectsTO::ProjectName)

[millmaine]

You have a client table and a project table, right?

 

Create a global field StartDate, and another EndDate in clients. Create a relationship your client table and a TO of your project table:

clients::StartDate ≤ projectsTO::Date

clients::EndDate ≥ projectsTO::Date

clients::id = projectsTO::ClientID

 

Create a calculation field in clients  = List(projectsTO::ProjectName)

That works. Now I need one more tweak. 

 

Each project is on a separate line. I want them as a single block of text with line-breaks replaced by comma space (or some other separator). Any help on that?

[millmaine]

Figured last bit out using Substitute.