Remove duplicates from connected dropdown options

You can create dropdown lists by connecting your dropdown to your data, as described here .

3shtar wants to do this, but has duplicates in the field they want to connect and wants to know if there’s a way to remove these duplicates so the dropdown options only display one selection per value. The answer is yes, but it takes some code and means you will connect your table using code and not the Connect panel.

The code below runs a query that returns all the the items in a collection. It then creates an object that contains only the data from one field (“Title”). It removes any duplicates from the object and then creates an object with the format: {label:Title, value:Title} which can be assigned to the options property of a dropdown.

To use this example you’ll need to replace:

  • “dishes” with the name of your collection

  • “Title” with whichever field in your collection you want to use to create your dropdown options

  • #selection1” with the ID of your dropdown element
    Note that since you are connecting your table with code, you may need to set up the columns in your table using the columns property so they display the way you want.

import wixData from 'wix-data';

$w.onReady(function () {
	// Run a query that returns all the items in the collection
	wixData.query("dishes")
		// Get the max possible results from the query
		.limit(1000)       
  		.find()
  		.then(results => {
  			// Call the function that creates a list of unique titles
		  	const uniqueTitles = getUniqueTitles(results.items);   
			// Call the function that builds the options list from the unique titles
		  	$w("#selection1").options = buildOptions(uniqueTitles);    
  		});
	// Builds an array from the "Title" field only from each item in 
	// the collection and then removes the duplicates
  	function getUniqueTitles(items) {    
  		// Use the map method to create the titlesOnly object containing all the titles from the query results
    		const titlesOnly = items.map(item => item.title);   
		// Return an array with a list of unique titles
       		return [...new Set(titlesOnly)];
	}
	// Creates an array of objects in the form {label: "label", value: "value"} from the array of titles
	function buildOptions(uniqueList) {   
		return uniqueList.map(curr => {
			// Use the map method to build the options list in the format {label:uniqueTitle, value:uniqueTitle}
			return {label:curr, value:curr};  
		});
	}
});
6 Likes

Hi Jeff,
That is awesome, I was trying to do this for a while. But this was too complex for me …
I got this code along with my adjustments of collection name, field and id of my dropdown but still get an error as per below. And I implemented it in the middle of another coding text.


I guess this has to do with what you mentioned about the column properties of the table but I cannot figure out exactly how to fix this. My table surely shows the Last Name along with some other columns such as first Name, and other info.

Thank you so much, I wish I can fix this issue in my dropdowns

Eric

Hey SGC - Can you send me your site URL? I can’t read the code in your screen cap.

Dear Jeff,

There it is:

QUOTE
import wixData from “wix-data”;

