Solved: Why does the barcode scanner add an I to the scann... - Page 2

jdtyler-dev · 10-27-2022 08:54 AM

the barcode scanner keeps adding an I to the beginning when scanning a vin barcode on a vehicle? it is doing this using the default scanner and the scandit 3rd party service.

The validation rule constraining the result to be 17 characters (exact character count of a VIN on a vehicle) allows the tech to delete the I but when you are on strict time constraints and scanning 15 to 20 cars a day it is annoying.

WillowMobileSys

Now that you have this additional specification, it is obvious the barcode and VIN are not the same thing. I would suggest introducing a separate column for the VIN and keep the current column as the VIN Barcode. I would NOT modify the barcode. It may be vital for something later.

When the barcode is scanned, use an expression in the new VIN column to extract the 17-digit VIN. Then use this new column to submit the NHTSC service.

View solution in original post

jdtyler-dev

yes it is indicating it as an import. you need to have a column to insert
the data the scanner extracts the a second column that formats the vin
using the LEFT() and RIGHT() functions. you should also be aware that chevy
includes over 50 word values in their vin codes describing the vehicle
equipment like engine size etc and will need to incorporate into your
formula.

View solution in original post

jdtyler-dev

This is the formula for the column that formats the data extracted from the VIN Barcode using the scanner

View solution in original post

jdtyler-dev

Created By:

jdtyler

Documentation:

Formats the VIN number when using the barcode scanner to remove additional vehicle properties stored in the barcode and cause it not conform to the 17 characters and will cause error if automating vin decoding.

Imports typically include an I in front of the VIN when scanned, GM will include all vehicle properties in the vin and can exceed 100 characters +.

This is proven and tested in production enterprise application currently deploy for my company and is used 50-60 times a day every day.

Dependencies
you must have 2 columns in your table

[VINBarcode]
is the column the raw scan results goes to and has the scanning
option enabled

[VIN]
should be where the formatting should be performed using an app
formula.

```formula

IFS( LEFT([VINBarcode],1) = "I",
RIGHT([VINBarcode], 17),
CONTAINS([VINBarcode], ","),
LEFT([VINBarcode], 17),
CONTAINS([VINBarcode], " "),
LEFT([VINBarcode], 17),
RIGHT([VINBarcode],18) = "G",
LEFT([VINBarcode],17),
NOT(
CONTAINS([VINBarcode], "I")),
[_THISROW].[VINBarcode],
NOT(
CONTAINS([VINBarcode],",")),
[_THISROW].[VINBarcode],
NOT(
CONTAINS([VINBarcode]," ")),
[_THISROW].[VINBarcode],
NOT(
CONTAINS([VINBarcode],"G")),
[_THISROW].[VINBarcode]
)

```

Expressions Used:

- [_THIS]

- {constant} (Number)

- {value_1} = {value_2}

- CONTAINS()

- IF(condition-to-check, value-if-true, value-if-false)

- IFS(condition1, value1, [condition2, value2, ...])

- IN()

- LEFT()

- NOT()

- RIGHT()

VINLog!
You will need a seperate sheet for the RETURN data then referencing it by
using the OUTPUT table and adding a new row to VINLog!.
Then dereference the updated row to pull it into wherever else your use
case needs the decoded vin data

NOTE!
You can use App Script to write a function to make the api call to the
NHTSA then using the "call a script" BOT and using the [VIN] as a variable to
populate in app script when the bot is triggered it will automate decoding
the vin.

You will also need actions to take the OUTPUT data from the
decoded vin and place where needed in your sheets to accommodate
your use case.

APP SCRIPT CODE

function getVehicleInfo(vin) {
 var vinResult = UrlFetchApp.fetch('https://vpic.nhtsa.dot.gov/api/vehicles/decodevinvalues/' + vin + '?format=json');
 var modelyear = JSON.parse(vinResult).Results[0].ModelYear;
 var make = JSON.parse(vinResult).Results[0].Make;
 var model = JSON.parse(vinResult).Results[0].Model;
 var abs = JSON.parse(vinResult).Results[0].ABS;
 var activesafetysysnote = JSON.parse(vinResult).Results[0].ActiveSafetySysNote;
 var adaptivecruisecontrol = JSON.parse(vinResult).Results[0].AdaptiveCruiseControl;
 var adaptivedrivingbeam = JSON.parse(vinResult).Results[0].AdaptiveDrivingBeam;
 var adaptiveheadlights = JSON.parse(vinResult).Results[0].AdaptiveHeadlights;
 var additionalerrortext = JSON.parse(vinResult).Results[0].AdditionalErrorText;
 var airbagloccurtain = JSON.parse(vinResult).Results[0].AirBagLocCurtain;
 var airbaglocfront = JSON.parse(vinResult).Results[0].AirBagLocFront;
 var airbaglocknee = JSON.parse(vinResult).Results[0].AirBagLocKnee;
 var airbaglocseatcushion = JSON.parse(vinResult).Results[0].AirBagLocSeatCushion;
 var airbaglocside = JSON.parse(vinResult).Results[0].AirBagLocSide;
 var automaticpedestrianalertingsound = JSON.parse(vinResult).Results[0].AutomaticPedestrianAlertingSound;
 var autoreversesystem = JSON.parse(vinResult).Results[0].AutoReverseSystem;
 var axleconfiguration = JSON.parse(vinResult).Results[0].AxleConfiguration;
 var axles = JSON.parse(vinResult).Results[0].Axles;
 var batterya = JSON.parse(vinResult).Results[0].BatteryA;
 var batterya_to = JSON.parse(vinResult).Results[0].BatteryA_to;
 var batterycells = JSON.parse(vinResult).Results[0].BatteryCells;
 var batteryinfo = JSON.parse(vinResult).Results[0].BatteryInfo;
 var batterykwh = JSON.parse(vinResult).Results[0].BatteryKWh;
 var batterykwh_to = JSON.parse(vinResult).Results[0].BatteryKWh_to;
 var batterymodules = JSON.parse(vinResult).Results[0].BatteryModules;
 var batterypacks = JSON.parse(vinResult).Results[0].BatteryPacks;
 var batterytype = JSON.parse(vinResult).Results[0].BatteryType;
 var batteryv = JSON.parse(vinResult).Results[0].BatteryV;
 var batteryv_to = JSON.parse(vinResult).Results[0].BatteryV_to;
 var bedlengthin = JSON.parse(vinResult).Results[0].BedLengthIN;
 var bedtype = JSON.parse(vinResult).Results[0].BedType;
 var blindspotintervention = JSON.parse(vinResult).Results[0].BlindSpotIntervention;
 var blindspotmon = JSON.parse(vinResult).Results[0].BlindSpotMon;
 var bodycabtype = JSON.parse(vinResult).Results[0].BodyCabType;
 var bodyclass = JSON.parse(vinResult).Results[0].BodyClass;
 var brakesystemdesc = JSON.parse(vinResult).Results[0].BrakeSystemDesc;
 var brakesystemtype = JSON.parse(vinResult).Results[0].BrakeSystemType;
 var can_aacn = JSON.parse(vinResult).Results[0].CAN_AACN;
 var chargerlevel = JSON.parse(vinResult).Results[0].ChargerLevel;
 var chargerpowerkw = JSON.parse(vinResult).Results[0].ChargerPowerKW;
 var cib = JSON.parse(vinResult).Results[0].CIB;
 var coolingtype = JSON.parse(vinResult).Results[0].CoolingType;
 var curbweightlb = JSON.parse(vinResult).Results[0].CurbWeightLB;
 var daytimerunninglight = JSON.parse(vinResult).Results[0].DaytimeRunningLight;
 var displacementcc = JSON.parse(vinResult).Results[0].DisplacementCC;
 var displacementci = JSON.parse(vinResult).Results[0].DisplacementCI;
 var displacementl = JSON.parse(vinResult).Results[0].DisplacementL;
 var doors = JSON.parse(vinResult).Results[0].Doors;
 var driverassist = JSON.parse(vinResult).Results[0].DriverAssist;
 var drivetype = JSON.parse(vinResult).Results[0].DriveType;
 var dynamicbrakesupport = JSON.parse(vinResult).Results[0].DynamicBrakeSupport;
 var edr = JSON.parse(vinResult).Results[0].EDR;
 var electrificationlevel = JSON.parse(vinResult).Results[0].ElectrificationLevel;
 var engineconfiguration = JSON.parse(vinResult).Results[0].EngineConfiguration;
 var enginecycles = JSON.parse(vinResult).Results[0].EngineCycles;
 var enginecylinders = JSON.parse(vinResult).Results[0].EngineCylinders;
 var enginehp = JSON.parse(vinResult).Results[0].EngineHP;
 var enginehp_to = JSON.parse(vinResult).Results[0].EngineHP_to;
 var enginekw = JSON.parse(vinResult).Results[0].EngineKW;
 var enginemanufacturer = JSON.parse(vinResult).Results[0].EngineManufacturer;
 var entertainmentsystem = JSON.parse(vinResult).Results[0].EntertainmentSystem;
 var errorcode = JSON.parse(vinResult).Results[0].ErrorCode;
 var errortext = JSON.parse(vinResult).Results[0].ErrorText;
 var esc = JSON.parse(vinResult).Results[0].ESC;
 var evdriveunit = JSON.parse(vinResult).Results[0].EVDriveUnit;
 var forwardcollisionwarning = JSON.parse(vinResult).Results[0].ForwardCollisionWarning;
 var fuelinjectiontype = JSON.parse(vinResult).Results[0].FuelInjectionType;
 var fueltypeprimary = JSON.parse(vinResult).Results[0].FuelTypePrimary;
 var fueltypesecondary = JSON.parse(vinResult).Results[0].FuelTypeSecondary;
 var keylessignition = JSON.parse(vinResult).Results[0].KeylessIgnition;
 var lanecenteringassistance = JSON.parse(vinResult).Results[0].LaneCenteringAssistance;
 var lanedeparturewarning = JSON.parse(vinResult).Results[0].LaneDepartureWarning;
 var lanekeepsystem = JSON.parse(vinResult).Results[0].LaneKeepSystem;
 var lowerbeamheadlamplightsource = JSON.parse(vinResult).Results[0].LowerBeamHeadlampLightSource;
 var makeid = JSON.parse(vinResult).Results[0].MakeID;
 var manufacturerid = JSON.parse(vinResult).Results[0].ManufacturerId;
 var modelid = JSON.parse(vinResult).Results[0].ModelID;
 var ncsabodytype = JSON.parse(vinResult).Results[0].NCSABodyType;
 var ncsamake = JSON.parse(vinResult).Results[0].NCSAMake;
 var ncsamapexcapprovedby = JSON.parse(vinResult).Results[0].NCSAMapExcApprovedBy;
 var ncsamapexcapprovedon = JSON.parse(vinResult).Results[0].NCSAMapExcApprovedOn;
 var ncsamappingexception = JSON.parse(vinResult).Results[0].NCSAMappingException;
 var ncsamodel = JSON.parse(vinResult).Results[0].NCSAModel;
 var ncsanote = JSON.parse(vinResult).Results[0].NCSANote;
 var nonlanduse = JSON.parse(vinResult).Results[0].NonLandUse;
 var parkassist = JSON.parse(vinResult).Results[0].ParkAssist;
 var pedestrianautomaticemergencybraking = JSON.parse(vinResult).Results[0].PedestrianAutomaticEmergencyBraking;
 var possiblevalues = JSON.parse(vinResult).Results[0].PossibleValues;
 var pretensioner = JSON.parse(vinResult).Results[0].Pretensioner;
 var rearautomaticemergencybraking = JSON.parse(vinResult).Results[0].RearAutomaticEmergencyBraking;
 var rearcrosstrafficalert = JSON.parse(vinResult).Results[0].RearCrossTrafficAlert;
 var rearvisibilitysystem = JSON.parse(vinResult).Results[0].RearVisibilitySystem;
 var saeautomationlevel = JSON.parse(vinResult).Results[0].SAEAutomationLevel;
 var saeautomationlevel_to = JSON.parse(vinResult).Results[0].SAEAutomationLevel_to;
 var seatbeltsall = JSON.parse(vinResult).Results[0].SeatBeltsAll;
 var seatrows = JSON.parse(vinResult).Results[0].SeatRows;
 var seats = JSON.parse(vinResult).Results[0].Seats;
 var semiautomaticheadlampbeamswitching = JSON.parse(vinResult).Results[0].SemiautomaticHeadlampBeamSwitching;
 var series = JSON.parse(vinResult).Results[0].Series;
 var series2 = JSON.parse(vinResult).Results[0].Series2;
 var steeringlocation = JSON.parse(vinResult).Results[0].SteeringLocation;
 var suggestedvin = JSON.parse(vinResult).Results[0].SuggestedVIN;
 var topspeedmph = JSON.parse(vinResult).Results[0].TopSpeedMPH;
 var tpms = JSON.parse(vinResult).Results[0].TPMS;
 var trackwidth = JSON.parse(vinResult).Results[0].TrackWidth;
 var tractioncontrol = JSON.parse(vinResult).Results[0].TractionControl;
 var transmissionspeeds = JSON.parse(vinResult).Results[0].TransmissionSpeeds;
 var transmissionstyle = JSON.parse(vinResult).Results[0].TransmissionStyle;
 var trim = JSON.parse(vinResult).Results[0].Trim;
 var trim2 = JSON.parse(vinResult).Results[0].Trim2;
 var turbo = JSON.parse(vinResult).Results[0].Turbo;
 var valvetraindesign = JSON.parse(vinResult).Results[0].ValveTrainDesign;
 var vehicledescriptor = JSON.parse(vinResult).Results[0].VehicleDescriptor;
 var vehicletype = JSON.parse(vinResult).Results[0].VehicleType;
 var wheelbaselong = JSON.parse(vinResult).Results[0].WheelBaseLong;
 var wheelbaseshort = JSON.parse(vinResult).Results[0].WheelBaseShort;
 var wheelbasetype = JSON.parse(vinResult).Results[0].WheelBaseType;
 var wheels = JSON.parse(vinResult).Results[0].Wheels;
 var wheelsizefront = JSON.parse(vinResult).Results[0].WheelSizeFront;
 var wheelsizerear = JSON.parse(vinResult).Results[0].WheelSizeRear;
 var windows = JSON.parse(vinResult).Results[0].Windows;
 const decode = [modelyear, make, model, makeid, modelid, manufacturerid, batteryinfo, batterytype, bedtype, bodycabtype, bodyclass, enginecylinders, displacementcc, displacementci, displacementl, doors, drivetype, driverassist, enginecycles, enginekw, entertainmentsystem, fueltypeprimary, seats, series, steeringlocation, transmissionstyle, trim, vehicletype, windows, axles, brakesystemtype, batterycells, bedlengthin, brakesystemdesc, curbweightlb, airbagloccurtain, airbaglocseatcushion, batterya, batteryv, batterykwh, wheelbasetype, seatrows, valvetraindesign, transmissionspeeds, engineconfiguration, airbaglocfront, fueltypesecondary, fuelinjectiontype, airbaglocknee, enginehp, evdriveunit, pretensioner, seatbeltsall, adaptivecruisecontrol, adaptiveheadlights, abs, cib, blindspotmon, ncsabodytype, ncsamake, ncsamodel, esc, tractioncontrol, forwardcollisionwarning, lanedeparturewarning, lanekeepsystem, rearvisibilitysystem, parkassist, airbaglocside, trim2, series2, wheelbaseshort, wheelbaselong, wheels, wheelsizefront, wheelsizerear, coolingtype, enginehp_to, electrificationlevel, chargerlevel, chargerpowerkw, batterya_to, batteryv_to, batterykwh_to, turbo, batterymodules, batterypacks, topspeedmph, suggestedvin, errorcode, possiblevalues, axleconfiguration, enginemanufacturer, additionalerrortext, trackwidth, tpms, activesafetysysnote, dynamicbrakesupport, pedestrianautomaticemergencybraking, autoreversesystem, automaticpedestrianalertingsound, can_aacn, edr, keylessignition, daytimerunninglight, lowerbeamheadlamplightsource, semiautomaticheadlampbeamswitching, adaptivedrivingbeam, saeautomationlevel, saeautomationlevel_to, rearcrosstrafficalert, ncsanote, ncsamappingexception, ncsamapexcapprovedon, ncsamapexcapprovedby, errortext, rearautomaticemergencybraking, blindspotintervention, lanecenteringassistance, nonlanduse, vehicledescriptor];
 Logger.log('API Response is: ' + decode)

 const Output = {    
   modelyear: decode[0],  
   make: decode[1],
   model: decode[2],
   makeid: decode[3],
   modelid: decode[4],
   manufacturerid: decode[5],
   batteryinfo: decode[6],
   batterytype: decode[7],
   bedtype: decode[8],
   bodycabtype: decode[9],
   bodyclass: decode[10],
   enginecylinders: decode[11],
   displacementcc: decode[12],
   displacementci: decode[13],
   displacementl: decode[14],
   doors: decode[15],
   drivetype: decode[16],
   driverassist: decode[17],
   enginecycles: decode[18],
   enginekw: decode[19],
   entertainmentsystem: decode[20],
   fueltypeprimary: decode[21],
   seats: decode[22],
   series: decode[23],
   steeringlocation: decode[24],
   transmissionstyle: decode[25],
   trim: decode[26],
   vehicletype: decode[27],
   windows: decode[28],
   axles: decode[29],
   brakesystemtype: decode[30],
   batterycells: decode[31],
   bedlengthin: decode[32],
   brakesystemdesc: decode[33],
   curbweightlb: decode[34],
   airbagloccurtain: decode[35],
   airbaglocseatcushion: decode[36],
   batterya: decode[37],
   batteryv: decode[38],
   batterykwh: decode[39],
   wheelbasetype: decode[40],
   seatrows: decode[41],
   valvetraindesign: decode[42],
   transmissionspeeds: decode[43],
   engineconfiguration: decode[44],
   airbaglocfront: decode[45],
   fueltypesecondary: decode[46],
   fuelinjectiontype: decode[47],
   airbaglocknee: decode[48],
   enginehp: decode[49],
   evdriveunit: decode[50],
   pretensioner: decode[57],
   seatbeltsall: decode[52],
   adaptivecruisecontrol: decode[53],
   adaptiveheadlights: decode[54],
   abs: decode[55],
   cib: decode[56],
   blindspotmon: decode[57],
   ncsabodytype: decode[58],
   ncsamake: decode[59],
   ncsamodel: decode[60],
   esc: decode[61],
   tractioncontrol: decode[62],
   forwardcollisionwarning: decode[63],
   lanedeparturewarning: decode[64],
   lanekeepsystem: decode[65],
   rearvisibilitysystem: decode[66],
   parkassist: decode[67],
   airbaglocside: decode[68],
   trim2: decode[69],
   series2: decode[70],
   wheelbaseshort: decode[71],
   wheelbaselong: decode[72],
   wheels: decode[73],
   wheelsizefront: decode[74],
   wheelsizerear: decode[75],
   coolingtype: decode[76],
   enginehp_to: decode[77],
   electrificationlevel: decode[78],
   chargerlevel: decode[79],
   chargerpowerkw: decode[80],
   batterya_to: decode[81],
   batteryv_to: decode[82],
   batterykwh_to: decode[83],
   turbo: decode[84],
   batterymodules: decode[85],
   batterypacks: decode[86],
   topspeedmph: decode[87],
   suggestedvin: decode[88],
   errorcode: decode[89],
   possiblevalues: decode[90],
   axleconfiguration: decode[91],
   enginemanufacturer: decode[92],
   additionalerrortext: decode[93],
   trackwidth: decode[94],
   tpms: decode[95],
   activesafetysysnote: decode[96],
   dynamicbrakesupport: decode[97],
   pedestrianautomaticemergencybraking: decode[98],
   autoreversesystem: decode[99],
   automaticpedestrianalertingsound: decode[100],
   can_aacn: decode[101],
   edr: decode[102],
   keylessignition: decode[103],
   daytimerunninglight: decode[104],
   lowerbeamheadlamplightsource: decode[105],
   semiautomaticheadlampbeamswitching: decode[106],
   adaptivedrivingbeam: decode[107],
   saeautomationlevel: decode[108],
   saeautomationlevel_to: decode[109],
   rearcrosstrafficalert: decode[110],
   ncsanote: decode[111],
   ncsamappingexception: decode[112],
   ncsamapexcapprovedon: decode[113],
   ncsamapexcapprovedby: decode[114],
   errortext: decode[115],
   rearautomaticemergencybraking: decode[116],
   blindspotintervention: decode[117],
   lanecenteringassistance: decode[118],
   nonlanduse: decode[119],
   vehicledescriptor: [120]
 };
 Logger.log(Output)

 return Output;
}

View solution in original post

Why does the barcode scanner add an I to the scanned value anytime our techs scan a VIN on a vehicle