Forum Academy Marketplace Showcase Pricing Features

Special search: Do I have to add the parent record ID to a list item?

Lets say I have table and one of the records is a list called FOOD LIST.

And I want to find all records where any item in their list is APPLE and bring up that record(s).

Please remember that I am talking about a special SEARCH. I know lists are associated to main records when they are added. But what about the searching for the records based on items in a list that is contained within the record.

Example

RECORD 1
Meal Monday
FOOD LIST: Mango, Rice …
RECORD 2
Meal Tuesday
FOOD LIST: Apple, Rice, Cheese …
RECORD 3
Meal Wednesday
FOOD LIST: Apple, Pasta …

I want to return a list of Meals that have APPLE in the list of foods.

So, when I add a food item for a meal, I need to add the record unique ID of the meal record, so I can later fetch each record using unique IDs found, correct?

Hi @mente12,
I think i assisted another user with doing something very similar to yours on this forum.
See the post below:

Assuming you want to display all records that contain some particular food item in the food list in a repeating group (RG), this is what you should do.
You will set the RG “Type of Data” to Record.
Then RG “Data source”: Do a search for Record > Add new constraint > Food list contains [search item]. Here [search item] is wherever you’re getting the item value from. e.g. if you’re using input box, then it will be input box’s value.
If you look at the search statement it is very close to the english statement you made above, “I want to return a list of Meals that have APPLE in the list of foods.”
Once you have the record pulled to the RG, you can add text element to the RG rows, and display the Meal names as: Current cell’s Record’s Meal name.
Take look the post below and see the demo and editor.

1 Like

Thanks I will try that method again. The reason for my question was that I was unable to do the constraint on the list because the “contains…” did not allow me to enter a value without getting the red error text.

Thanks again, will try…

I just tested and it will not let me do because it shows an error after the “contains…”.

Could it be?
The difference between your example (I checked it out) is that your list only has one field and my list has multiple fields; example FOOD/ COLOR etc.

You have sh-habilities contains VALUE but in my case food_list contains APPLE but I have two fields in food_list FOOD and COLOR so how does it know to search?

Error is SEARCH FOR MEALS: SHOULD BE FOOD LIST BUT NOW IT"S A

Yeah figured out the difference, my list is a list of another type and yours is a list of text. That’s the difference.

Can’t figure out a way to do it.

Since the list is a list of another type then then the expression would be:

If the search is happening via Dropdown of dynamic options or Search box element…

Search for Records
Constraint: Food List contains Input’s Value

If the search is happening with a regular input to type in the food item’s name…

Search for Records with Constraint: Food list contains Search for Food :first item with Constraint: Food Name = Input’s Value

Did what you suggested in the last part and I get only one RECORD back. I have 3 RECORDS where the FOOD LIST contains APPLE. If I delete APPLE from the list belonging to the first record then I get again one item but this time I get the second RECORD.

What I did:
Search for type Records
with Constraint: Food list contains
Search for Food list:first item
with Constraint: Food Name = Input’s Value

Result only one record back

@romanmg upon further inspection, what happens, as explain in the previous post, is that we are using an entire Food List row to compare that logic will only match one of the list items. So the end result is that I get only one RECORD back.

I am not sure what I want can be done in Bubble. Any thoughts?

Hi @mente12,
Yes what you want can easily be done in bubble as i explained. Even if your foodlist contains a list of things you can still do it as @romanmg explained.
I’ve taking the time to do a demo for you:
Demo: https://seanhootstest.bubbleapps.io/version-test/foodrecord?debug_mode=true
Editor: https://bubble.io/page?type=page&name=foodrecord&id=seanhootstest&tab=tabs-1
I show you both cases where the food you’re searching for is form a dynamic input such as dropdown and the case where you enter the name as text. Try it and look at the editor and with the explanations in this thread it should be clear.

Thanks for taking the time for doing this demo. Amazing, many thanks.

After looking at the example and examining your example, I think this is the reason it does not work for me and yours does.

My searchers and yours are the same but the way I add to a list item to the record is different.

