Hey Team, So here's the situation, I need to report on a dataset, grouped by client name, then by job number: I'm importing a dataset into Smartsheet; filenames should be in the below format: Great. I can run a formula to break this up and run the report. Right? The issue Human error. Some examples of incorrectly named files: * 12242441 Clientname Projectname Projectname Projectname * 12457034_Clientname_Clientname_Projectname_Projectname * Clientname_12242011_Projectname Considerations * Job numbers always either 6 or 8 digits. * Client names are entered as free text in the source data. * I do have an approved client name index already within Smartsheet, that could be referenced. * I do have influence to change the approved filename structure within the imported dataset. Right now I'm out of ideas. Any thoughts? 🤯 Matt

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Formula help required

darkmatter

Hey Team,

So here's the situation, I need to report on a dataset, grouped by client name, then by job number:

I'm importing a dataset into Smartsheet; filenames should be in the below format:

Great. I can run a formula to break this up and run the report. Right?

The issue

Human error. Some examples of incorrectly named files:

12242441 Clientname Projectname Projectname Projectname
12457034_Clientname_Clientname_Projectname_Projectname
Clientname_12242011_Projectname

Considerations

Job numbers always either 6 or 8 digits.
Client names are entered as free text in the source data.
I do have an approved client name index already within Smartsheet, that could be referenced.
I do have influence to change the approved filename structure within the imported dataset.

Right now I'm out of ideas. Any thoughts? 🤯

Matt

Find more posts tagged with

Formulas

Accepted answers

S.Stone

@darkmatter I'm most of the way there for my template scenarios (may not work for all edge cases). Do you have a list of project names, or are they going to be new each time?

Job Number:

=IFERROR(VALUE(LEFT([Submitted File Name]@row, 8)), IFERROR(VALUE(LEFT([Submitted File Name]@row, 6)), IFERROR(VALUE(MID([Submitted File Name]@row, FIND("_", [Submitted File Name]@row) + 1, 8)), IFERROR(VALUE(MID([Submitted File Name]@row, FIND("_", [Submitted File Name]@row) + 1, 6)), IFERROR(VALUE(MID([Submitted File Name]@row, FIND(" ", [Submitted File Name]@row) + 1, 8)), IFERROR(VALUE(MID([Submitted File Name]@row, FIND(" ", [Submitted File Name]@row) + 1, 6)), ""))))))

Client Name:

=IFERROR(INDEX(COLLECT({Job-Client-Project Client List Range 1}, {Job-Client-Project Client List Range 1}, CONTAINS(@cell, [Submitted File Name]@row)), 1), "")

Once you've got everything separated, you can just concatenate the final list however you want. It'll just come down to whether you can reference a project name list the same way you did the client name or if you need to extract the remaining text once you remove the job number and client name text.

All comments

Adam_C

Hi @darkmatter,

Human error in free text fields is the trickiest problem to solve in my experience, mostly because it is impossible to anticipate how people will mess up free text fields, especially when they contain text and number combinations.

A few questions to get us started:

is it possible for you to automatically reject file submissions with the wrong filename format?
Can you look up the client name and/or project name from the job number in another dataset?
Do your client or project names have spaces in them?
Have you got any of the fields you need elsewhere in the dataset?

I can solve for the specific errors you mentioned above, but as noted at the beginning of this answer - it's impossible to predict how the error will show up in future.

S.Stone

@darkmatter I'm most of the way there for my template scenarios (may not work for all edge cases). Do you have a list of project names, or are they going to be new each time?

Job Number:

Client Name:

=IFERROR(INDEX(COLLECT({Job-Client-Project Client List Range 1}, {Job-Client-Project Client List Range 1}, CONTAINS(@cell, [Submitted File Name]@row)), 1), "")

darkmatter

Thank you this is exactly what I need!

Matt

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.