Forum Academy Marketplace Showcase Pricing Features

How to search for "something" that's inside a "list of text" field on my database

Hi people!! Could anyone please help me with this!!

My database “Orders” contains a field called linked (type: list of text).
It contains names (products) and numbers (trace code) separated by a ,
For example: onion 6543, tomato 6321, orange 6555.

I have a track and trace page which looks for all the products that I have used with the same trace code. Let’s say I want to search for all the trace codes 6321.

The problem that i’m facing is that the program sees for example onion 6543 as one thing inside the field and when he looks for 6543 doesn’t return anything back.
Here a piece of my track and trace page …
image


Is there any way do this right ?

Thanks in advance.
Geert (sorry for my english!!)

Hi @gsger

I’ll put something together for you today and share it once finished.
Hopefully you can use it as an example. I suspect your database structure may need to change a little, so for example, if you had both Product and Trace Code tables then it may make things a little easier but bare with me and I’ll come back to you soon!

Paul

Try this:

Feel free to edit it etc.

Add some orders and check the Order table. You’ll notice that the Product and Trace Code tables are also populated and the repeating groups manipulate the data.

If it doesn’t do exactly what you want, then I hope it will at least show another way to do what you’re trying to do.

Hi Paul, thanks for helping!!!

I will try to explain better what I meant. Because my app is more complicated than that.
This is a picture of my database fields:
image
The file “Orders” contains the field “Product” and this can be one or a combination of products. For example it could be “1kg bananas” or “300g apples and bananas”.

Once that “an order” is finished and sent to the client, I will link the products that I used with the trace codes of each product. For example, I sold “300g apples and bananas” and I used apples with trace code 5111 & 5112 and bananas with trace code 5113.
This means that the field “Linked” will be filled as follow: “apples 5111, apples 5112, bananas 5113”. This field contains 3 texts separated by comma’s. Until here, everything works just as expected. The field linked on the database is correctly saved.

The problem that i’m facing at the moment occurs when I want to track & trace all the orders that where used with a specified trace code.
To do this I’ve created a page in bubble called track_trace. This page contains an Input trace field and a repeating group (see my first post for the pictures and info).
Let’s say that on this page I just want to see all the orders that where made with the trace code 5111. So i just type the value 5111 in the input field InputTrace. Then the repeating group will search the field “linked” on the database Orders.
I assume it’s not returning anything because this field contains: “apples 5111, apples 5112, bananas 5113” So when he’s looking for 5111 he just sees “apples 5111” and thinks this is not what i’m looking for (5111) and then goes to “apples 5112” and finally to “bananas 5113”.
I’m almost sure about this because in the past I had on the field linked as follow: “apples, 5111, 5112, bananas 5113” and in this way it used to work. But I had to change it because the list didn’t save the name apples 2 times (because a list of things doesn’t allow repeated elements).

I hope you now understand what I mean! Thanks for spending time helping me!!

This article should help I https://medium.com/@raz_855/building-a-bubble-is-search-filter-db80b8253d13

1 Like

Thanks Patricia for this excellent article!! I’ve learned something new!!

The problem is that my field (Linked) is a list of text and I cannot use the option :truncated on it! It doesn’t appear as option in the list to choose from. :disappointed_relieved:

Still, thank you so much for responding to me!!! I really appreciate it!! But I can use it for other things!!!

Ok, understood. Thanks for the details there. Do you need the content format of your Trace Number input to be ‘integer’? I just wondered if the ‘truncated option’ would appear if the content format was say text for example. I’ll have a play today and try to make it work also.

Ignore what I said above!
Has nothing to do with it!

Hi Paul, I will try to change it to text and check what you suggested.
Indeed my InputTrace is an integer type. This is because I wanted to be sure that the users types only possible values into it. I managed to do that as follow:

image

Thanks again!!!

I just change my InputTrace to text but still doesn’t work. Now I’m sure that truncated doesn’t appears because my field (Linked) is a list of texts. :cry: :cry:

Yep I agree, will try and replicate it

I’m almost there, go and check the editor again, see the second RG
Have to have the input field set to Text though because it compares you’re list of texts to the input text. I think it just needs tweaking.

@gsger Sorry, I’ve been busy since last online here. I learnt something new myself this week that I think will address your search problem. It is not quite as fast as Search&AutoCorrect but it is more powerful and uses vanilla bubble.

I set the data source of the RG based on what the user types into the Input using the typical Do a Search with a :filtered. The filter uses an Advanced Filter and a Regex to search for the content the user types. This approach searches for any length of string but you can use the When condition if you want a minimum number of characters like 2 or 3 or something to be typed before the filtering starts. It also finds the input’s value anywhere in the entry not just the beginning of a word so it is particularly powerful. I use lowercase to make sure I get matches regardless of case.

Screenshot 2020-04-19 at 13.06.34

Screenshot 2020-04-19 at 13.06.48

Screenshot 2020-04-19 at 13.06.57

I’m not certain if it will work for an integer so you will need to try it. If not it will certainly work on the combined text field I mentioned previously. Even if you already have a solution, this might be a helpful one to keep in your arsenal.

1 Like

Thank you Patricia for your help! It didn’t work because like you said … my value is a number.
I just fixed this problem changing the InputTrace into a text field and then I make the search for the whole name of the product and the code.

1 Like