Dynamically setting a lookup column from another field

Forms SharePoint Online
1

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, '');
alert(scanValue);
alert(digitsOnly);
fd.field('Employee_x0020_Name').value = digitsOnly ;
}
}

2

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 +"'")
    .get()
    .then(function(items){
        console.log(items[0].Id);
    });

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
3

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