How to Filter Dataset, but Count a Field Key not Included in Filter?

Ask the community
,
1

My live website has a page that includes searching a database based on a combination of any four dropdowns. The result of the chosen option displays a total count of items found based on the conditional dropdown options. The code was compiled between a handful of YouTube vids and several forum posts. The conditional search function and the totals returned work perfectly. I need to add an additional count total display, but can’t figure it out.

I need to add a second displayed count based on the return from the conditional dropdown search. The second displayed count needs to be based on the filtered dataset return, but this new second count needs to be of a dataset field not included in the filter. The four dropdowns used in the search are each connected to a field in the database, as you’ll see in the code snippet: “type”, inheritancePattern," “neuropathyType,” and “chrmosomeShort.” The count displays in a text element the result of this filter. The database has a field “geneName” that I need to count based upon the result of this filter. The filter returns a count for “subtypes found.” For example, if a user chooses “Browse All,” the displayed count is “158 Subtypes were Found”. If the user chooses “CMT2,” the count displays “36 Subtypes were Found.”

I need to add a “… Genes were Found,” as a second text element based upon how many “Subtypes were Found.” The "… Genes were Found is a count of database field key “geneName” which is not included in the conditional dropdown filter. I also need to ignore duplicate “geneName” entries when counting so that I’m counting only unique entries. For example, when a user chooses “Browse All” as their search criteria, one count will be “158 Subtypes were Found,” and the count I need to add would be “124 Genes were Found.” And, if a user chooses “CMT1,” one count will show “11 Subtypes were Found,” but the gene count would be “9 Genes were Found” when duplicate entries in the filed “geneName” are ignored. I’ll need to add this same second “genes found” count to a text input search function that also displays “Subtypes Found.”

Each of the four dropdowns run the same code on change, so I’m only sharing the one. The live website is: https://www.expertsincmt.com/cmt-geneticsdatabase . Scroll down to and click on “Begin Search” to open the search UI.

I greatly appreciate any and all help!

Conditional Dropdown Filter Code:

export function TypeCategoryDropdown_change ( event ) {

$w ( "#SubtypesDataset" ). setFilter ( wixData . filter () 
        . eq ( "type" ,  $w ( "#TypeCategoryDropdown" ). value ) 
        . eq ( "inheritancePattern" ,  $w ( "#InheritanceDropdown" ). value ) 
        . eq ( "neuropathyType" ,  $w ( "#NeuropathyTypeDropdown" ). value ) 
        . eq ( "chrmosomeShort" ,  $w ( "#ChromosomeDropdown" ). value )) 

    . then ( results  => { 
        count (); 
    }) 

console . log ( "Dataset is filtered" ); 

function  count ( ) { 

    let  total  =  $w ( '#SubtypesDataset' ). getTotalCount (); 

    //Button Label 
    if  ( total  ===  0 ) { 
        $w ( '#dropdownSearchButton' ). label  =  'No Results' ; 

    } 
    if  ( total  ===  1 ) { 
        $w ( '#dropdownSearchButton' ). label  =  'Show Result' ; 
        $w ( '#dropdownSearchButton' ). enable (); 

    } 
    if  ( total  >  1 ) { 

        $w ( '#dropdownSearchButton' ). label  =  'Show Results' ; 
        $w ( '#dropdownSearchButton' ). enable (); 
    } 

    //Count 
    if  ( total  ===  0 ) { 
        if  ( wixWindow . formFactor  ===  "Mobile"  ||  wixWindow . formFactor  ===  "Tablet" ) { 

            $w ( "#dropdownResults" ). html  = 
                `<span style= "font-family: Lato;  
            color: #457f8c;  
            font-size: 15px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps"> ${ total }  </span> 
            <span style= "font-family: P22 Underground;  
            color: #457f8c;  
            font-size: 17px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps">Subtypes<br>Match Your Search</span>` ; 

        }  **else**  { 
            $w ( '#dropdownResults' ). html  =  `<span style= "font-family: Lato;  
        color: #457f8c;  
        font-size: 20px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps"> ${ total }  </span> 
        <span style= "font-family: P22 Underground;  
        color: #457f8c;  
        font-size: 21px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps">Subtypes Match Your Search</span>` ; 
        } 
        $w ( "#dropdownResults" ). show (); 
        $w ( '#dropdownSearchButton' ). disable (); 
        $w ( '#loadingGIF2' ). hide (); 

    } 

    if  ( total  ===  1 ) { 

        if  ( wixWindow . formFactor  ===  "Mobile"  ||  wixWindow . formFactor  ===  "Tablet" ) { 

            $w ( "#dropdownResults" ). html  = 
                `<span style= "font-family: Lato;  
            color: #457f8c;  
            font-size: 15px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps"> ${ total }  </span> 
            <span style= "font-family: P22 Underground;  
            color: #457f8c;  
            font-size: 17px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps">Subtype Matches Your Search</span>` ; 

        }  **else**  { 
            $w ( '#dropdownResults' ). html  =  `<span style= "font-family: Lato;  
        color: #457f8c;  
        font-size: 20px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps"> ${ total }  </span> 
        <span style= "font-family: P22 Underground;  
        color: #457f8c;  
        font-size: 21px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps">Subtype Matches Your Search</span>` ; 
        } 
        $w ( "#dropdownResults" ). show (); 
        $w ( '#dropdownSearchButton' ). enable (); 
        $w ( '#loadingGIF2' ). hide (); 

    } 

    if  ( total  >  1 ) { 

        if  ( wixWindow . formFactor  ===  "Mobile"  ||  wixWindow . formFactor  ===  "Tablet" ) { 

            $w ( "#dropdownResults" ). html  = 
                `<span style= "font-family: Lato;  
            color: #457f8c;  
            font-size: 15px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps"> ${ total }  </span> 
            <span style= "font-family: P22 Underground;  
            color: #457f8c;  
            font-size: 17px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps">Subtypes<br>Match Your Search</span>` ; 

        }  **else**  { 
            $w ( '#dropdownResults' ). html  =  `<span style= "font-family: Lato;  
        color: #457f8c;  
        font-size: 20px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps"> ${ total }  </span> 
        <span style= "font-family: P22 Underground;  
        color: #457f8c;  
        font-size: 21px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps">Subtypes Match Your Search</span>` ; 
        } 
        $w ( "#dropdownResults" ). show (); 
        $w ( '#dropdownSearchButton' ). enable (); 
        $w ( '#loadingGIF2' ). hide (); 

    } 
}

}

