Forum Academy Marketplace Showcase Pricing Features

Searching in Related Table

Hello all - I’ve been wrestling with this all day and have not been able to solve it. I hope someone has done something similar as this should be a fairly straight forward routine.

I have a Customers table (primary field is CustomerName) and an Orders table (primary field OrderNumber). They are related through a CustomerOrders field in the Orders table.

I would like to list all the orders for a given customer.

I found a forum topic that used advanced filters and I thought this was the closest to my problem. I’ve set up a repeating group that looks for orders based on a CustomerName that I’ve set with a custom state named customername which is part of the page state customerdetails.

repeatingGroup

Search

ListFilter

This returns nothing in my repeating group.

Any assistance to point me in the right direction would be greatly appreciated.

Be safe
Ray

I think a couple of pictures of your database structure would help here. I wouldn’t use a filter, a constraint on the search would be much faster and more efficient rather than making the end user’s browser do all the computational work filtering a list.

Do a search for orders with constraint of the customer that is set by your custom state.

Hi Brian - thank you. I agree and that was my first thought, but when I ran into troubles I tried the filter option.

Hope these help.

Ray

A bit of advice: It’s good practice to name your data tables in the singular to help reinforce the idea that you’re talking about a single customer, a single order, etc. For example, you have a customer. This customer has a name. This customer has an email address. This customer has a phone number… and so on.

So, your Orders table has a field called CustomerOrders, which is of type Customers. Ack, this is hard to describe with the plurals so I’m going to talk about it as if you’ve already renamed them: Your Order table has a field called Customer which is of data type Customer. So, you can do a search for Orders with a constraint of Customer = your custom state (which you said you were setting to a specific customer somehow). Easy peasy, assuming you’re assigning a customer to each order.

Some might add a field to the Customer data type that is a list of orders, but I don’t recommend that because you don’t know in advance how many orders they may have in the future. Fields that are lists of potentially infinite size are not a good idea, so I like that you’re just assigning a customer to each order and trying to search for your info that way.

1 Like

Hi Brian - good advice on the singlular naming convention. This is my first run at a Bubble app and have made many notes for the next. This is on my list. :slight_smile:

My link to the Customer table is called CustomerOrders - I assume that is the reference you are talking about when you suggest Customer = custom state. This is exactly how I tried to do this early in my trial today. However when I tried to set the constraint the drop down list of options did not include a reference to the CustomerName field.

You mentioned you set a custom state on something. Reference that thing’s custom state.

When the page is loaded I use a url parameter to set the customer name as a custom state. My intention was to search for all orders with that name - matching the CustomerOrders field. It seems so simple and there must be a way - but it feels like I’ve missed something either in my setup or my approach.

In the graphic above I expected the custom state to be an option - but it isn’t on that list.

I have it working right now. In the options list (graphic above) I used the Do a Search for option and searched Customers that matched the custom state value.

solution1

solution2

I’m certain I tried this before - but hey :slight_smile: - shows the value of a good nights sleep and a fresh start…

Will post if this changes as I work through the final display but in any case I appreciate your input and assistance. I did learn from this.

Be safe

1 Like