Dynamically setting a lookup column from another field

I have 3 lists on a site Point of sale, inventory and EMPINFO I have fields that employees can scan with a barcode reader and it pulls the inventory item along with the price and item description this works great. I have another lookup field for Employee which I hope to have the employee scan their badge and then pull their Name over to the form. the Employee ID is stored as a 10 digit number in the EMPINFO list which we import from payroll daily. but when the employee scans their badge it comes across as %12345678905? so I can't scan directly into the lookup field. To get around that I created a SLT field where they scan into then grab the digits only. but I cannot set the lookup field with that value the alerts display the proper value. wondering what your solution would be. below is the code I have to convert the value to digits only and one attempt to set the lookup field. I am assuming the issue is the lookup field is looking for the ID from the EMPID List and not the EMPID.

function empbadge()
var scanField = fd.field('Employee_x0020_Number_x0028_Scan');
var nameField = fd.field('Employee_x0020_Name');
if (scanField && nameField)
var scanValue = scanField.value || '';
var digitsOnly = scanValue.replace(/\D/g, '');
fd.field('Employee_x0020_Name').value = digitsOnly ;

Hello @JohnnyThunder,

You need to know the item ID to set the Lookup field value. To get the item ID, you can use PnPjs function:

pnp.sp.web.lists.getByTitle('My List').items
    .filter("FieldName eq '" + digitsOnly +"'")

But this will only work when the Employee ID is unique.

EMPINFO list which we import from payroll daily

Do you rewrite the entire list or add/remove entries as needed? I ask because the first option may cause problems in the future. If an item is deleted form EMPINFO list, the Lookup field in the main list will lose its value.

1 Like

TY this worked, we were planning on rewriting the whole list so appreciate the insight and make sure employees stay in long enough to pull reports needed. Really appreciate your help!

1 Like