Datatable Lookup Mode: Checkbox if no Unique Identifier Value was found in the Datatable

I'm using a Datatable Lookup to populate rows on a sheet.

Is there any way to add a checkbox automatically if the system found no result of the Unique Identifier in the datatable?

To be clear, I mean the value in that Row's "Sheet Unique Identifier" column was not found, and NOT that it found it but the mapped columns didn't have any data.

Find more posts tagged with

DataMesh

lookup

no results

Accepted answers

NeilKY

It's been a while since I posted this but I just discovered a way to know if the Datatable found a result or not!

I just noticed that the "Last Updated by" field on the corresponding row is changed to "datatable@smartsheet.com" after the lookup is FOUND on the sheet. So, until it finds a result, the modified by field will remain the email address of the person who entered in the data.

For example, this could be useful if you use an IF statement to add a checkbox if the email address is equal to datatable@smartsheet.com. If it's not, then no result is found and it gets checked.

All comments

prime_nathaniel

@NeilKY this is not a feature I am aware of. Your best option is to denote some kind of flag in your datatable import workflow, such as "send to datatable" or "archive", now you can use that field to denote if it exists in datatable as every record should have that piece of data to exist in datatable at all.

This is also beneficial whenever you need to do audits or other manipulations against your historics.

NeilKY

https://community.smartsheet.com/discussion/comment/479692#Comment_479692

@prime_nathaniel I"m not sure what you mean?

Allow me to give an example of the data:

I'm looking up a "street address" value in a list of all existing addresses in our city. in the other columns it has information such as the city, zip, geolocation, etc.

If the address doesn't exist, I need the sheet to flag the address as "Not found". If it finds the address, it populates the details of the address from the matching values in the Datatable (City, zip. etc).

Are you saying that I should use an automation of some sort to add the "street address" to the end of the Datatable that I'm looking up, whenever a row is added, as well as another column that has a value in it on the same row to flag the row? That way, the lookup finds the first result (if it exists in the table already) but otherwise it finds the row I just added with the flag?

NeilKY

It's been a while since I posted this but I just discovered a way to know if the Datatable found a result or not!

For example, this could be useful if you use an IF statement to add a checkbox if the email address is equal to datatable@smartsheet.com. If it's not, then no result is found and it gets checked.

Marcela

Hey @NeilKY

Thank you for posting your solution. I'm glad you got this resolved!

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.