Search Results for 'get values from another list'

[Silvestre Kassoka]

Hi Alex,

Background
We have two lists connected through Vlookup , List A and List B. Basically List A is a leave request form and List B keeps records leave days taken and remainder etc.(print screens annexed).

Objective

a)To display 2 columns on New Form from List B which are connected to list A through
Vlookup. Remainder & Leave days to be taken

b) To prevent user from saving the forms if the remainder of leave days is not enough. I tried to create a Javascript code that will basically make calculations based on two columns from list B (leave taken & remainder) and one column from List A (days taken). Basically to say if (leave taken + days taken > remainder ) then an alert is made “no enough leave days etc and the form is not saved (submitted. However this giving an error, that bring me to another question.
It is possible to get Field Values from columns of another list connected through Vlookup.? through getFieldValue?

I hope i was clear.

Attachments:

1. 

   List-A.png
   

2. 

   JS.png
   

3. 

   PreventSave_Rule.png
   

4. 

   Vlookup_List-A.png
   

[Alexander Bautz]

You can use some custom js to read or update values from another list. I’m not 100% sure I understand what you mean though. If the person filling in list A uses a cascading dropdown and selects a Mentor from list B – how should the data in list B be modified? – remove the person from the Mentor field, flag it with a value in a field (like “selected = yes) or delete the item?

Because the cascading dropdown does not retrieve the id of the list item, you would have to use the selected values in a query to get the item. If you can sow me some screenshots of your list a and b (please mask any sensitive data) I can create a code snippet to get you started.

Please note: using custom js in DFFS means any updates in datasheet view will NOT trigger the code.

Alexander

[Paul Lynch]

Thanks! With the help of the debug it became clear it was something to do with the Prefill values in child as it was returning 0 results because the parentID field was not being populated, so no results were appearing.

Now working (sorry changed so many things at once before working I cannot say exactly what I did wrong to help future users).

Below is the final piece of the jigsaw to get this form ready..

jQuery("#dffs_LinkToParentItem input:first").val("click to see more details");

Replace LinkToParentItem with your field internal name.

My steps:
1. My prefill child works (ItemRelURL, and it prefills a relative link which is accurate.

2. This relative link (format /teams/gcoiti/Lists/Parent/DispForm.aspx?ID=8 goes into a multiline of text rich text field. But it does not appear as a link just text. Should I use another column type?

3. This column has internal field name of “Linktoform”

4. I go into the child DFFS settings, and in the custom tab I entered

jQuery("#dffs_Linktoform input:first").val("click to see more details");

5. But still the none clickable Relative URL shows up in the column Linktoform (in list view which is ideally where I want to show it) and child item display form (which is not so important)

Wondering if I could get bit more help to make this work?

[Leonid]

Right, Alex, just a regular Lookup field named “Groups”, with enabled multi-value selection.
The field is a lookup to another list. I’d like to pull not just selected values from the source list, but also selected items’ IDs.

getFieldValue("Groups")

only returns Values.
Wondering if there is a DFFS function to get value pairs or IDs.
I understand that if nothing else, I can run

spjs.utility.queryItems()

passing selected Values in CAML, but that seems too cumbersome just to get IDs.
Please advise.

Attachments:

1. 

   Capture.png
   

[Paul Lynch]

This is an Office 365 E3 licensed SharePoint Online Team Site

I’ve attached the console image (although some object arrays are cut off not sure how important they are)..

Also I pressed F12 on the chart page, whilst in edit chart view. (Not sure if that is relevant).

_________________________________________________________________________________

In order to get this chart ready for me to demo

I’d like to filter the chart to only show the entries in the list where the
“lookup column name” – (internal fieldname) is as below..
“Portal Status” – (Deployed)

This looks up another list called “Chart List”, picks the Title field (internal fieldname is also Title).

3 Title values I wish to show;
Identified
In development
Internal

_________________________________________________________________

Separate question – is there any function in the chart software to rename returned values? E.g. if I were to rename the above “Internal” as something else like “Deprecated”. Or at least rename their values in a legend?

Thanks!

Attachments:

1. 

   charthelp6.png
   

2. 

   charthelp7.png
   

[Patrice]

I cannot get the below custom-js to save to my lookup list. I have tried a few things to trouble-shoot. 1) Permissions are good 2) Thought perhaps when the Display Name is different from FIN it would make a difference so tested with another field with both the same – didn’t make any difference 3) Tried turning isLookupInSelf = false, and 4) tried adding to the lookup list with spjs-lookup which worked just fine. I recently installed v4.4.3.45 but had not put this code into any previous version so I don’t know if it worked before I upgraded. We have SP2013 back end (server) but still SP2010 front end. I tried in a SP2013 evaluation site but kept getting SOAP errors. My code is below and I have attached a screenshot of field and the console error message I received, however I received this error message in Google Chrome but not IE11; new item was not saved in either browser. Any thoughts? Thank you, as always.

