Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: Comma separated list

    I need to take the a column of fields in CR and return it with comma separating the values. Instead of..

    Name 1
    Name 2
    Name 3
    Name 4

    it would be Name 1, Name 2, Name 3, Name 4.

    Thanks in advanced.

  2. #2
    Join Date
    Jul 2007
    Posts
    54
    Create a formula as follows:

    WhilePrintingRecords;
    Shared StringVar CSVString;
    if {CurentCell} = ""
    then CSVString:= CSVString;
    else CSVString:= CSVString & "," & {CurrentCell} ;

    CSVString is just a name I made up - you can call it whatever you like.

    {CurentCell} is the name I made up for your data Name 1

    The IF statement avoids adding blank data (unless you want that).

    You will need a formula to initialise CSVString at the start. The formula shown must be placed on the same line as your data (Name 1 and so on). On the last line you should get what you want, BUT if you then want the result on the following line then you must be careful and check that it doesn't add the last value on twice (so you might end up with
    Name 1, Name 2, Name 3, Name 4, Name 4) - in this case you will need a separate final formula which just gives CSVString and doesn't add anything to it or else a formula that accepts the result and then removes the very last value. Basically just try it and then look very carefully at the result you get and test it.

    HTH
    Caspar

  3. #3
    Join Date
    Sep 2009
    Posts
    2
    I posted the above formula in my report, now I get a list with a comma in the front of the list and it duplicates the information for every "name" that I have associated with a specific ID Number.

    I am trying to use this as a subreport in an existing report. The reports are linked with a common ID number. A specific ID Number has different values than another ID number.

    ID 001 has 4 names linked to it that I need listed in the comma list. Here is what I see now...

    ID 001 Report -
    Subreport
    , Name 1
    , Name 1, Name 2
    , Name 1, Name 2, Name 3
    , Name 1, Name 2, Name 3, Name 4

    Here is what I need
    ID001 Report
    Subreport
    Name 1, Name 2, Name 3, Name 4

    TIA

  4. #4
    Join Date
    Jul 2007
    Posts
    54
    Try setting a Group on the Name field and then suppress the details and just show the group footer and add the formula in there (but beware the possibility of adding the last field twice like I mentioned in my last post).

    Try changing the formula to:

    else CSVString:= CSVString & {CurrentCell} & "," ;

    - this should move the comma.

    You will probably find that you end up with
    Name 1, Name 2, Name 3, Name 4,
    or possibly
    Name 1, Name 2, Name 3, Name 4, Name 4,
    with a comma after the last piece of data - is that acceptable? A possibly solution would be to put a different formula in the Group Footer (and NOT the formula you already have) which says something like:

    WhilePrintingRecords;
    Shared StringVarTrimmed CSVString;
    CSVStringTrimmed:= LEFT(CSVString,(LEN(CSVString)-1) ;

    The above should leave you with:
    Name 1, Name 2, Name 3, Name 4

    Don't forget to initialise CSVStringTrimmed somewhere.

    I have only created this in theory and it will require a little tinkering with.

    In fact I am thinking about this as I type it and suppressing the detail section may mean that the WHILEPRINTINGRECORDS never happens (because it is not actually printing/displaying that line) - you'll just have to try it and see. One way around that challenege is to make the detail line as narrow as possible and shrink all of the fields to be as small as possible and suppressed or white font - the only disadvantage of that idea would be a bit of empty space which would be larger for the greater number of lines.

    I guess it's a bit late for me and so my brain is not working so well just now - please try it and let me know how you get on.

    HTH
    Caspar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •