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.
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.
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
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
@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?
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:
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:
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.
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.
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.
[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.
