How to split text to support reporting?

Anne-Solene Monrouzeau

Hi,

I am bumping into a design / Smartsheet limitation issue. I use an request inventory sheet to track client change requests. CR are submitted via a form. One of the field helps tracking the category of the CR (business feature grouper). I had to concatenate 2 types of data to workaround the form and the fact that I cannot have a conditional menu. Example: if 01) core claim processing is selected, then sub menu should display only A,B,C. See information and table below for more details.

What I'd like to do now is report only on the business feature grouper, not the sub-groupers (so 01) Core Claim Processing, 02) Payroll Processing, 03) Claim Correspondence, etc.). I am manually capturing the information in dedicated column that is hidden and that is used in a report.

How can I either use a formula to split the grouper field and capture only the first part of it?

OR how can I redesign my form to capture the grouper / sub grouper in an easy way?

Current drop down menu, to support form submission:

01) Core Claim Processing 10) Leave Plan Configuration (Fed FMLA, State FMLA, ADA, Military, and all flavors of company leaves)

01) Core Claim Processing 20) Disability Plan Design(STD, LTD, PFL, STAT, etc.)

01) Core Claim Processing 30) Claim Workflow

01) Core Claim Processing 40) Claim Tasking

01) Core Claim Processing 50) Claim Calculations

01) Core Claim Processing 60) Intake Scripts

01) Core Claim Processing 98) TBD

01) Core Claim Processing 99) Other

02) Payroll Processing 10) Benefit Calculations

02) Payroll Processing 98) TBD

02) Payroll Processing 99) Other

03) Claim Correspondence 10) Letters

03) Claim Correspondence 20) Emails

03) Claim Correspondence 30) Text

03) Claim Correspondence 40) Forms

03) Claim Correspondence 98) TBD

03) Claim Correspondence 99) Other

04) Inbound File Feed Integration From ER or Agent 10) SSO Setup

04) Inbound File Feed Integration From ER or Agent 20) Roster File

04) Inbound File Feed Integration From ER or Agent 30) API Integration

04) Inbound File Feed Integration From ER or Agent 40) Conversion of Takeover claims

04) Inbound File Feed Integration From ER or Agent 98) TBD

04) Inbound File Feed Integration From ER or Agent 99) Other

05) Outbound File Feed Integration To ER or Agent 10) Time Keeping File Feed

05) Outbound File Feed Integration To ER or Agent 20) Leave Status File Feed

05) Outbound File Feed Integration To ER or Agent 30) Data Warehouse File Feeds

05) Outbound File Feed Integration To ER or Agent 98) TBD

05) Outbound File Feed Integration To ER or Agent 99) Other

06) Digital Tools 10) Next Self Service Portal

06) Digital Tools 20) Self-Service Reporting

06) Digital Tools 98) TBD

06) Digital Tools 99) Other

07) External Vendor Integration 10) Cigna Better Integration

07) External Vendor Integration 98) TBD

07) External Vendor Integration 99) Other

08) Telephony / IVR Integration

09) Information Security

10) Partner Core Integrations (Carrier) 10) CIGNA

10) Partner Core Integrations (Carrier) 20) GLIC

10) Partner Core Integrations (Carrier) 30 )GLIC

10) Partner Core Integrations (Carrier) 40) Guardian

10) Partner Core Integrations (Carrier) 50) Co-Source

10) Partner Core Integrations (Carrier) 98) TBD

10) Partner Core Integrations (Carrier) 99) Other

Mapping table:

Paul Newcome

Are you able to provide a screenshot of the portion you are wanting to automate?

Anne-Solene Monrouzeau

Here you go. The column "Request Business Feature Grouper" is the one populated via form. The business feature grouper column is the one I manually update when a new request is submitted to use in a report.

Leibel S

@Anne-Solene Monrouzeau

Create (or if you have already a) sheet that just has the list of Business Feature Groupers (01) Core Claim Processing, 02) Payroll Processing, etc.).

Then in your Business Feature Grouper column you would use the following formula:

=IFERROR(JOIN(COLLECT({Business Feature Grouper Range}, {Business Feature Grouper Range},CONTAINS(@cell,[Request Business Feature Grouper]@row)), CHAR(10)), "")

Anne-Solene Monrouzeau

The formula below returns an incorrect argument set. I am not too sure I understand how it works to troubleshoot. Any thoughts?

=IFERROR(JOIN(COLLECT({Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row)), CHAR(10)), "")

Paul Newcome

You will need to repeat that first range a second time within the COLLECT function:

=IFERROR(JOIN(COLLECT({Grouper Mapping Business grouper mapping}, {Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row)), CHAR(10)), "")

Anne-Solene Monrouzeau

Here is what the formula returns.

Here is a snapshot of the mapping table - do I need to change anything in the mapping?

Leibel S

Thanks @Paul Newcome for the correction :)

@Anne-Solene Monrouzeau

The list of 'Feature's should only have each 'Feature' once.

Alternatively you can make a duplicate column and exclude it in your formula:

=IFERROR(JOIN(COLLECT({Grouper Mapping Business grouper mapping}, {Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row),{Duplicate Range},@cell <>""1), CHAR(10)), "")

Anne-Solene Monrouzeau

Updating the mapping table fixed the issue with the duplicates.

I have one more issue with one of the grouper: the formula does not work with 2 of the cells. I am not sure why. That's the only 2 cells that returns blank over 200.

Business Feature Grouper 

Leibel S

@Anne-Solene Monrouzeau

Lets change the formula to not use the duplicate column and instead add the DISTINCT function.

=IFERROR(JOIN(DISTINCT(COLLECT({Grouper Mapping Business grouper mapping}, {Grouper Mapping Business grouper mapping}, CONTAINS(@cell, [Request Business Feature Grouper]@row))), CHAR(10)), "")

It looks like you do have some things spelled differently:

e.g. 'Claim Correspondence' vs 'Claim Correspondance' (e vs a)

I would go through and make sure they are all spelled the same...

Anne-Solene Monrouzeau

Good catch, that fixed the issue. I did not see the typo :)