Retrieving the correct item when filtering a dataset.

Hi guys,

I’m a newbie on this matter, and I feel like I’m making just a “little” mistake that keeps me out of the results I’m looking for.

I have two databases, one of them populated with Team information (from now on we can call this database “team_dbs”) and a second one with the prices that each person of the team offers for a specific type of service (we can call it “prices_session_prof”). The services are always the same (psychology services like: child therapy, adult therapy, assessments and so on), but the prices of each service varies depending on who provides that specific service, i.e, the therapist.
Note: not all employees provide all the services, they are specialised.

Following you can check how both databases look like:

This is team_dbs, which has a referenced field called “Preços Sessão”. This field is referencing the other database I mentioned before: prices_session_prof.

This is the second database: prices_session_prof. Here is where I store the team member price for each specific service (“Areas” column) they provide. This is probably not the best optimisation/ structure for this database, but as I said, I’m new to this and I figured it out the best I could.

On the other hand, the page contains a multiple-filter area with dropdowns in it, and the repeater that comes originally with the Team Member (All) page.

This is the filter area with the multiple dropdowns (the one in the middle “Áreas de Intervenção” is the one which I’m having the problem that I’m gonna detailed in a min. ):

This is how the repeater item looks like when the user make a query by using the filter area:
Note: the only field of this repeater that is linked to a dataset that is linked to the prices_session_prof database is the price (“50” on this example). The rest of textboxes and buttons are linked to the team_dbs database.

I have also applied a filter (in the dataset using Settings option, not code) to the dataset linked to the prices_session_prof database, in order to link the team member with the correct price of the specific service displayed in the textbox when the user search for a specific service. I doubt this is the correct setting, but after some tests, it seems that the repeater displays a price related to that team member. The problem comes when only the first price related to the first service in the database of the whole services that each member provides is retrieved. That means, if I’m looking for “Adultos” (adults) therapy services, the repeater will only show me the team members that provide that service (which is perfect), but not the specific price they have set for that service, just the price of the first row related to that member in the database prices_session_prof.

Following there is an image for visual explanation (this happens with all team members that provides that service, this is just an example):

I believe this may be quite confusing, but the main thing I’m looking for is when using that dropdown, it will retrieve all the team members that provide that specific service and the prices they practice for that service. I have also code for the other filters, which was easier because the data to filter is only contained in one dataset.

If anyone can help me on this I will be eternally grateful, cause at this point I don’t know if it’s related to the filters I can set on the dataset on the editor, some code I need to write down or maybe I have the wrong idea about the whole concept of the structure of the databases and its links between them.

Thank you in advance.

Best regards,

Sergio.

You scenario is quite complex, having many issues and components. Please try to isolate the problem to a minimal scenario so that we can properly evaluate and understand your problem.

Hi Yisrael,

Thanks for the approaching and the quick answer on this. Yes, I believe the problem comes from the logic I’ve applied to the databases performance and architecture, so it’s getting more and more complicated.

What I’m looking for is really shown in the last picture I’ve attached:

I select the option “Adultos” in the dropdown, so the repeater must show me (with the rest of TMs) “Ana Ramirez” as one of the team members that provide the service “Adultos”, and also the price (column “Preço”) she has for that specific service (which in the example is 50€). The problem is that I’m getting always the price of the first element of that team member, so in this example I get 40€ on the repeater , which is the price for a completely different service she offers, not the one that I was looking for.
This happens with all team members, no matter the search is by using the dropdown, it will always retrieve the price stored in the first option/service of that team member and not the correct one.

This is the code I have for the dropdown that I’m using to filter the “Areas” column in the database shown before:

export function dropdown4_change(event) {

 const dropDown_prof_type = $w('#dropdown1').value;
 const dropDown_area_type = $w('#dropdown4').value;
 const dropDown_genre_type = $w('#dropdown2').value;

 const byType = $w('#teamDataset').setFilter(wixData.filter().contains('jobTitle', dropDown_prof_type)
    .contains('areas_interv', dropDown_area_type).contains('gender', dropDown_genre_type));

 const byArea = $w('#dataset1').setFilter(wixData.filter().contains('areas_price', dropDown_area_type));

    $w('#text56').show();
    $w('#text57').show();


    byType
    .then(() => {
        console.log("Ok");
        })
    .catch((err) => {
        console.log(err);
    }); 
 }

I also set a filter for the dataset linked to this database (Filters the Team-1 column with the condition “Includes” connected to the Team Dataset):

With this filter, the repeater achieve to connect the price (stored in one dataset) with the team member (store in the second dataset of the page).

I hope this can be more clear for you to help me, however I’m still trying to find the errors.

Appreciate your help. Thank you.

Hi again Yisrael,

I’m trying to simplify this, as long as I’m not very experienced with JS, but I think I found a workaround which is more simple.

I just want to use a dropdown to search within the Team database, specifically in the column “Preço Sessão” (that contains references to the Prices per Service database) , in order to only retrieve the related price to that service and then show it in a text box. Is this possible?


This is the code I have, but I cannot figure out how to access the price and convert it into string. I’m not sure if the queryReferenced is correctly implemented.

wixData.queryReferenced("Team", dropDown4, "price_session")
6  .then( (results) => {
7    if(results.items.length > 0) {
8      let firstItem = results.items;

        $(#textbox1).text = firstItem.price.toString();
9    } else {
10      
11    }
12  } )
13  .catch( (err) => {
14    let errorMsg = err;
15  } );

I know you guys are quite busy, but any help on this will be great for me. I’m stuck on this for a week!