I have a simple form that I need to work out ...

[Amir_West]

I have a simple form that I need to work out 3 problems on and hoping its possible and someone can help. The way the form works is my users will be using a barcode scanner to enter books into my database. The book’s ISBN number which is read from the barcode scan is the primary key for the spreadsheet.

Here are the problems, in order of importance:

1. If someone happens to have more than one copy of the same book in the batch they are currently working on, it throws an error due to duplicate primary key. I am trying to figure out a formula that allows for it to simply increment the quantity of the previously listed book by 1 (so for instance the second time someone tries to scan the same ISBN, the row with the previously scanned book would change quantity from 1 to 2, or 2 to 3, etc)

2. Is there a way to set up something within the ‘valid if’ conditions that would allow the field to only accept alpha numeric inputs that are either 10 digits or 13 digits?

3. When I hit save on the form, I currently have it set to automatically restart the form for me to be able to make another entry, but after the first entry the cursor doesnt automatically populate the first field anymore, which causes me to always have to click it with my finger before I can scan a new book entry into the database. When dealing with hundreds of books this slows things down considerably. Is it possible to make the cursor always appear in the first record after saving?

[Brian_Russell]

Also look at this appsheet.com - Repeat Scan - An app that repeatedly scans barcodes Repeat Scan - An app that repeatedly scans barcodes appsheet.com

[Aleksi_Alkio]

Q1) It’s doable, but if two device is adding the same ISBN at the same time, you will get that same dublicate. If this can happen, it’s not a good solution. Q2) You can use something like IN(LEN(TEXT([ISBN])),{10,13}) Q3) It should do that if the first field on the form needs an entry.

[Amir_West]

Thanks @Aleksi_Alkio!

For Q1, there wouldn’t be a possibility of another user entering the same ISBN at the same time, can you please help me to configure an expression that accomplishes this feat as this is currently the biggest showstopper I’m dealing with?

For Q2, I tried the formula suggestion, but what it did was make it so that I could only select 10 or 13 in that field and could no longer enter an ISBN number. What I’d like to accomplish there is for people to be able to scan a bar code and if the number of digits returned from the scan don’t equal either 10 or 13 then they’d receive an invalid entry notification.

For Q3, it looks like from the demos shown by Brian that the desired behavior is what I will get if I set the system to auto save and auto reopen, but right now I can’t set auto save unless and until I workout the Q1 issue because I need to preserve the ability for users to adjust the quantity of books in case of known duplicates and they’d lose that ability if the system automatically saved the record as soon as they scanned…

[Aleksi_Alkio]

Let’s say you want to add a number after ISBN… like 1876398467-1 you need to add an extra column for the serial number and then make a virtual key column like CONCATENATE[ISBN],"-",[SerialNumber]) You can then write an initial value for the serialnumber like

MAX(SELECT(TableName[SerialNumber],[ISBN]=[_THISROW].[ISBN]))+1

[Aleksi_Alkio]

Q2) My mistake… IN(LEN(TEXT([_THIS])),{10,13})

[Amir_West]

@Aleksi_Alkio Oddly that still produces the same behavior as the other when added to my ‘valid if’ section for the ISBN field.

If this is too tricky, is it at least possible to prevent the input string from being 12 characters in length? I can see how to prevent it from being the actual #12, but not sure how to restrict by length of the character string…

[Aleksi_Alkio]

Of course… you need to use AND(TRUE,IN(LEN(TEXT([_THIS])),{10,13})). Otherwise it will give you the list.

[Amir_West]

@Aleksi_Alkio

AWESOME! That script totally worked, now I am all set with my input validation! Thank you so much

[Aleksi_Alkio]

You’re welcome