Filter using CAML

[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

[Sergio Giusti]

As an update, is there also a way to make the autocomplete field prepopulate with some data? i.e. what I’m really trying to do is to get the 2nd autocomplete field to prepopulate with the PostCode lookup (with the highest ID number in the lookup list) that matches the selection made from the 1st autocomplete lookup, this way my users could enter an account name which would set the actual lookup value of the account field, this in turn gives me the ID number of the account field lookup column in my 2nd list, now if there are 1, 2, 3, 5, 10 etc. entries for that account in the account contacts list, id like to pull back the post code for the last item added into the contacts list where the account matches the one I selected.

Thanks

[Alexander Bautz]

Hi,
In the current version it’s not so easy to have the one filter the other, but I have actually added this functionality in the upcoming release. I’m unfortunately busy as always and will most likely not be able to get it wrapped up until sometime next week. I might post another BETA during the weekend, but this is not 100%.

Alexander

[Sergio Giusti]

ok great, ill wait for the new release, thanks Alex

[Author]

Posts