I’ve made an inventory app where I check in products using QR codes on a form and automatically fill fields such as [inventory id] [SN] [expiration] [Lots] based on the parsed values. The products SN’s are unique. I’ve created formulas in sheets that parse the data fine on the backend. My issue is I’m trying prevent duplicates in app for [SN] using NOT(In([_THIS], Select(Check In[SN],[Log ID]<>[_ThisRow].[Log ID]))) so that it notifies user of duplicate and rejects form. The products are similar but the strings have different formats ie. “CB-05G AZ79DD13A97A AZ79091 2029-03-31” “010081612502167310DGV22A58A21DGV2201817280831” “010081612502175810EDF13B17A21EDF1300717290630”
With the current formula the “CB-05G AZ79DD13A97A AZ79091 2029-03-31” type strings parse into [SN] just fine and tell me if i scan duplicates in app. Its just the others strings like “010081612502167310DGV22A58A21DGV2201817280831” with no apparent delimiter produce no value. I’ve tried to convert my sheets formula to something appsheet understands via the app formula field but I keep getting a blank value in [SN] when I scan the “010081612502167310DGV22A58A21DGV2201817280831” type strings.
The column type is set to Text. I’ve removed the sheets formula for [SN] that works on the backend. This is the formula I came up with for appsheet formula field that isn’t working how i want it to.
IFS(MID([QRCode],1,16)=“0100816125024919”,MID([QRCode],30,8),MID([QRCode],1,16)=“0100816125021673”,MID([QRCode],30,8),MID([QRCode],1,16)=“0100816125024940”,MID([QRCode],30,8),MID([QRCode],1,16)=“0100816125021758”,MID([QRCode],30,8), TRUE,INDEX(SPLIT([QRCode]," "),3,))
for example with string “010081612502167310DGV22A58A21DGV2201817280831” id like to pull out “DGV22019” but i get nothing.
Hopefully my question isn’t too long and I can get some guidance.
Thanks
