Auto-populating Job Numbers

I've had a query from one of our Smartsheet users who are looking to generate consecutive numbers when we they are building rows of jobs. I've advised him of the Auto Number/System column type, which would help with this. He then needs for all children rows to be auto generated an asset number. His query is as below:

If we add a row to a sheet of jobs to create a new job, is there a way of smartsheet allocating it a job number?

Then within sub rows if that job has 10 assets/deliverables would there be a way of creating asset numbers?

Eg Job No: 3021 followed by asset number 3021.1 3021.2 etc

Next job 3121 followed by 3121.1 3121.2

I think this could be possible, perhaps involving a formula and using a separate column but I haven't a clue where to begin! Any ideas would be much appreciated.

Find more posts tagged with

Sheets

Formulas

Support

Project Management

Accepted answers

All comments

Paul Newcome

Are you going to want to pull the auto-number for the parent rows, or did you want a specific incrementation for the parent rows?

SteCoxy

Hi Paul,

Yes, the Parent Row would be the basis for the assets (aka children rows) underneath. My colleague sums it up quite well:

Eg Job No: 3021 followed by asset number 3021.1 3021.2 etc

Next job 3121 followed by 3121.1 3121.2

I wasn't sure if this might need a helper/hidden column to achieve this.

Paul Newcome

I understand that. I meant for the parent rows. In your example we have 3021 for the first job and 3121 for the second. Is there specific logic behind this or is it simply pulling from the auto-number column?

SteCoxy

Hi Paul, thank you for your assistance (once again!) - I am not 100% on this, so I will get my colleague to respond here.

Shaun Doyle

Hi,

Thank you for this. It might be that standard sequential parent rows are fine for job numbers(101,102, 013 etc) and the only option. However the above was testing to see if more of a coding system could be used.

3021 (is job 30 year 2021)

3121 (is job 31 year 2021) etc

That way parent rows have a time stamp to them as well as a job number.

I hope that explains my thought process?

Shaun

Paul Newcome

It is possible to have the "3021" portion automated as well, but we would need to get a few more questions answered...

Do you have a date column that we can pull the year from?

Are you anticipating less than 100 jobs in a year?

Do you want leading zeros so that all entries have the same number of digits (00121 through 99921 for example) for the prefix?

Same question with the Child Count indicators. Do you want leading zeros? If so, how many digits do you want prefilled?

Prefill examples for Job 31 in year 2021 on child row number 5:

Both job and child indicators to 3 digits: 03121.005

Job to 3 digits and child to 2 digits: 03121.05

Both to 2 digits: 3121.05

Job at 2 digits and child at 3 digits: 3121.005

Or would you rather just the numbers with no prefilled zeros so that Job 1/Child 1 would be "121.1" and then Job 52/Child 12 is "5221.12"?

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.