//2. See at which event a member is registered to:
export function lastnamebox_change (event) {
$w(“#tableofevents”).hide();
// Run a query that returns all the items in the collection
wixData.query(“registrationsdataset2”)
// Get the max possible results from the query
.limit(1000)
.find()
.then(results => {
// Call the function that creates a list of unique Last Name and First Names
const uniquelastName = getUniquelastName(results.items);
// Call the function that builds the options list from the unique Last and Last Names
$w(“#lastnamebox”).options = buildOptions(uniquelastName);
});
// Build an array from the “lastName” field only for each item in the collection and then removes the duplicates
function getUniquelastName(items) {
// Use the map method to create the lastNameOnly object containing all the last Names from the query results
const lastNameOnly = items.map(item => item.title);
// Return an array with a list of unique last Names
return […new Set(lastNameOnly)];
}
// Creates an array of objects in the form {label: “label”, value: “value”} from the array of titles
function buildOptions(uniqueList) {
return uniqueList.map(curr => {
{label: curr, value: curr};
// Use the map method to build the options list in the format {label:uniquelastName, value: uniquelastName}
});
//First Name to be automatically filled-in depending on Last Name selected
let lastName = event.target.value;
$w(‘#registrationsdataset2’).setFilter(wixData.filter().eq(‘title’,lastName));
}

export function firstnamebox_change() {
//Once First Name is selected, table with all events appear
let firstName = $w(‘#firstnamebox’).value;
$w(‘#registrationsdataset2’).setFilter(wixData.filter().eq(‘firstName’, firstName));
$w(“#tableofevents”).show();
}
UNQUOTE

My collection is ‘Registrationsdata2’, main dropdown (from which I want to remove duplicates) is ‘lastnamebox’, other dropdown is ‘firstnamebox’ and finally my table to show results is ‘tableofevents’.

BTW, whenever both dropdowns are called, the table appears fine but I cannot run another inquiry since dropdowns are not refreshed. can you see why from this doce?

Thank you so much,
Eric

Hi Eric,
I can drill down further if needed but the first thing I noticed is you’re missing a closing } for this function:

export function lastnamebox_change (event) {
	  $w("#tableofevents").hide();

Try that first and if it’s not enough let me know.

Hi Jeff,

The function which you mention actually is closed here
"…
$w(‘#registrationsdataset2’).setFilter(wixData.filter().eq(‘title’,lastName));
}
"
I have then isolated the different function to make code clearer:

  1. remove duplicates;
  2. filter by last name when last name is selected in the dropdown;
  3. push corresponding first names in second dropdown, filter by selected first name and show final result in table.

I have the same error as per below:

And same as before, I cannot run a second or third inquiry since dropdowns are not refreshed after table appears. Can you see why from here?

Thank you so much for your assistance

Jeff,

I think there was a mistake: wixData.Query(“registrationsdataset2”) [it was a dataset on the page] changed to wixData.Query(“Registrations”) [name of my collection]. Still same error persists.

The page is not visible on the site but you can access it at www.shanghai-gc.com/who-is-registered

BR

Hi Eric,

Coupla things - the error you’re seeing is because you missed the 2nd “return” here (you should remove the extra parenthesis you added):

function buildOptions(uniqueList) {		
        return uniqueList.map(curr => {
	return {label:curr, value:curr};
});

About your dropdowns, the problem is that they are still connected in the Connect to data panels and that is conflicting with the code you have. When you remove those connections everything should work (deselect “Connect dropdown list items”).

BTW- I noticed that your first name dropdown isn’t filtering based on the last name selection. Your firstnamebox_change function needs to look like this:

export function firstnamebox_change() {
	let firstName = $w('#firstnamebox').value;
 	$w('#registrationsdataset2').setFilter(wixData.filter()
 		.eq('firstName', firstName)
 		.eq('title',$w("#lastnamebox").value)
 	);
 	$w("#tableofevents").show();  
}

Let me know how it goes.

Dear Jeff,

  1. Add the return : BINGO!! Thanks. Make sure to add it also into your original post as I guess it is missing …
    Additional question: the dropdown is now sorted as per Date of Creation of each item in the collection. How can I sort it differently (say A->Z)?

  2. Deselect " Connect dropdown list items "): it works for the first dropdown (lastnamebox) but not for the second one (firstnamebox) as nothing appears when I click the dropdown. Nevertheless, if I add same sort of coding to remove duplicates for these first Names, then all corresponding first Names appear once (meaning my filtering code did not work properly).

  3. Your last point BTW : I am not clear and will open a new post on subject as this is not related to duplicates. Note that above points 1 and 2 were tested/tried with my original code.

Thank you so very much,
Eric

Jeff,
3. BTW : I got it!! Makes complete sense.
Still small issues related to 1. and 2.
Rgds,

EDITED: To use the .ascending( ) function in the initial query to sort the query results instead of adding a sort function to the code.

Hey Eric,

Glad it’s all working so far. I’m having fun with this.

  1. Add the .ascending( ) function in the initial query to sort the query results. So the first few lines of your query will look like this:
wixData.query("Registrations") // Get the max possible results from the query 
    .limit(1000) 
    .ascending("title") 
    .find()
  1. I checked and I can see the selection. Make sure the code panel is collapsed so the panel doesn’t shrink.

  1. is perfect. Thank you so much
  2. does not give any value into the dropdown when I remove the '‘Connect dropdown list items’ (that’s why I let it ON). It is removed now, you can test.

Once again, huge thanks
Eric

Hi Jeff,
Sorry for the trouble. I still cannot solve point 2. (nothing in 2nd dropdown if I deselect ‘Connect dropdown list items’). This is the last bug I have before fully publishing the site … I wish you could have a quick look at it.
Thanking you in advance,
Eric

EDITED: To remove the array sort function in the getUniqueLastName and getUniqueFirstName functions and replace them with the .ascending( ) function in the initial query to sort the query results.

Hey Eric,
Here we go…

  1. The 2nd dropdown needs to be completely disconnected. But… We need to do a little more magic to filter it based on the user’s selection in the 1st. Also, if one person appears multiple times, we only want their 1st name to appear once. We’ll then populate the dropdown using code, like we did the 1st.
  2. To do this, we need to run another query on the collection to get all the first names, based on the last name selection. This query needs to run in the lastnamebox_change event handler because at that point we know the user’s last name selection.
  3. Then we remove duplicates and build the options list like we did before. We call those functions also in the lastnamebox_change event handler.

I’m going to show you the complete, modified code here. Note: I removed “buildFirstOptions” and “buildLastOptions” and just made one “buildOptions” function because they were doing the same thing and you don’t need both.

import wixData from "wix-data";

$w.onReady(function () {
	// Run a query that returns all the items in the collection
	wixData.query("Registrations")
		// Get the max possible results from the query
		.limit(1000)
		.ascending("title")
		.find()
		.then(results => {
			// Call the function that creates a list of unique Last Names
			const uniquelastName = getUniquelastName(results.items);
			// Call the function that builds the options list from the unique Last Names
			$w("#lastnamebox").options = buildOptions(uniquelastName);
		});
});

// Build an array from the "lastName" field only for each item in the collection and then removes the duplicates
function getUniquelastName(items) {
	// Use the map method to create the lastNameOnly object containing all the last Names from the query results
	const lastNameOnly = items.map(item => item.title);
	// Return an array with a list of unique last Names
	return [...new Set(lastNameOnly)];
}

// Build an array from the "firstName" field only for each item in the collection and then removes the duplicates
function getUniquefirstName(items) {
	// Use the map method to create the firstNameOnly object containing all the last Names from the query results
	const firstNameOnly = items.map(item => item.firstName);
	// Return an array with a list of unique first Names
	return [...new Set(firstNameOnly)];
}

// Creates an array of objects in the form {label: "label", value: "value"} from the array of titles
function buildOptions(uniqueListLast) {
	return uniqueListLast.map(currLast => {
		// Use the map method to build the options list in the format {label:uniquelastName, value: uniquelastName}
		return {
			label: currLast,
			value: currLast
		};
	});
}

//2. See at which event a member is registered to:
export function lastnamebox_change(event) {
	$w("#tableofevents").hide();
	//First Name to be automatically filled-in depending on Last Name selected
	let lastName = event.target.value;
	$w('#registrationsdataset2').setFilter(wixData.filter()
		.eq('title', lastName)
	);
	wixData.query("Registrations")
		// Get the max possible results from the query
		.limit(1000)
		.eq("title", lastName)
		.ascending("firstName")
		.find()
		.then(results => {

			// Call the function that creates a list of unique first Names
			const uniquefirstName = getUniquefirstName(results.items);

			// Call the function that builds the options list from the unique first Names
			$w("#firstnamebox").options = buildOptions(uniquefirstName);
		});
}

export function firstnamebox_change() {
	let firstName = $w('#firstnamebox').value;
	let lastName = $w("#lastnamebox").value;
	//Once First Name is selected, table with all events appear

	$w('#registrationsdataset2').setFilter(wixData.filter()
		.eq('firstName', firstName)
		.eq('title', lastName)
	);
	$w("#tableofevents").show();
}

Oh dear …!! I never thought these two small dropdowns would induce so many lines of code. Just for the record, my final page has actually four dropdowns, making about 200 lines of code. In the future, this page should have 6 dropdowns. I am already scared about it!
Very big thank to you Jeff, your assistance has been extremely valuable to me.
Eric

i’m having a problem with this topic where the duplicates are being deleted when i’m in preview mode but not once the website is published.

Any thoughts?

Hey jq579,
Can you share your site url?

Hello Jeff. thanks for the tutorial, it’s amazing!
I have a question for my dropdown menu: in my case I have four values for my dropdown filter, for filtering cars by (diesel, gas, electric, hybrid). The filter works well after following this tutorial, But how can I have a value (‘all cars’) and then get all the cars again? In other words, is a kind of reset value for resetting the repeater… Thanks a lot in advance

Daniel, hope this helps:

//array is your clean array after removing duplicates  
array.unshift({
"value": '',
"label": 'All Categories'
});
$w('#dropdown').options=array;

Hey Felipe, I’m trying to do what Daniel is doing as well, trying to get an “all” item in the drop down- where exactly in Jeff’s code would these lines fit in? Thanks