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};
});
}
});
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
//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?
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:
remove duplicates;
filter by last name when last name is selected in the dropdown;
push corresponding first names in second dropdown, filter by selected first name and show final result in table.
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.
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();
}
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)?
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).
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.
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…
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.
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.
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
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
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
