Update database collection based on user input (Dropdown)

Hey! I am quite a rookie in coding; I understand the basics but I am far from good at this.

I have set up a page where I want people with specific member role to be able to update data in a data collection:

I have the following dataset:

I have a dropdown that is connected to the data collection; users can choose from any Committee (in ‘title’).

The Goal: I want the chosen item in the dropdown (let’s say it’s “Legal Committee”) to change to “active” in the ‘active’ field. However, when the user changes the dropdown value (let’s say to “Economic and Financial Committee”), the state of “Legal Committee” should be set to “inactive” again and “Economic and Financial Committee” to “active”.

Long story short: the dropdown changes the value of another datafield, while only one value can have an “active” state.

It should be underlined that I want this result to change side-wide (so if another person with the same role visits the page a day later e.g. “Ecnomic and Financial Committee” should still be “active”).


The purpose: The purpose of the dataset is for people with specific roles to decide which committees will be used during a debate.

If any further eloboration is needed, please let me know. I hope someone can help me with this issue.

Well first of all → your DB-FIELD → “active” has not a good ID, why?

Because you have already the state → “active” itself!
Your DB-FIELD should have the following ID → “Activity” (ID: → activity ).
And your two states would be → active / inactive.

You can use the - - > “UPDATE”-function of the wix-data-API for your purposes.