spjs.ac.textField({
“applyTo”: “Company”,
“helpText”: “Start typing to search for Company”,
“loadText”: “”,
“listGuid”: “69B37C20-7963-420C-94A1-97F8E9C5DEF9”,
“listBaseUrl”: “/sites/DCO/ACDC/ACDCDev”,
“showField”: “Title”,
“searchFields”: [],
“filterCAML”: “”,
“useREST”: false,
“preloadData”:false,
“filterREST”: “”,
“optionDetailFields”: [],
“optionDetailPrefix”: [],
“enforceUniqueValues”: true,
“rowLimit”: 15,
“listOptionsOnFocus”: false,
“minLengthBeforeSearch”: 3,
“reValidateOnLoad”: false,
“allowAddNew”: true,
“isLookupInSelf”: true,
“addNewAdditionalFields”: [],
“multiselect”: false,
“multiselectSeparator”: “; “,
“orderBy”: {
“fin”: “Title”,
“ascending”: true
},
“setFields”: [],
“debug”: true
});

Attachments:

1. 

   spjs-autocomplete.png
   

[Caron Shimo]

I have recently upgraded and getting a feel for the new version. In my current forms I have the ability to show and hide fields when they are selected which is working. What doesn’t work is if I am in a form and I select one thing and then change the selection to something else, it doesn’t automatically hide the fields that are not needed. it shows both sets of fields for the first selected option then fields for the option it is changed to also.

When Add/Modify/Remove values is selected in Type of Request – Agent Data Groups the following field visible and required
◦Add/Modify/Remove Values from which ADG
◦New/Modify/Remove AD Value
◦MU Number and Name

When New ADG is selected in Type of Request – Agent Data Groups the following field visible and required
◦Name of New ADG
◦New/Modify/Remove AD Value
◦MU Number and Name
◦Business Need for New ADG

If I toggle between the 2 selected values in the old version it would hide the fields not listed as visible and required.. Can someone help me on how this version would allow me to do the same. Do I need to write the rules differently? The user would have to refresh the page to get the correct field if a mistake or another selection was made within the same field

[John Freemont]

Hi.

I’m using SharePoint Online with DFFS and have a requirement to relate one list to another. Unfortunately the user experience requires the use of a multi-line description field from List A to relate to List B but using a Lookup column in List B it isn’t allowed to use a multi-line column.

Can anyone help with how I can create an experience when the user is creating a new item or editing an item in List B where they can see the values in the multi-line column (DESCRIPTION in example below) to select from but the value of a calculated ID column (CALCULATEDID) is stored to link the two together?

e.g.

**LIST A**
TITLE DESCRIPTION CALCULATEDID
Blank Lorem Ipsum…. LISTA-001

**LIST B**
TITLE DESCRIPTION LISTA_RELATIONSHIP LISTA_DESCRIPTION
Blank Lorem Ipsum…. LISTA-001 Lorem Ipsum…

Thanks.

• This topic was modified 3 years, 2 months ago by John Freemont.

[Alexander Bautz]

You can pass the values in the URL like this:

SP.UI.ModalDialog.showModalDialog({
        "url":"/DFFS/Lists/DFFS_TestList/NewForm.aspx?Title=Put your string here&AnotherField=Put the string here",
...
...

Then in the NewForm Custom JS add this code:

var urlTitle = GetUrlKeyValue("Title");
if(urlTitle !== ""){
    setFieldValue("Title",urlTitle);
}
// Repeat for your other fields

PS: Thanks for the beer!

Alexander

[Chris Diltz]

I’m wondering if anyone has a solution that would launch a NewForm from another list in the site collection when a field is either (a) set to a specific value or (b) changes values. Use case: User answers a Yes/No question in parent form and must immediately be prompted to complete a child form (ticket) in a new dialog before moving forward with the current form. They must be prompted as we cannot rely on the user to click a button or navigate out to the child list.

After the ticket(s) is/are created and the parent form completed, you could then use vLookup to tie the forms together.

[Keith Hudson]

//spjs_QueryItems
//retrieve items from a list
//takes an object containg the following parameters:
//listName, query, viewFields
spjs_QueryItems(argObj)

example:
function getMacros(){
var fieldName = “Title”;
var fieldType = “Text”;
var listName = “Foods”;
var targetString = getFieldValue(“Food”);
var qty = getFieldValue(“Qty”);
var res, qb = [], item;
qb.push(“<Where>”);
qb.push(“<Eq>”);
qb.push(“<FieldRef Name='” + fieldName + “‘/><Value Type= ‘” + fieldType + “‘>” + targetString + “</Value>” );
qb.push(“</Eq>”);
qb.push(“</Where>”);
res = spjs_QueryItems({“listName”:listName,”query”:qb.join(“”),”viewFields”:[“PctFat”,”CalTot”,”PctCarb”,”PctProtein”]});
if(res.count > 0){
item = res.items[0];
setFieldValue(“TotCal”,item.CalTot !== null ? item.CalTot*qty: “not set”);
setFieldValue(“PctFat”,item.PctFat !== null ? item.PctFat*100: “not set”);
setFieldValue(“PctCarb”,item.PctCarb !== null ? item.PctCarb*100: “not set”);
setFieldValue(“PctProtein”,item.PctProtein !== null ? item.PctProtein*100: “not set”);
}
}

Forum articles:
get values from another list/
vlookup-from-new-on-separate-list

• This reply was modified 3 years, 6 months ago by Keith Hudson.
• This reply was modified 3 years, 6 months ago by Keith Hudson.

[Sergio Giusti]

Hi Alex

I am trying to use Autocomplete to filter some values with CAML and I cannot get it to work, ill briefly explain what I’m trying to have happen on the form so you have some understanding, then Ill put the current code.

I have a list called Jobs where the code is running, on this list I have a lookup field to an Accounts list and another to an Account Contacts list, I am trying to get the form to offer me two autocomplete fields, when I select the ‘AccountName’ autocomplete field it is completing the first lookup column (Account) for me with the ID selected and also setting another single line of text field to the same ID of the item selected, the second autocomplete field should then show me a filtered list of items from the second autocomplete field ‘Location’ which should only show items from the lookup list that have one its columns (a lookup column called ‘Accounts’) match the ID held in the AccountID field on the form.

Here is my code:

spjs.ac.textField({
	"applyTo":"AccountName",
	"helpText":"Enter the name of the account...",
	"listGuid":"Accounts",
	"listBaseUrl":"/sites/jobs",
	"showField":"Title",
	"enforceUniqueValues":true,
	"rowLimit":15,
	"listOptionsOnFocus":false,
	"reValidateOnLoad":false,
	"orderBy":[{"fin":"Title","ascending":true}],
	"allowAddNew":false,
	"isLookupInSelf":false,
	"filterCAML":"",
	"setFields":[
		{
			"fromFIN":"Title",
			"toFIN":"AccountName",
			"parseFunction":"",
			"skipIfEmpty":false
		},
		{
			"fromFIN":"ID",
			"toFIN":"Account",
			"parseFunction":"",
			"skipIfEmpty":false
		},
		{
			"fromFIN":"ID",
			"toFIN":"AccountID",
			"parseFunction":"",
			"skipIfEmpty":false
		}

	]					
});			

spjs.ac.textField({
	"applyTo":"Location",
	"helpText":"Enter the post code of the account...",
	"listGuid":"Account Contacts",
	"listBaseUrl":"/sites/jobs",
	"showField":"PostCode",
	"enforceUniqueValues":true,
	"rowLimit":15,
	"listOptionsOnFocus":false,
	"reValidateOnLoad":true,
	"orderBy":[{"fin":"PostCode","ascending":true}],
	"allowAddNew":false,
	"isLookupInSelf":false,
	"filterCAML":"<Where><Eq><FieldRef Name='Account' LookupId='TRUE'/><Value Type='Lookup'>' + AccountID + '</Value></Eq></Where>",
	"setFields":[
		{
			"fromFIN":"Title",
			"toFIN":"Location",
			"parseFunction":"",
			"skipIfEmpty":false
		},
		{
			"fromFIN":"ID",
			"toFIN":"AccountLocation",
			"parseFunction":"",
			"skipIfEmpty":false
		}

	]					
});

In between the <Value Type=’Lookup’>’ + AccountID + ‘</Value> tags I have tried the following:

‘ + AccountID + ‘
” + AccountID + ”
‘” + AccountID + “‘
+ AccountID +
+ ‘AccountID’ +
+ “AccountID” +
[AccountID]

Nothing seems to work, is the issue that its only initially filtering the 2nd autocomplete field on page load? if so, is there a way to make the 2nd field recalculate based on when the filter value changes?

Thanks

[DougMcCourt]

Hi all – is there a way to use spsj-utility to get values from another list on the site based on a key value on the current NewForm? eg the user is on the NewForm for an Event (custom list) and I would like to look up 2 fields on another list, and set the corresponding fields on the NewForm? There really isn’t a parent / child relationship between the two lists. If needed I can have a hidden list view added to the NewForm – that list would show exactly one row for the user, and would have fields MyDivision and MyMarket that Id like to use to set the values of Division and Market on the NewForm On Form Load – thanks!

[Alexander Bautz]

I got a question about summing the values from another column in a list connected with a multi lookup column. Here is the request:

I have a Product list that contains columns for product [Title] and the [Install hours] for that product.

On our Orders list I have a look up column where multiple products can be added to an order.

Based on the items added to that order, I would like the Install hours (on the products list) to be summed in another field in the orders list.

Here is a code example to get you started:

function lookupExtraColumnData(){
	var a = [], b = [], c = 0;
	a.push("<Where>");
	a.push("<In>");
	a.push("<FieldRef Name='ID' />");
	a.push("<Values>");
	$("#dffs_LookupFieldName select:last option").each(function(i,o){
		b.push("<Value Type='Number'>"+$(o).val()+"</Value>");
	});
	a.push(b.join(""));
	a.push("</Values>");
	a.push("</In>");
	a.push("</Where>");
	if(b.length > 0){
		var res = spjs.utility.queryItems({"listName":"ProductListDisplayName","query":a.join(""),"viewFields":["InstallHours"]});
		$.each(res.items,function(i,item){
			if(item.InstallHours !== null){
				c += Number(item.InstallHours);
			}
		});
		// set the value in a field
		setFieldValue("SumFieldName",c);
	}
}

Add this function to the custom js textarea in DFFS backend, and call it for example on “save” using the “The form is saved” trigger.

You must change “LookupFieldName” for your lookup field name, the “ProductListDisplayName” with the display name or the GUID of the product list, the field name “InstallHours” with the actual field you want to sum, and the “SumFieldName” with the field to write the sum to.

Please note that the sum is written to a “static” field in the list item, and will not update on already added items if you update the “InstallHours” field in your products.

Hope this can be used as a staring point.

Alexander

[Alexander Bautz]

Hi,
Unfortunately there is no way to query into a list using JavaScript when the user doesn’t have read access. The only option I can think of where this might be possible is to use a SP Designer workflow running with an impersonation step to pull this extra information – but then the user must be able to input a “key” to user in the WF when looking up the correct record to fill in the values in the list item.

Another option could be to create the list with a “difficult” name, and hiding it from browsers in SP Designer (or using this tool). This will not prevent users typing in the correct address to the list accessing it, but it will “obfuscate” it.

Alexander