×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Build comma separated list with output from formulas

Build comma separated list with output from formulas

Build comma separated list with output from formulas

(OP)
I need to build a comma-separated list with the data that is output from the formulas I've setup. There are 40 different groups (based on 40 different formulas) that people can fall into. Each person can be part of more than one group. I need to build a list summarizing the groups that they fell into. Is there a simple way to do this - generating a comma-separated list of all the groups they're in?

RE: Build comma separated list with output from formulas

A simple solution to summarize the data would be to insert a crosstab in the report header or footer, using {table.person} for the row and {table.group} for the column. The summary could be almost anything, e.g., maximum of {table.person}, which would then show up if the person was a group member. You can remove subtotals and totals in the last crosstab tab.

If the comma-separated list is essential for some other use, you need to provide more info, e.g., sample results for two or three people with fields labeled. You should also show the contents of of a couple of your formulas, including any nested formulas. It might be helpful also to know how the resulting lists will be used.

-LB

RE: Build comma separated list with output from formulas

(OP)
Unfortunately, I can't use a cross-tab for formatting purposes. The formulas aren't complicated, but they're each set to super-specific output criteria and formatting based on the data.

Essentially, it will end up in almost a spreadsheet-like column format. The person's name would be listed in rows with columns such as "Giving Society" and "Dean" and "Special Award". Based on those columns being populated, their would be a summary field that read "Giving Society; Dean; Special Award". An example of the desired output is below (in Excel for ease of demonstration).



Example of formula for field output:

CODE -->

IF {Membership_CnMem_1Cur.CnMem_1Cur_Category_description} = "Giving Society" AND (isnull({Membership_CnMem_1AtrCat.CnMem_1AtrCat_1_Description}) or {Membership_CnMem_1AtrCat.CnMem_1AtrCat_1_Description} = "Invite Only")
THEN "Member"
ELSE
    (IF {Membership_CnMem_2Cur.CnMem_2Cur_Category_description} = "Giving Society" AND (isnull({Membership_CnMem_2AtrCat_1.CnMem_2AtrCat_1_Description}) or {Membership_CnMem_2AtrCat_1.CnMem_2AtrCat_1_Description} = "Invite Only")
    THEN "Family"
    ELSE "") 

RE: Build comma separated list with output from formulas

Assuming your data now shows multiple rows for each person, but with only one entry per column (formula) per person, you could group on {table.person} and then write a formula like the following to place in the group header and then suppress the detail and group footer section. The formula below also assumes that your column formulas cannot result in nulls—if they can, you would have to add in null checks.

Maximum({@GivingSociety},{table.person})+”; ”+maximum({@Dean},table.person)+”; ” +maximum({@SpecialAward},{table.person})//etc.

Be sure to replace my single quotes with straight ones.

-LB

RE: Build comma separated list with output from formulas

(OP)
I do have it grouped by the person already and it can result in nulls. Looks like it'll be long formula for all 40 potential options. I appreciate the help!

RE: Build comma separated list with output from formulas

You could consider changing your report setting to “convert nulls to default”, but then you would have to change your current null checks inside your formulas from null to “”. Might be the most reliable way and easiest way to go. Maybe save your report as a test version and use the convert nulls to default and see how that works for you.

-LB

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close