Custom JS [Retrive Value from Lookup Table]

[Drew Heggie]

Hi Alex,
I’m hoping you can assist here.
I have a function that performs a calculation based on Field inputs. Part of the calculation requires a value input from a Lookup type field. So, for example, I have lookup table with Countries and an associated Score. I need to retrieve the Score based on the Country Selected:-

Country::Score
UK::3
USA::2
So User selects, say, USA and I need to capture the associated score of 2.
I can capture the selected Country by using:
var a = String(getFieldValue(“Country”));
but don’t know how to capture the associated “Score” value of 2.

Any help/ assistance would be appreciated.
Kind regards,
Drew

[Drew Heggie]

Hi Alex,
Have you had a chance to look at this? Would the solution posted for (this item) be applicable to my requirement?

Kind regards,
Drew

[Alexander Bautz]

Sorry for not replying – I have had some trouble with notifications form the forum.

Yes, you should be able to use the linked post – her is an example based on the code form the linked post:

function getLookupExtraFieldValue(){
	var val = do_getLookupExtraFieldValue();
	alert(val);
}

function do_getLookupExtraFieldValue(){
	var data, lookupValue, b;
	data = spjs.utility.getItemByID({"listName":_spPageContextInfo.pageListId,"id":spjs.dffs.data.thisItemID,"viewFields":["PutYourFieldInternalNameHere"]});
	lookupValue = data["PutYourFieldInternalNameHere"]; 
	if(lookupValue !== null){
		return lookupValue;
	}
}

Change “PutYourFieldInternalNameHere” with you fieldname. Then call “getLookupExtraFieldValue” to test the return value.

Let me know if you have any questions.

Alexander

[Drew Heggie]

Hi Alex,
Excellent, Thanks for this.
It retrieves the value for the selected country. However, when another country is selected then the previous value (score) is returned. Is there a way to re-read the field selection and then return the value associated?
Additional information: I’m launching the function from a onclick button event. So the user selects the country and clicks on the button to receive the score.

As ever, I really appreciate your guidance and support. Thanks again.

[Alexander Bautz]

Hi,
Can you give me some more information about how you plan to use use this? Do you use this as a lookup reference for manually filling in other fields in the form based on the returned value, or do you save the selected value(s) to the list to “use” then later?

The code is designed to pull in the value saved in the field, and not the current selected value when you do the selection so it will need a change to work “live”.

Alexander

[Drew Heggie]

Hi Alex,
The user will select a value (in this case a country) from the lookup table and then click on the Calculate button. The onclick button event will run the Function and return the associated score. In the event that the user selects another country within the same session and clicks on the Calculate button again then, ideally, the function would read the new value (Country) and retrieve the associated score.
The score will then be made available for further calculations.
Thanks for your support.
Regards,
Drew

[Alexander Bautz]

Hi,
This code snippet will pull in the “live” value of a lookup column.

function getLookupExtraFieldValue(){
	var val = do_getLookupExtraFieldLive();
	alert(val);
}

function do_getLookupExtraFieldLive(){
	var res, lookupValue = "";
	res = spjs.utility.getItemByID({"listName":"Customers","id":spjs.$("#dffs_Customer").find("option:selected").val(),"viewFields":["TestValue"]});
	if( res["TestValue"]!== null){
		lookupValue = res["TestValue"]; 
	}
	return lookupValue;
}

You must change the list name “Customers”, the fieldinternal name “Customer” and the fieldinternal name “TestValue”.

Let me know if you have any questions.

Alexander

[Drew Heggie]

Hi Alex, Thanks for the code. Just a couple of clarifications:-

“listName”:”Customers”,
Does this refer to the List Name of the List I am Looking up?

