Forum Documentation Showcase Pricing Learn more

Filtering lists

Banging my head against the wall trying to do a simple filter.

I have 3 tables (things? )

Shops

  • name
  • address

Product

  • name

Inventory

  • shop
  • qty
  • product

I am trying to display a repeating group of the 10 closest Shops within 100 miles of my current location and have that filtered by only shops how have a value in inventory that is > 0 for a particular product. I have a drop down to change products.

I can’t seem to figure out how to filter out the shops that have 0 qty for a particular product. I also can’t seem to get it to work efficiently.

I tried to attack it from a repeating group of shops and inventory (it seems to be easier to tie inventory back to a shop vs the other way around, but it’s a list of shops so that seems the logical thing for the repeating group).

Feels like a simple ‘where’ db call, but I can’t seem to get it to work in the bubble “no code” UI

Hi @jim1,

I would advise you to connect your Shops table with your Inventory tables resulting in the following changed table structure for Shops:

Shops

  • name
  • address
  • Inventory

This way you can fairly easy perform a filter operation (the familiar “where” db call) on all shops you have in your repeating group.

E.g. Do a Search for Shops –> Choose filtered –> Advanced filter (at the bottom) –> This Shop’s Inventory’s Products contains Dropdown selection’s Product.

Hope this helps!
Let me know if something is unclear or you still have trouble setting this up.

All the best,
Julius

Hi @jim1 :slight_smile: It may be faster to do a search for “Inventory”, with the constraints:

Product = Dropdown product’s value
Qty is greater than 0

If you don’t have that many Products being searched through, you could add an Advanced filter such as “Advanced: This Inventory’s Shop’s location is within 100 miles of the Current User’s location”. However, advanced filtering can sometimes slow things down since the filtering occurs after the search. (It’s always best to keep as many constraints in the “Do a search for” as possible.)

Even though you’re already storing the address on the Store data type, you can also add/store the address field onto the Inventory data type, which will allow you to put all three constraints in the search, without using any advanced filter. So the constraints would be:

Do a Search for Inventory
Product = Dropdown product’s value
Qty is greater than 0
Location is within 100 miles of the Current User’s location

Then, the text elements in the repeating group would dynamically display the associated Store’s information using “Current cell’s Inventory’s Store’s…”

Note: If you use this approach, you’d need to add another workflow which updates each Inventory’s “location” field if its associated Store changes its address.

I would recommend the second approach since keeping the constraints in the search will be faster as your app grows, but feel free to let me know if you have any questions :slight_smile:

Thanks for the advice. I did in fact start with inventory as you suggested and pulled the distance on the fly from the store table. The main issue I ran into there was I could not figure out how to sort the results by distance from the user.

The idea of adding address to the inventory table does feel faster to the user, but is it efficient to set up and data intensive? I do not expect shop addresses to change very often if at all ever. But adding an address for every sku (say 1,000 but could be 100x that some day) for every shop (5,000) would mean adding 5 mill address. I can’t even get the system to convert more then about 300 at a time before locking up simply trying to fill addresses 1 time for each shop.

No problem at all! :slight_smile: That’s right – currently the only way to sort by distance from the Current User would be if the location field is on the Thing that is being searched for.

Are you uploading these through the editor via .csv upload? If so, adding an address column to the Inventory type which has the value of its associated Store’s address will not really be different in comparison to uploading without that column. In my experience, I would prefer adding that location field to the Inventory type just to avoid the advanced filter, especially because that search is very large, but it’s completely up to you of course!

I am uploading via csv but ran into this issue Csv import timing out

Ohh, got it! Is that happening when you upload the Stores? I agree with Neerja’s suggestion to store the address as a text first and then run an API workflow on that list to convert them to geographic addresses. Alternatively, you could manually use a searchbox element (set to type: geographic places) to enter each address, then obtain the exact Google formatted address for that location, and replace your spreadsheet values with the Google formatted values before uploading. I think as long as they match Google’s address format exactly that it should work when uploading via CSV.

Then, once the Stores are finished uploading, you can use those same formatted address values in the location field of the Inventory data type. I just tested by uploading 100 addresses and all seemed to work as expected, but if you run into anything I can definitely take a look!

If you start with the many-to-many (in this case the inventory) then you will sooner or later hit the hard bit … how to pick the “10 nearest” by sorting on distance from current user.