What I do is:

  1. Create a new Food row in the Food List and then add result of Step 1 to modify the RECORD I am currently “standing on”. The reason I do this is because the new Food List is complex and has several other items that are unique to the RECORD that it is associated with apart from the food name.

What you do is:

  1. Make changes to a list of records

So in your case you have apple only once in the Food table. In my case I have apple many times. One for every record that it belongs to. Example

record 1 has Apple, Red, ‘Bought at StoreX’, LIKED
record 1 has Apple, Red, ‘Brought at StoreY’, NOT LIKED
record 1 has Banana, Brownish, "Bought a BodegaX’, OK
so forth … record 2 has Banana, Yellow "Bought a BodegaX’ OK

So I want to extract all records with ‘Apple’ but I don’t have a comparison that would search exactly all containing ‘Apple’.

I will search for a work around, so I can do it your way.

Many thanks again @seanhoots and @romanmg

Can you share your editor either publicly here or in a private message so i take a look at.
I’m not sure i’m following how your data is structured.
Alternatively you can just share your exact database structure here.

EDIT:
It doesn’t matter here whether your food table has several other fields. Your search is based on the food name so that is the only relevant field here. I can add several fields to my food table and i wouldn’t have to change anything but it will still work.

I can’t share, you know the typical reason but I can explain.

There is a very important reason for this app to do that. (My app is not really about food at all but the structure of what I am saying is identical, I just changed the theme) Imagine that the owner of the Store where the food was bought is able to look at the record of the meal but only have access food items that were in her store, then the owner could insert a comment in the food item row back to the person that had the meal.

So every row for a meal is not shared with any other RECORD. It is totally unique to that meal but has the fields that are in the food list table.

In your case the apple row exist once and it is shared with every meal that it is added to. Since it exist only once you can use it in a comparing constraint. Does this make sense?

BTW: The structure of both our databases are identical. The different is the workflow approach only.

EDIT: RE: your edit comment above. Yes I understand that. I actually added a field to your example to make sure it was not that and your example still worked. So I discarded that as being the problem.

I think i understand your problem now. Let me see if i can update the demo to reflect my understanding of your requirement

Hi @mente12,
Check out the updated demo. I made a simple change to the data structure which now makes the search even simpler.
I added a field to the food table called record (which is of type Record).
So now instead of searching the Record table i search the Food table for all rows that have the food name = the input name.
Then i get the records fields of these food rows. Finally I use “:unique items” to ensure i dont get any duplicates.

EDIT:
I added the ability to click on the Records returned after the search to be able to view the foodlist in the record for that particular search item.

2 Likes

OK, that’s very cool.

[PLEASE IGNORE MY NONSENSE HERE:
So you are adding to the name field to the food list so you can later find the record using that name?

If the name fields to two different records are the same, there might a problem, correct?

If you look at the tittle of my question, I am talking about using the Unique ID of the record in a similar manner. So is your solution similar to what I was saying originally?

Or did I get all this wrong?

My thinking was since since the food list is already associated by Bubble with the RECORD, maybe there was a bubble way of doing this search without adding the reference field.
TO HERE]

In any case your example really helps because I did not know how to construct the search like you did. Please leave it up so I refer to it in the next day or so.

Thanks again…

UPDATE: I see now that the referring field is a type RECORD. This makes sense now. I got confused with the NAME field use to display in the dropdown.

So you are adding to the name field to the food list so you can later find the record using that name?
NO. I’m not using the name. I’m using the record reference which is a unique id (a foreign key in relational database terminology)

If the name fields to two different records are the same, there might a problem, correct?
See answer above.

If you look at the tittle of my question, I am talking about using the Unique ID of the record in a similar manner. So is your solution similar to what I was saying originally?
Again refer to previous answer. Note that the record field in the food table is of type Record and not text. When you use a Thing as a field in another table Bubble uses the unique id.

1 Like

That makes total sense. You beat me to it (see note above). I realized my mistake.

The picture is clear now. It’s exactly what I was looking for.

Thanks again

You’re welcomed. Glad was able to help. I use these opportunities to learn myself too.

1 Like