“id”:spjs.$(“#dffs_Customer”).find(“option:selected”).val(),
Do I replace “#dffs_Customer” with the field name the user selects (In my example this would be “Country”, So “#dffs_Counrty”

[Alexander Bautz]

Yes, listName “Customers” is the list the lookup column pulls the information from. This can be the display name or the GUID.

“#dffs_Country” looks correct as long as “Country” is the FieldInternalName of your field.

The field “TestValue” is the FieldInternalName of the field in the list your lookup column pulls information from that you want returned by the function.

Alexander

[Drew Heggie]

Worked perfectly, thanks again.

[Eric Dickerson]

I have tried to set this up and I am not seeing the results I expect:

Desired Results: I am trying to create a bidirectional lookup for Vender Name and Vendor Number. In the new item form I have a lookup column for each (name and number). I want the user to be able to use either of these and for the value to be populated in the other. So if I choose Acme Inc in the Vendor Name lookup, I want the Vendor Code field to show the corresponding vendor code; Acme123.

To do this I expect to have to have a single line text column for each of these columns when I use the “retrive value” js for the value to be reported in. So my lookups are VendorName (no space) and my single line of text columns are Vendor Name (with a space).

When the user chooses the VendorName from the lookup on the form, DFFS rule will hide the VendorCode (lookup) and show the Vendor Code (text) on form change. The Vendor Code field will be populated by the js.

Then vise versa would be true… if user choose the VendorCode lookup, the js would populate the Vendor Name text field, DFFS would hide the VendorName Lookup and show the Vendor Name test field.

What I have created:
– a lookup column to VendorName with a sub-lookup to VenderCode (working fine)
– a lookup column to VendorCode with a sub-lookup to VendorName (working fine)
– Single line of text columns for Vendor Code and Vendor Name (working fine)
– DFFS rules to show & Hide fields on lookup field change (working fine)
– These are the field names according to the Field table tab:
Vendor Code Vendor_x0020_Code SPFieldText
Vendor Name Vendor_x0020_Name0 SPFieldText
VendorCode VendorCode0 SPFieldLookup
VendorName VendorName0 SPFieldLookup
– I have put the following in the Custom JS tab:

function getLookupExtraFieldValue(){
var val = do_getLookupExtraFieldValue();
alert(val);
}

function do_getLookupExtraFieldValue(){
var data, lookupValue, b;
data = spjs.utility.getItemByID({“Purchase%20Request”:_spPageContextInfo.pageListId,”id”:spjs.dffs.data.thisItemID,”viewFields”:[“VendorName0”]});
lookupValue = data[“VendorCode0″];
if(lookupValue !== null){
return lookupValue;
setFieldValue(” Vendor_x0020_Code”,val);
}
}

I’m not sure I put the below line you suggested to set the value in the JS correctly above (as the last line of the script).

setFieldValue(“FieldNameToSet”,val);

– I set up a DFFS Rule as follows:

Rule friendly name:
Vendor Code from VendorName

If this trigger:
Custom JavaScript Function
no operator

This Value:
getLookupExtraFieldValue

Validate on:
Form Load and field change

Issue: I am not seeing the Vendor Code field populate when the Vendor name is set. Note I am only trying to set this up one direction for now, ie; user selects VendorName lookup then Vendor Code is populated by JS.

Once that works I will set it up for the opposite direction.

[Alexander Bautz]

Thanks for the detailed description. Before I look into your current setup, I want to ask if you have looked at the “autocomplete” plugin to DFFS?

To use this solution, create a calculated field in your vendor list that concatenates vendor name and number like this:

=[Vendor name]&" - "&[Vendor number]

Then set up the autocomplete to use this field, and then set the “setFields” part of the ac plugin to fill the other fields.

Will this work for you?

Please let me know if you have any questions.

Best regards,
Alexander

[Eric Dickerson]

That is a cool feature and I like that for some other situations… this customer wants to be able to choose either the vendor name OR the vendor code and have the other field auto populate. As I describe in my post I do not expect to have both vLookups populate… thus I have created substitute fields.

So when a user chooses either vendor name or vendor code in the vLookup fields the JS retrieve value will populate the substitute field for the other vLookup. Then rules can show/hide the appropriate fields and even populate the other substitute field.

Thanks!!

[Alexander Bautz]

Hi,
Sorry for the delay. I’m not sure I completely understand what you plan to do with the values from the lookup column. This code will automatically populate the other lookup when selecting a value in one of them – add it to the “Custom JS” in DFFS backend:

$("#dffs_VendorName").find("select").change(function(){
   $("#dffs_VendorNumber").find("select").val($(this).val());
   var sText = $("#dffs_VendorName").find("option:selected").text();
   setFieldValue("Internal_name_of_alternate_field_to_write_to",sText);
});

$("#dffs_VendorNumber").find("select").change(function(){
   $("#dffs_VendorName").find("select").val($(this).val());
   var sText = $("#dffs_VendorName").find("option:selected").text();
   setFieldValue("Internal_name_of_alternate_field_to_write_to",sText);
});

The “setFieldValue” line can write the value from the lookup column to a single line of text column if you need the value in a text field.

PS: Change “VendorName” and “VendorNumber” to match your field names.

Let me know if you have further questions.

Best regards,
Alexander

• This reply was modified 8 years, 3 months ago by Alexander Bautz.

[Author]

Posts