Auto Generated Unique ID

Romano el Polako

Hello Smart Heads! Happy New Year!

I am facing an issue with big data sets, missing a single unique ID.

The only option of creating an unique ID is by joining content from 3 various cells, see:

id is created by using simple formula:

=EntryNumber@row + BLNumber@row + ContainerNo@row

In a raw file (excel of course), I have created the same column with:

=CONCATENATE(A255,G255,H255)

Important about raw file:
rows can change overt time, hence it is required to update the unique rows on the weekly basis

I have created a data shuttle set up to update the master file with weekly raw data:

The main issue (I suppose) is with id column generated via formula. Therefore I cannot link it with id column created in excel:

Desired outcome:

Master file (quarterly - considering size limit) with rows updated on the weekly basis (both new and updated)
Manual effort: 1) download file, 2) upload to smartsheet
Reporting (not in scope of this request, i should be able to manage, as soon as I ensure maintenance of unique rows).

Thanks in advance for your help, please let me know in case of any questions!

Romano

Find more posts tagged with

Formulas

Data Shuttle

Accepted answers

All comments

Julie Fortney

@Romano el Polako

You should still be able to use the id column as your unique ID, but you don't need to map it. Just map the columns that make up your id column formula: EntryNumber, BLNumber, and ContainerNo.

Does that help?

Romano el Polako

Hey @Julie Fortney

Sorry for delay and thank you for your answer.

Actually it did not help, as I had to anyway create similar column manually in excel before upload, which was not a huge automation anyway.

Nevertheless, believe it or not, requesters forgot to mention one column at the very end of the raw file (deleted in the very beginning of project scoping as non-value added) with unique id provided by broker.

As a result I was able to feed the master file. The only problem might be the size limit of 20k rows, but hoping for the best and lack of limits in the near future.

Thanks,

Romano

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.