How to error proof QR code

I have a function that is dependent on the operator scanning a QR code:

=(IF(NOT(ISBLANK([QR Code]@row)), LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), ([Job number]@row)))

If the operator doesn't scan a QR code or doesn't have one, I want the cell to show what the operator typed or scanned from a 2D bar code rather than opening a new input (Job Number) to populate the cell or retuning #invalid value

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Alfredo Brioli

    First, I cleaned up your formula to remove extra brackets that don't need to be there. This helps me see the structure of your instructions. I also updated the NOT(ISBLANK()) portion with <> "" (which says "not blank") because this helps me read through the formula, but it's a personal preference:

    =IF([QR Code]@row <> "", LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), [Job Number]@row)

    This says:

    If the QR Code is not blank, then return everything to the left of the # sign, otherwise, if the QR Code IS blank, return the Job Number.

    It sounds like you don't want the Job Number portion of the formula. In this case, you can reference the cell that would contain the 2D Bar Code or typed number instead. Replace the bolded portion with the name of the column you have set up to receive the typed number:

    =IF([QR Code]@row <> "", LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), [Alternate Number]@row)


    Let me know if this works for you! If not, it would be helpful to see a demo version of your sheet with a full explanation of how you want the formula to be automated.

    Cheers,

    Genevieve

  • Genevieve, thanks for your reply.

    What if the QR Code is not a QR code, i.e. they typed in because they didn't have a QR code? Is there a way to error proof it, and/or just print what they typed in the QR code?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Alfredo Brioli

    Do you have a database sheet that stores all of your current QR Codes? If so, you could use a cross-sheet formula to ensure that the value entered exists in that other sheet.

    If it doesn't you can simply return what they typed in. If it does exist, you can run your current LEFT formula.

  • It retuns an #INVALID VALUE if it is not a QR Code

    =IF([QR Code]@row <> "", LEFT([QR Code]@row, FIND("#", [QR Code]@row) - 1), LEFT([QR Code]@row))

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Alfredo Brioli

    Can you confirm what you mean when you say "It is not a QR code"? Do you mean when that cell is blank, or when the value typed in does not correlate to one of your specific codes?

  • Alfredo Brioli
    edited 09/06/22

    If a number or characters are entered (not a QR Code), it returns an #INVALID VALUE in the worksheet.

    7619630#30767300#307673#R00#4 is a QR code they could scan

    JOB NUMBER#PART NUMBER#RAWING NUMBER#REV LEVEL#JOB QUANITY

    If they scan the wrong code, or enter say, 7619630, it errors

    only when a QR code is scanned does the data populate correctly, regardless what is in the "Else" of the IF statement


  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Alfredo Brioli

    Formula errors can create a domino effect, meaning that if a cell has an error, when you reference that same cell in another formula that formula will automatically show the same error.

    Can you clarify what formula you're using in the "Job (QR)" column?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!