Search database by two fields

Discussion Ask a question
, ,
1

Hi users.

I have a customer form in which I need two input boxes. Let’s call these “Article” and “Code”.

The customer will enter an article number, such as “Baxter1” as well as a customer code, such as “Code1”, and the results shown will be the description, colour, price and image.

I need the results to ONLY show the price that relates to both the Article number input and the Code input. If the code entered is not exactly correct, i.e. no partial matches, then no results will show even if the article number is correct.

I also need the results to not auto-populate when the user is typing. A submit button can be used.

Essentially, I am trying to create a members-only section of my site where procurement staff can log in, enter the article number of their current product and their custom pricing code, and see the best alternative product that my company can offer.

1615×204 9.98 KB

Can anyone assist with the coding of this?

2

Hi :raised_hand_with_fingers_splayed:

You can you’re using a dataset to populate the search results, you need to use the filter() function, and if you’re binding the data via code, you need to use the query() function.

In either ways, you need to import Wix Data API module.

import wixData from 'wix-data';

Define variables:

let articleCode = $w('#articleCodeInput').value;
let code = $w('#codeInput').value;

Method #1: Filter a dataset:

let myFilter = wixData.filter()
    .eq('articleCode', articleCode)
    .eq('code', code)


$w('#dataset').setFilter(myFilter)

Method #2: Refine a query:

let myQuery = wixData.query('collection')
    .eq('articleCode', articleCode)
    .eq('code', code).find()

Hope this helps~!
Ahmad

3

Thanks Ahmad.

I have entered the code as per above using method 2 but when an article code and customer code is entered the dynamic page fields do not update.

Any idea why?

1217×769 52.5 KB

4

You need to put the query inside a function, and call that function inside an onInput() event handler, and you should write some code to update the fields as well, and since you decided to use method 2, you need to disconnect all the involved elements from the dataset.

Create the function:

function search() {
    let articleCode = $w('#articleCodeInput').value;
    let code = $w('#codeInput').value;
    
    wixData.query('collection')
        .eq('articleCode', articleCode)
        .eq('code', code)
        .find()
        .then((result)=> {
            if (result.length > 0) {
                let article = result.items[0];
                
                // Updating the fields
                $w('#articleCode').text = article.articleCode;
                $w('#description').text = article.description;
                $w('#colour').text = article.colour;
                $w('#articleImage').src = article.image;
            } else {
                console.warn('No results found!');
            }
        })
}

You need to change the orange keys to the actual keys in your collection.

5

Thanks for your time Ahmad. I appreciate your help.

So I have this then based on your assistance but it still is not updating any fields when I enter an article code and customer code.

I’m sure there is something very obvious to you that I am not doing! Can you see what?

1792×898 64.7 KB

1383×891 66.5 KB

6

I also see this:

1015×748 23.3 KB

Should I be replacing the term collection in your function with the name of my dataset (#procureDataset)?

7

I have it working now Ahmad. Thanks :grinning:

8

I was about to point at a few mistakes, but thankfully you have it working well :joy::joy: You’re welcome :wink:

9

Thanks. I’m new at this so I appreciate your help.

Can I ask one more thing?

If I only had one input box being codeInput how can I have the query show ALL collection results that contain the matching code entered?

This is so a customer can enter their customer code and see the pricing of all products linked to that code.

10

You can do this by removing the “article code” refinement code from the query.

wixData.query('collection')
        .eq('articleCode', articleCode)
        .eq('code', code)
        .find()
        // The rest ....

By deleting the article code, you’ll get all the items that matches the customer code.

You’re welcome friend, you can tag me in your posts whenever you need help and I’ll try to offer as much help as I can :blush:

11

Thanks Ahmad.

I have created a second function as doing this all on the same page as shown below.

I only get one result back though when I enter the code.

How do I have the screen show multiple results?

2467×1474 156 KB