Text Input Search:

export function searchBar_keyPress ( event ) {

let  debounceTimer ; 

if  ( debounceTimer ) { 
    clearTimeout ( debounceTimer ); 
    debounceTimer  =  **undefined** ; 
} 

debounceTimer  =  setTimeout (() => { 
    $w ( "#SubtypesDataset" ). setFilter ( wixData . filter (). eq ( "gene" ,  $w ( '#searchBar' ). value . toUpperCase ())) 

        . then (() => { 
            count (); 
        }) 
},  200 ); 

function  count ( ) { 

    let  total  =  $w ( '#SubtypesDataset' ). getTotalCount (); 

    //Button Label 
    if  ( total  ===  0 ) { 
        $w ( '#geneSearchButton' ). label  =  'No Results' ; 

    } 
    if  ( total  ===  1 ) { 
        $w ( '#geneSearchButton' ). label  =  'Show Result' ; 
        $w ( '#geneSearchButton' ). enable (); 

    } 
    if  ( total  >  1 ) { 

        $w ( '#geneSearchButton' ). label  =  'Show Results' ; 
        $w ( '#geneSearchButton' ). enable (); 
    } 

    if  ( total  ===  0 ) { 
        if  ( wixWindow . formFactor  ===  "Mobile"  ||  wixWindow . formFactor  ===  "Tablet" ) { 

            $w ( '#geneResults' ). html  = 
                `<span style= "font-family: Lato;  
            color: #457f8c;  
            font-size: 15px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps"> ${ total }  </span> 
            <span style= "font-family: P22 Underground;  
            color: #457f8c;  
            font-size: 17px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps">Subtypes<br>Match Your Search</span>` ; 
        }  **else**  { 
            $w ( '#geneResults' ). html  =  `<span style= "font-family: Lato;  
        color: #457f8c;  
        font-size: 20px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps"> ${ total }  </span> 
        <span style= "font-family: P22 Underground;  
        color: #457f8c;  
        font-size: 21px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps">Subtype<br>Matches Your Search</span>` ; 
        } 
        $w ( '#geneResults' ). show (); 
        $w ( '#repeaterStrip' ). collapse (); 
        $w ( '#repeaterStrip, #loadingGIF, #loadingGIF2' ). hide (); 

    } 

    if  ( total  ===  1 ) { 
        if  ( wixWindow . formFactor  ===  "Mobile"  ||  wixWindow . formFactor  ===  "Tablet" ) { 

            $w ( '#geneResults' ). html  = 
                `<span style= "font-family: Lato;  
            color: #457f8c;  
            font-size: 15px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps"> ${ total }  </span> 
            <span style= "font-family: P22 Underground;  
            color: #457f8c;  
            font-size: 17px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps">Subtype<br>Matches Your Search</span>` ; 
        }  **else**  { 
            $w ( '#geneResults' ). html  =  `<span style= "font-family: Lato;  
        color: #457f8c;  
        font-size: 20px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps"> ${ total }  </span> 
        <span style= "font-family: P22 Underground;  
        color: #457f8c;  
        font-size: 21px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps">Subtype Matches Your Search</span>` ; 
        } 
        $w ( '#geneResults' ). show (); 
        $w ( '#geneSearchButton' ). enable (); 
        $w ( '#loadingGIF, #loadingGIF2' ). hide (); 

    } 
    if  ( total  >  1 ) { 
        if  ( wixWindow . formFactor  ===  "Mobile"  ||  wixWindow . formFactor  ===  "Tablet" ) { 

            $w ( '#geneResults' ). html  = 
                `<span style= "font-family: Lato;  
            color: #457f8c;  
            font-size: 15px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps"> ${ total }  </span> 
            <span style= "font-family: P22 Underground;  
            color: #457f8c;  
            font-size: 17px;  
            font-weight: normal;  
            letter-spacing: 1.6px;  
            font-variant: small-caps">Subtypes<br>Match Your Search</span>` ; 
        }  **else**  { 
            $w ( '#geneResults' ). html  =  `<span style= "font-family: Lato;  
        color: #457f8c;  
        font-size: 20px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps"> ${ total }  </span> 
        <span style= "font-family: P22 Underground;  
        color: #457f8c;  
        font-size: 21px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps">Subtypes Match Your Search</span>` ; 
        } 
        $w ( '#geneResults' ). show (); 
        $w ( '#geneSearchButton' ). enable (); 
        $w ( '#loadingGIF, #loadingGIF2' ). hide (); 

}

}

2

You’ll need to query() the data collection directly with the same set of .eq() statements plus your filter for genes however that is defined. Then do a .count() on that. Essentially your Dataset doesn’t contain all the data in your collection at any given time, it loads it dynamically as more data is needed to display.

3

Hi, Anthony! Thanks for your reply!

With your guidance, and although the API reference is invaluable, the only combination of anything that would display anything in the new “gene count” text field (#dropdownGeneCount) is the following snippet. Regardless of where I added the snippet, whether within onReady or within the dropdown onChange event handler, the result was the same. Adding the wixData.aggregate is what generated the text element to display something. Without it, nothing.

I know I’m missing something and it’s probably super simple. My frustration is probably getting the best of me, too.

wixData . query ( “SubtypesDataset” )
. eq ( “type” , $w ( “#TypeCategoryDropdown” ). value )
. eq ( “inheritancePattern” , $w ( “#InheritanceDropdown” ). value )
. eq ( “neuropathyType” , $w ( “#NeuropathyTypeDropdown” ). value )
. eq ( “chrmosomeShort” , $w ( “#ChromosomeDropdown” ). value )
. eq ( “geneName” , $w ( ‘#ChromosomeDropdown, #NeuropathyTypeDropdown, #InheritanceDropdown, TypeCategoryDropdown’ ). value )

wixData . aggregate ( “SubtypesDataset” )
. group ( “geneName” )
. count ()
. run ()
. then (( results ) => {

        let  geneTotal  =  results . items . length ; 

        if  ( geneTotal  >  1 ) { 
            $w ( '#dropdownGeneCount' ). html  =  `<span style= "font-family: Lato;  
        color: #457f8c;  
        font-size: 20px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps"> ${ geneTotal }  </span> 
        <span style= "font-family: P22 Underground;  
        color: #457f8c;  
        font-size: 21px;  
        font-weight: normal;  
        letter-spacing: 1.6px;  
        font-variant: small-caps">Genes Match Your Search</span>` ; 
        } 
        $w ( "#dropdownResults" ). show (); 
        $w ( '#dropdownSearchButton' ). enable (); 
        $w ( '#loadingGIF2' ). hide (); 

    })

I’m working with a test site rather than my live site. I’ve verified the collection name is correct and I’ve verified the field names are correct. I’ve also made sure sandbox is synced to live.

In the screenshot, the combination of dropdown selections return “158 Subtypes…” This is correct for the dropdown options shown. The next line should then return “124 Genes…” It’s stuck at 50. The subtypes count changes as dropdown selections change and remains accurate. The gene count stays at 50, even when the above code snippet is within each of the dropdown onChange event handlers.

1920×786 200 KB

This next screenshot is the collection structure. There’s several dozen fields for each item. The dropdown filter counts the Subtypes field. I’m stuck at how to count the Gene Name field in relation to Subtypes count. As you can see in the screenshot, for example, “PMP22” in the Gene Name field appears twice. But, if the corresponding “CMT1A” and “CMT1E” are responsive to the user’s search inputs (dropdown selections, and there is also a text search input), I need “PMP22” to be counted only once for accuracy of “Gene Count” displayed in the text element. I tried using a .distinct, but the 50 count did not change.

1920×837 207 KB

What am I missing? What am I overlooking? I’m hoping you make me look like a fool. That would be the bees knees.

I greatly appreciate your time and help.