Forum Academy Marketplace Showcase Pricing Features

Database relationship not appearing when searching

I’m having a really hard time getting the data set up to work, hoping I’m missing something obvious. I’ve read other similar posts, but can’t seem to get this working.

Essentially I have the following Data Types:

  • Supplier (Text)
    ** List of units (Unit)
  • Unit (Text)
    ** Supplier (Supplier)
    ** Address
    ** Discount (Discount)
  • Discount (Number)

When I create the above and try to embed the tables, the lookup field isn’t working. So if I want the Unit table to pull in a discount, when I say “Search on the Discount field” nothing comes back.

What am I missing?

Here is what I currently have:

Try structuring it a little differently. If you want to do a search for a Unit “A” that has a Discount, add a field in the Discount table called Unit of type Unit and each time you create a discount put the Unit in that field. Then do a search for Discounts that contain Unit “A”. I added the field into the private app so you can see what that looks like.

I added a Repeating Group to the test app as well to display what I’m talking about. Let me know if you have any other questions.

Thanks Chris - am I thinking about things wrong then? The way I would have phrased it is “Units have a discount”. But it seems like you are saying I should be thinking about it in terms of ‘discounts have a unit’.

Is that the right way to think about it? I ask because the same question will apply for bookings. For instance, a unit has a list of bookings. A booking has a single unit.

Thanks for the help on this. Once I wrap my mind around the right way to think about it, I’ll be cooking with gas :slight_smile:

Haha, yeah. It’s “Discounts have a Unit”. I had the same issue when I first started on here as well. It’s a slightly different way of thinking, but once you get the hang of it it’ll all make sense.

Alright, so to be clear (and hopefully help others who may read this thread).

Units have a supplier.

That means I would put a ‘supplier’ field on my ‘units’ table, and I would make it a type of ‘supplier’. Then on my supplier table I would add a field called ‘units’ and make it a type of ‘list of units’. Doing this should lead to my supplier field having a list of all units that appear in the units table.

EDIT: Also - does this mean the embedded data does not appear on the units table? So if discounts have a unit, should I see that discount on the units table as well as on the discounts table?

Does that sound right?

You’re almost there. You have to take into account if it’s a One-To-Many relationship or a Many-To-One relationship.

  • Let’s say in this app you have a Supplier table. Each row in this table is a new supplier that has a name, address, etc.
  • You also have a list of Units that would be stored in a table called Units. Each row in this table is a separate Unit.
  • Each unit has a supplier, so multiple units can be assigned to the same supplier.

To tie these together you would add a Supplier field of type Supplier to the Unit’s table. Each unit will now have a supplier linked to it.

There’s no need to have the reference in both places. You don’t need a supplier for each unit and also a list of all units for a supplier. Check out this article that walks through what a Relational Database is and how it works. Since we don’t need to get into the technical side of how it works on Bubble it’s the best article I’ve found that explains it simply.

3 Likes

Having a reference in both places can actually be very helpful.

It can make searches quite a bit easier, particularly when you want to do some filtering on both things.

Bubble isn’t necessarily relational , it is more document based. But it really doesn’t matter. Do whatever makes your search simple rather than structure the data in some sort of rigid way.

So maybe you have a repeating group that you are doing a filter on … having another related thing embedded makes it easy to expand the query.

Maybe it is wrong, but when I am working with a particular data type I like to have everything related to that type embeded so that it is easily accessible. It makes getting the answers easier.

2 Likes

Thanks Nigel - so I should be able to put a field in both tables. One as type “supplier” and one as type “Unit”, right?

In doing so, I should be able to perform a search against either in both the app and in the App Data view. Correct?

Yes, that’s right. Your don’t have to do it both ways, and it is entirely dependent on how you want to access the data.

But I find it helps.

1 Like

@NigelG - would you be willing to take a look at my data structure and tell me what I’m missing? I have the relationships built…in this case I have table ‘supplier’ and a supplier can have many ‘units’ (a list of units).

But, how do I get that list of units to appear? No data is appearing and I can’t get it to show up if I do search.

Here is a cloned application to look at how I have it set up:

I appreciate your input.

Hi,

You can’t link to multiple things in the data tab. So you would need to set up admin page to do this if you wanted something quick to link things.

Sorry I don’t understand…you are saying I can’t have multiple relationships in the data tab? I’m trying to figure out how I can show my related data in the tables. So how do I see a supplier’s units, and a unit’s supplier.

No, I was saying you can’t make those changes from the data tab directly. You can with Unit’s Supplier, as that is a single thing.

So I have created a new field, suplier thing with field type Supplier.

If you click on “Change Search Field”

You can change the way you can attach the Supplier in the data tab.

This now means we can do …

And if we had another thing (or list of things) embedded in the Unit, we could do Current Cell’s Unit’s supplierthing’s Owner’s Name … or something like that.

By embedding a thing in another thing means the way we get to it is really simple.

So what does marking something as a list do? I would have assumed in the Supplierthing table, I could now make a field called ‘unit’ that is a ‘list of units’ and I would be able to see all units that have supplierthing value of 8430. Is that not correct?

Well, you need to maintain that list yourself.That is what I just did in the admin panel. You add a unit to the supplier’s Unit list.

Lists are not just parent/child relationships. A list can be a list of any set of things (of that type) you like.

Okay, that makes a little more sense. I would imagine I can also maintain that list by connecting to an API and changing the supplier thing to include a list of results?

Thanks for the help.