Hi ,
I have a type called “Products”. This table has products for sale. Each product will have prices that may be in different currencies. For example product A is selling for 50,000 vietnamese dollar. Product B is selling for 150 Yuan. Each product has different categories and subcategories.
I have created a repeating group to list the products. Buyer has a default currency to view products in. For example USD. Repeating group shows all the products in buyer’s base currency after conversion.
When the buyer filters the products, he specifies a minimum price and maximum price range for products searched. These minimum and maximum values are stored in a state.
While setting up the repeating group, Its easy to search products in the data source that match a category. Problem is, once I have done that, how do I filter products where the minimum price is higher than the buyer price in buyers default currency. I will need to create a very long complicated formula that checks the price in each row against the minimum price in the state.
For multicurrency marketplaces i generally recommend a new dataType for Prices, which will a field for price (type number) and a field for currency (ideally an optionset, but a currency dataType would also work), and a field for parent product (type Product).
You can save the list of Prices on a Product.
This will make it much easier to handle filtering by price. Simply run a search for Prices where Currency=User’s default currency, and then reference the Prices’ parents in the Repeating group.
Dealing with multiple filters at once becomes slightly more tedious, as you’ll quickly realise that the filterable fields are on different dataTypes. (Other relational databases get past this by using a Join operator, which I really hope Bubble adds at some point, but for the time being we’ll have live without it.)
Do you want to allow for Multiple filters at once? eg filter by Price AND category at once?
Another solution you could look into is standardising the filterable currency. So this means that even if a user is looking for a product at 50000 vietnamess dollars, you’ll run a function which quickly converts their request into USD (or whichever currency you want to consider default) and filter using that value. This will not be 100% precise if you allow sellers to define the price of their goods for each currency, as a seller might put a lower price in their currency and a higher price in some foreign currencies, and the conversion would not catch that.
The buyer does not search for products that are listed only in his currency. The buyer will simply search for products but the price has to be converted and shown in their currency.
Yes correct. Category is easy since I just place it as a condition in the “Do a Search For”.
This is exactly what I am trying to acheive. How do I do this?
I already have an api integrated along with the conversion rates. I store the values in another table.
My issue is when I’m searching the database of products, how do i convert each rows currency to the base currency and then return only those rows where the value is more than the minimum specified in the buyer currency. I know the conversion formula…i just want to know where to put it.
You can add a field on the database which contains the product’s price in the filtering currency. Run a backend workflow every few days that checks whether a currency has changed more than your tolerance error (say 1-5%). If the currency conversion changed more than your tolerance, update the filtering currency price of all products listed in that currency.
Running a conversion on every row of your database every time a search is made is not feasible.
This is not the only way to do it but thats how I would do it. Multicurrency marketplaces are quite complex and I generally don’t recommend them to clients unless there is a very real need for it.
Hi Nico,
Thanks for your response. I’m sorry I am not able to explain my issue. For the time being, lets assume a simpler use case.
I have a table of products.
I have a price.
I have a category column
I first search for products for a specific category.
Next , I want to multiply each rows price with a specific value “y”. Then, I only want to return rows where this value (i.e. price * y) is greater than another specific value “b”.
Could you help understand how I would acheive this?
Thanks
Raj
Actually I have already acheived this another way. I hide the groups in repeating groups that have a price above a certain value. Please see this screenshot with the minimum price set at 0. This shows all the products. However, when I set the minimum price to “6”, it hides all products where price is under 6. Problem is that the groups don’t collapse.
