Searching for specific data in a table with multiple columns

Need help Database
1

I need to do a search for a product’s variations inventory. Currently my database looks like this:

Item Name | Description | Price | Variant Name | Total Inventory

I want to write a text field that says “Currently Variant Name has Total Inventory items in stock”, and it should check that the product name is the same as displayed on the page.

The table can look like this at times:
Bucket Hat | Description | 30 | Blue Hat | 10
Bucket Hat | Description | 35 | Red Hat | 4
Dad Hat | Description | 40 | Blue Hat | 6
Dad Hat | Description | 25 | Red Hat | 9

If the current item on the page is “Bucket Hat,” I only want it to display the total inventory for X bucket hat.

For example, if the user picks Blue Hat while on the page for Bucket Hat, it should display: Currently Blue Hat has 10 items in stock

How would I go about doing this? Been searching the forums but haven’t been able to crack this.

2

You can insert dynamic data in the middle of a text field. So in your example, if the ‘blue hat’ and ‘red hat’ are Options, you could create a text field, and type: 'Currently [Insert Dynamic Data → Get an option → Blue Hat] has [Insert Dynamic data → Do a search for: Type: Inventory → Color = Blue:Count].

The exact structure and wording depends on your database layout and what the various data types are labeled, but the example reminds the same: you can include dynamic data from your database directly into the middle of a plain text sentence.

3

So my issue is I need it to validate the item. The table is called “Products,” and basically what I need to do is this:

  1. Find the item in the database (there can be multiple items with the same name)
  2. Once it’s found that item, I need it to find the variant. The variant is provided by an input from the user from a dropdown.
  3. Once it’s found that variant, I need it to display the inventory for that variant

So even if there’s multiple “Bucket Hat” items, for example, I need it to verify that the variant is the same as selected from the dropdown, and then I need it to display the correct inventory for that selection.

Hope this makes more sense now, and I appreciate the help!

4

You should be able to still do this, but what you’ll do instead is when inserting dynamic data, point to the drop-down input’s value as the item to do the search for.

5

Gotcha, that makes sense. How should I go about validating it’s the correct product?

6

That’s where database structure and naming convention come into play - you’ll need to make sure you establish a repeatable process for naming items within your database so you know you’re calling up the right data. That and lots of trial and error (at least it was for me :smiley: ).