Your steps would be…

  1. query your db first (eq.(“activity”, “active”)
  2. this will find the right active state
  3. you change/update the state to → “inactive” → now all are inactive
  4. the second step would be to filter for → ( eq.(“title”, “Economic and Financial Committee”) - → now you have found the entry which you want to change/update
  5. How to update? → https://www.wix.com/velo/reference/wix-data/update

There are surely better ways of doing that, but this could be a possible solution.

Also think about a → “boolean” - DB-FIELD → true / false

1 Like

Hey @russian-dima ,

Thank you so much for the response. I have been discovering a lot related to the properties of the datasets. However, the full code seems to be really difficult for me to get together.

To simplify the matter for myself; my dropdown now has 3 options

these option are linked to a new dataset called “committeessetup”

The dropbox is linked to the data in the following way:

So far, I have been going through dozens of treats in the forum but and of course I have tried to follow the steps you provided me with: I came as far as this in coding:

import wixData from 'wix-data';

$w.onReady( function() {
  } );

 export function dropdown2_change(event) {
function UPDATE_JUST_ONE_ColumnItem  (parameter) {

 let toUpdateRowData = "active";
    console.log(toUpdateRowData)

    wixData.query("committeessetup")
    .eq("activity", toUpdateRowData)
    .find()
    .then((results) => {
 if (results.items.length > 0) {
 
  let item = results.items[0];
            item.reference2 = "active";
            wixData.update("committeessetup", item);
        }
    })
}
}

I know this code is wrong as it does not include any connection at all to the dropdown #title. However, I don’t really know how to include it as well as the filter you mention.

Again thank you in advance!

const TYPES = await get_UniqueData(userItems, "type"); console.log("All-Types: ", TYPES);

OK! At first i want to recommend you the following. If you are working with code → you perhaps should think about to use pure code and without mixing DATASETs + CODE.

What do i mean? If i would be you → i would generate absolutely EVERYTHING by code, without using DATASET-CONNECTIONS. You can use DATASETS, but generate connections and cummunications between DATASET and PAGE by → CODE, instead of using the option in the properties panel. This will save you headaches in some situations.

A simple example: Populating DROPDOWN by code…
You want to populate your dropdown from a DATA-FIELD of your wished DATABASE?
But you do not know how to do it → because your DATA-FIELD includes also a lot of DUBLICATES → but you want just to see UNIQUE-ENTRIES? And all this done by → CODE?

Take a look onto this example…
All you will need to do in this example, is to set-up 3-VALUES:

  1. Your ITEM-DATA-QUERY: the ITEM-RESULTS of your DATABASE-QUERY
  2. Selecting the right DATA-FIELD: for example → “title” which is always given inside DB.
  3. The ID of your DropDown you want to populate.
create_UniqueDropdown(itemData, "title", "#DropDownIDhere"); 
function create_UniqueDropdown(items, dbfield, dropdown) { 
        const uniqueTitles = getUniqueTitles(items); $w(dropdown).options = buildOptions(uniqueTitles); 
        function getUniqueTitles(items) {const titlesOnly = items.map(item => item[dbfield]); return [...new Set(titlesOnly)];}
        function buildOptions(uniqueList) {return uniqueList.map(curr => {return {label:curr, value:curr};});}
    }

OK! Let’s do it together!

Your DATABASE-ID is? → “committeessetup”

const DATABASE = "committeessetup"

The field you need is? → “title”

const DB_FIELD = "title"

Your query:

const QUERY = wixData.query(DATABASE)

Your DropDown-ID:

const ddCommitee = "#dropdown2"

All the process starts when you select an option from dropdown (DropDown.onChange)

$w(ddCommitee).onChange(()=>{

});

Of course everything inside → onReady()

$w.onReady(()=>{ ... all here inside ... });
import wixData from'wix-data';

const itemData;
//----------- USER-INTERFACE ------------------
const DB_FIELD = "title";
const DATABASE = "committeessetup"
const ddCommitee = "#dropdown2"
//----------- USER-INTERFACE ------------------

$w.onReady(()=>{
   $w(ddCommitee).onChange(()=>{
      create_UniqueDropdown(itemData, DB_FIELD, ddCommitee); 
   });
});
function create_UniqueDropdown(items, dbfield, dropdown) { 
        const uniqueTitles = getUniqueTitles(items); $w(dropdown).options = buildOptions(uniqueTitles); 
        function getUniqueTitles(items) {const titlesOnly = items.map(item => item[dbfield]); return [...new Set(titlesOnly)];}
        function buildOptions(uniqueList) {return uniqueList.map(curr => {return {label:curr, value:curr};});}
    }

OK, till here everything ok and clear? Of course not !!! What do you still missing?
Yes → You are still missing the DATA-Query (itemData), to be able to populate your dropdown.

Lets do first the DATA-QUERY:

let QUERY = wixData.query("committeessetup");
    QUERY.find()
    .then((res)=> {console.log(res);
        if (res.items.length > 0) {let itemData = res.items;}
        else {console.log("No DB-Entries found!");}
    }).catch((err)=>{console.log(err);});

After TRANSFORMATION it into a → RETURNING-FUNCTION…

function get_ItemData() {
    let QUERY = wixData.query("committeessetup");
    QUERY.find()
    .then((res)=> {console.log(res);
        if (res.items.length > 0) {let itemData = res.items; return itemData}
        else {console.log("No DB-Entries found!");}
    }).catch((err)=>{console.log(err);});
}

The COMPLETE-CODE: for creation of an unique DropDown by CODE automaticaly out of your own DATABASE (without any DUBLICATES).

import wixData from'wix-data';

//----------- USER-INTERFACE ------------------
const DB_FIELD = "title";
const DATABASE = "committeessetup"
const ddCommitee = "#dropdown2"
//----------- USER-INTERFACE ------------------

$w.onReady(async()=>{
    //First getting QUERY-DATA (DATABASE-DATA)....getting our itemData...
    let itemData = await get_ItemData(); console.log(itemData);
    //now you have all your DATABASE-DATA inside --> "itemData".

    $w(ddCommitee).onChange(()=>{//this will start the --> creation of a unique DropDown....
        create_UniqueDropdown(itemData, DB_FIELD, ddCommitee); 
    });
});

function get_ItemData() {
    let QUERY = wixData.query("committeessetup");
    QUERY.find()
    .then((res)=> {console.log(res);
        if (res.items.length > 0) {let itemData = res.items; return itemData}
        else {console.log("No DB-Entries found!");}
    }).catch((err)=>{console.log(err);});
}

function create_UniqueDropdown(items, dbfield, dropdown) { 
    const uniqueTitles = getUniqueTitles(items); $w(dropdown).options = buildOptions(uniqueTitles); 
    function getUniqueTitles(items) {const titlesOnly = items.map(item => item[dbfield]); return [...new Set(titlesOnly)];}
    function buildOptions(uniqueList) {return uniqueList.map(curr => {return {label:curr, value:curr};});}
}

I would say → FIRST-STEP done?

Let me know if it worked :stuck_out_tongue_winking_eye:

Question: What is the speciality of this code? What’s it’s magic?

1 Like

Hey @russian-dima

Thank you for the quick response! Also thank you so much for explaining it step by step so I can actually make sense of the code myself. I have gone through the instructions and things are starting to make more sense :')

What I have done is removed any db connections from my dropdown, in order to re-achieve this by means of code. (#dropdown2 = now just in it’s default state).

Afterwards I implemented the full code on the page and tested it with another user account; the dropdown seems to work as expected!

I guess the second step now is to use the toUpdate code. So, if a dropdown item (by “title”) gets selected, the state of “activity” changes to ‘active’. (And if it gets deselected, the state of “activity” changes to ‘inactive’.)

Also, the dropdown should always remain on the latest selected item (e.g. Legal Committee) and this should be the default item when a page is refreshed or opened by another user.

I think I’d now how to do it if it was about the “title” itself, however, I have not yet been able to figure out how can I connect the state of #dropdown2 with the state of “activity” in the DB, while #dropdown2 is only connected to - and showing the “title” db-field.

Thanks in advance!

Yes you stil have some steps to do until you get your END-RESULT.

Step-2: RESET all ACTIVITY when changing value in the → “Title”-Dropdown.

Step-3: Set the current chosen value of title to → “active”.

But first i would say, try to solve it first on your own. Put some time on it. The more you work on it, the more you will understand it. LEARNING BY DOING is the BEST LEARNING METHOD!

1 Like

Thanks @russian-dima

Thank for another reply! Very much true that learnring by doing is the best learning method ;). However, I feel like some guidance in the process is always useful as most internet forum (like an average stackoverflow thread) are just full of perceived ultra-advanced explanations.

I won’t ask you to send the full code, no worries haha.

First of all, with reference to Step 2 as suggested by you: I don’t think resetting the DB field is applicable for my case; once I get this dropdown to work I want to create more duplicates to set multiple items to "active’ at the same time (I want multiple committees to have an active state for dynamic content on other pages) So I’ll try to find another solution to that.

I will spend some more time in trying to figure out the ToUpdate function, too. Nevertheless, for now, I would want to know how I can resolve two errors that I keep getting:

This is my full code now:

import wixData from'wix-data';
import wixLocation from 'wix-location'

//----------- USER-INTERFACE ------------------
const DB_FIELD = "title";
const DATABASE = "committeessetup"
const ddCommitee = "#dropdown2"
//----------- USER-INTERFACE ------------------

$w.onReady(async()=>{
    //First getting QUERY-DATA (DATABASE-DATA)....getting our itemData...
    let itemData = await get_ItemData(); console.log(itemData);
    //now you have all your DATABASE-DATA inside --> "itemData".

    $w(ddCommitee).onChange(()=>{//this will start the --> creation of a unique DropDown....
        create_UniqueDropdown(itemData, DB_FIELD, ddCommitee); 
    });
});

function get_ItemData() {
    let QUERY = wixData.query("committeessetup");
    QUERY.find()
    .then((res)=> {console.log(res);
        if (res.items.length > 0) {let itemData = res.items; return itemData}
        else {console.log("No DB-Entries found!");}
    }).catch((err)=>{console.log(err);});
}


function create_UniqueDropdown(items, dbfield, dropdown) { 
    const uniqueTitles = getUniqueTitles(items); $w(dropdown).options = buildOptions(uniqueTitles); 
    function getUniqueTitles(items) {const titlesOnly = items.map(item => item[dbfield]); return [...new Set(titlesOnly)];}
    function buildOptions(uniqueList) {return uniqueList.map(curr => {return {label:curr, value:curr};});}
}

    let titleid = $w('#dropdown2').value;

    wixData.query("committeessetup")
        .eq("title", titleid)
        .find()
        .then((results) => {
            let Id = results.items[0]._id;
            let recommended = results.items[0].recommended;
            let activityid = results.items[0].activity;

            //adding all the fields of the record 

        
            let toUpdate = {
                "_id": Id,
                "title": titleid,
                "recommended": recommended,
                "activity": "active",
            };
            


            updateActivity(toUpdate);
        });

            let options = {
  "suppressAuth": true,
  "suppressHooks": true
            };

function updateActivity(toUpdate){
    wixData.update("committeessetup", toUpdate, options) 
    .then((results) => { 
        console.log("item was updated sucessfuly"); 
    }) 
    .catch((err)=>{                     
        console.log(err); 
    });
} 

The errors can be found in line 42 (let Id = results.items[0]._id;)
Error = Cannot read properties of undefined (reading ‘_id’)

And when selecting a dropbox item, in line 32 ( function getUniqueTitles(items) {const titlesOnly = items.map(item => item[dbfield]); return […new Set(titlesOnly)];})
Error = Cannot read properties of undefined (reading ‘map’)

Thank you in advance!

Pay attention where you do place which code-parts at which places!

import wixData from'wix-data';
import wixLocation from 'wix-location'

//----------- USER-INTERFACE ------------------
const DB_FIELD = "title";
const DATABASE = "committeessetup"
const ddCommitee = "#dropdown2"
//----------- USER-INTERFACE ------------------

let options = {
  "suppressAuth": true,
  "suppressHooks": true
};

//------START of $w.onReady-Part-----------------------------------------------------------
$w.onReady(async()=>{
    //First getting QUERY-DATA (DATABASE-DATA)....getting our itemData...
    let itemData = await get_ItemData(); console.log(itemData);
    //now you have all your DATABASE-DATA inside --> "itemData".
    $w(ddCommitee).onChange(()=>{//this will start the --> creation of a unique DropDown....
        create_UniqueDropdown(itemData, DB_FIELD, ddCommitee); 
    });
});
//------END of $w.onReady-Part-----------------------------------------------------------



//-------------- FUNCTIONS-------------------------------------------------------------------
function get_ItemData() {
    let QUERY = wixData.query("committeessetup");
    QUERY.find()
    .then((res)=> {console.log(res);
        if (res.items.length > 0) {let itemData = res.items; return itemData}
        else {console.log("No DB-Entries found!");}
    }).catch((err)=>{console.log(err);});
}

function create_UniqueDropdown(items, dbfield, dropdown) { 
    const uniqueTitles = getUniqueTitles(items); $w(dropdown).options = buildOptions(uniqueTitles); 
    function getUniqueTitles(items) {const titlesOnly = items.map(item => item[dbfield]); return [...new Set(titlesOnly)];}
    function buildOptions(uniqueList) {return uniqueList.map(curr => {return {label:curr, value:curr};});}
}

function updateActivity(toUpdate){
    wixData.update("committeessetup", toUpdate, options) 
    .then((results) => { 
        console.log("item was updated sucessfuly"); 
    }) 
    .catch((err)=>{                     
        console.log(err); 
    });
} 
//-------------- FUNCTIONS-------------------------------------------------------------------


// and what about this ???? Where this code should be placed instead ???
    let titleid = $w('#dropdown2').value;

    wixData.query("committeessetup")
        .eq("title", titleid)
        .find()
        .then((results) => {
            let Id = results.items[0]._id;
            let recommended = results.items[0].recommended;
            let activityid = results.items[0].activity;

            //adding all the fields of the record
            let toUpdate = {
                "_id": Id,
                "title": titleid,
                "recommended": recommended,
                "activity": "active",
            };
            updateActivity(toUpdate);
        });
// and what about this ???? Where this code should be placed instead ???

There are 2 possible ways of how to solve your issue.

  1. Converting the mentioned code-part to an own FUNCTION.
  2. Replacing it in the right place of code. (but where is the right place for the mentioned code-part?

And additionaly the question: Do you realy need to query again? Don’t you have already the query? This is why i have already generated a SEPARATE-QUERY

let QUERY = wixData.query("committeessetup");

You can use this already generated QUERY to do your update.
It is not neccessary to query again → it will just increase your loading times.

  1. Declare your query-variable on top of your code as PUBLIC-VARIABLE.
  2. use instead…
wixData.query("committeessetup")
        .eq("title", titleid)

  1. this one…(because the QUERY is already done and existing.
QUERY.eq("title", titleid)
    .find()
    ....continue your code....

And YES! If you want to have multiple activity-states to be set as ACTIVE, then you do not need to reset them before updating. At this point i missunderstood you.

1 Like

Hey @russian-dima

Thank you so much for all your support!

I spent some more hours on the page and established a code that seems to work. I can imagine that it’s far from perfect and better ways can be taken, but it does the job for now:

import wixData from'wix-data';
import wixLocation from 'wix-location'

//----------- USER-INTERFACE ------------------
const DB_FIELD = "title";
const DATABASE = "committeessetup"
const ddCommitee = "#dropdown2"
const reset = "#button1"
//----------- USER-INTERFACE ------------------

let options = {
  "suppressAuth": true,
  "suppressHooks": true
};



//------START of $w.onReady-Part-----------------------------------------------------------
$w.onReady(async()=>{
    ShowText()
    //First getting QUERY-DATA (DATABASE-DATA)....getting our itemData...
    let itemData = await get_ItemData(); console.log(itemData);
    //now you have all your DATABASE-DATA inside --> "itemData".
    $w(ddCommitee).onChange(()=>{//this will start the --> creation of a unique DropDown....
        create_UniqueDropdown(itemData, DB_FIELD, ddCommitee); 
        updateData()
    });

$w(reset).onClick(()=>{
       create_UniqueDropdown(itemData, DB_FIELD, ddCommitee); 
        resetData()
})}


);
//------END of $w.onReady-Part-----------------------------------------------------------



//-------------- FUNCTIONS-------------------------------------------------------------------

function create_UniqueDropdown (items, dbfield, dropdown) {
      function getUniqueTitles(items) {
            const titlesOnly = items.map(item => item[DB_FIELD]);
         return [...new Set(titlesOnly)];
        }
        function buildOptions(uniqueList) {
            return uniqueList.map(curr => {
                return {label:curr, value:curr};
            })}}

function get_ItemData() {
    let QUERY = wixData.query("committeessetup");
    QUERY.find()
    .then((res)=> {console.log(res);
        if (res.items.length > 0) {let itemData = res.items; return itemData}
        else {console.log("No DB-Entries found!");}
    }).catch((err)=>{console.log(err);});
}


function updateActivity(toUpdate){
    wixData.update("committeessetup", toUpdate, options) 
    .then((results) => { 
        console.log("item was updated sucessfuly"); 
    }) 
    .catch((err)=>{                     
        console.log(err); 
    });
} 
//-------------- FUNCTIONS-------------------------------------------------------------------

  
function updateData (){
      let titleid = $w('#dropdown2').value;
      let options = {
  "suppressAuth": true,
  "suppressHooks": true
};
    wixData.query("committeessetup")
        .eq("title", titleid)
        .find()
        .then((results) => {
            let Id = results.items[0]._id;
            let recommended = results.items[0].recommended;
            let activityid = results.items[0].activity;
            let keyid = results.items[0].key;

            //adding all the fields of the record
            let toUpdate = {
                "_id": Id,
                "title": titleid,
                "recommended": recommended,
                "activity": "active",
                "key": "1",
            };
            updateActivity(toUpdate);
                $w('#dropdown2').hide();
                 $w('#button1').show();
            

$w("#text13").text = titleid
$w("#text13").show()

            
        });
}

function resetData (){
      let titleid = $w('#dropdown2').value;
      let options = {
  "suppressAuth": true,
  "suppressHooks": true
};
    wixData.query("committeessetup")
        .eq("title", titleid)
        .find()
        .then((results) => {
            let Id = results.items[0]._id;
            let recommended = results.items[0].recommended;
            let activityid = results.items[0].activity;

            //adding all the fields of the record
            let toUpdate = {
                "_id": Id,
                "title": titleid,
                "recommended": recommended,
                "activity": "inactive",
                "key": "",
            };
            updateActivity(toUpdate);
                $w('#dropdown2').show();
                 $w('#button1').hide();
                 $w("#text13").hide()
                $w("#text13").hide()
        });
}

function ShowText () {
wixData.query(DATABASE)
.eq("activity", "active")
.eq("key", "1")
.find()
.then( (results)=> {
    let resultedtext = results.items[0].title;
        $w("#text13").text = resultedtext;
        $w("#text13").show();
        $w("#dropdown2").hide();
        $w("#dropdown2").value = resultedtext; 
        $w("#button1").show();

})}

Well done! So if it works for you, you were able to manage it by your own (just putting some more time on it). This is how it works!
More invested time → more experience → more success!

Good luck with your Project!

And like always: Do not forget to like it → if you really liked it :wink:

1 Like