Formula to workflow a form submit that duplicates another entry

Marc Meilert

I would like some help please on a formula that would verify the submitted information from the FORM against all existing rows and inform the submitter/owner that a duplicate entry has been submitted.

I want to workflow this so the row gets highlighted against the existing configuration of another row and informs the submitter of the other existing configuration (Row identity)

I have 6 columns of information to verify against.

Help?

Hopefully this functionality will be added to smartsheet eventually.

L_123

You don't even need the join formula per say.

=cola@row + ", " + colb@row + ", " + colc@row

Marc Meilert

https://community.smartsheet.com/discussion/comment/269351#Comment_269351

This actually was a critical part of the answer as well

Mike Wilday

It's very easy to create something that would initiate conditional formatting. But I would need to see an example of the columns and some sample data.

Marc Meilert

That is an interesting approach... Can you give me an example? I literally have 6 columns with a single select of 50+ options. All columns have the exact same list of 50+ available to select.

If all 6 columns have the exact same selections as a previous row of all 6 selections, how would you highlight it, or format it? It becomes a visual verification then?

Unfortunately the grid is not something I can easily share. Let me know if there is a way I can help clarify my ask.

thanks

Mike Wilday

That is intriguing, So all 50 items are available in All six columns? So there would be 6 * 50 different combinations? Having the column headers at bare minimum would be helpful.

Marc Meilert

Column headers are literally:

Custom Component #1

thru

Custom Component #6

The combination from the request form gets attributed a configuration ID (auto-numbered)

I want to avoid duplicate configuration ID's being ordered.

L_123

First thought is I would use a helper column and join all of the values together, then use something like

=if(count(distinct(JoinerCol:JoinerCol))<count(joinerCol:joinerCol),1,0)

for the alert.

Mike Wilday

@L@123 That is what I was thinking as well...

@Marc Meilert Create the joiner column that joins the values of columns 1-6 then use the formula that L@123 put up there as a column fomula in a checkbox column to check the box if there its not unique. Then you could create conditional formatting based on when the checkbox is checked.

Marc Meilert

Great guys, I appreciate the thoughts. I will format those trials and let you know!

Marc Meilert

I had the opportunity to work on this today. My issue is that it seems the JOIN function needs to be a range of 6 columns that would be next to each other. My 6 columns are separated by other columns of information that are not in the JOIN information I need...am I mistaken?

I looked into =JOIN(COLLECT......) as well , no success.

SO can I have a "joiner" column that collects the information from 6 columns separated by other columns?

L_123

You don't even need the join formula per say.

=cola@row + ", " + colb@row + ", " + colc@row

Mike Wilday

Yep. That would work as well.

Marc Meilert

That worked!  Thanks guys!

Mike Wilday

Awesome. Glad we could help you out. Please accept our answers!

Marc Meilert

see below

Marc Meilert

https://community.smartsheet.com/discussion/comment/269351#Comment_269351

This actually was a critical part of the answer as well