Forum Academy Marketplace Showcase Pricing Features

Convert Text to Number

I need help with fixing a rookie mistake.

We have a catalog of a few thousand catalog items.

Each catalog item has a standard price.

I created the data field for “standard price” as text.

This is because I wanted it formatted as currency. Silly me.

Some of the “standard price” fields have a “$”.

How do I move that data to a new number field?

Live and learn, much thanks for your help!

Hi @jason1!

Someone smarter than me might have a Bubble-only solution, but moving text to a number field in bulk is tricky, so here’s one path you could take. Export your data to CSV > remove the $ with an Excel or Google Sheet function > upload the file and map the column to a number field. You won’t be able to modify existing records, so these would be new entries in your database (which you could get rid of later), so each number would need some kind of identifier to map back to your existing records (catalog item id?)

Upload them to a temporary data type with 2 fields: number field for the numbers, catalog field to map to the item id

After uploading, create a workflow off a button click: make a change to a list of catalog items > new number field = search for temporary data type: first item’s number, with constraint on the search: catalog item = this catalog item

I’d run this on a small sample to try it out first, but in theory, this should work. Also, I guess I should mention that if you’re still in development and don’t have a problem with just overwriting your data with the CSV upload, you can skip the whole workflow part. Just export > make the fix in a spreadsheet application > upload.


Gaby | Coaching Bubble

I have create a little API to do a convert from text to number.

https://buildingonbubble.com/block/convert-text-to-a-number-1473139645028x680387991794876400

5 Likes

Thanks, Gaby -

This method works but it creates new things instead of updating current things. In our case, we’ll need to keep our current catalog items and update them from here as we have purchase/customer history for these already. I should have made that clear in my initial post.

I think NigelG has posted a solution and I’m about to check it out. I believe his API solution will update current things.

Thanks for taking the time to help!

Hi, Nigel-

Thank you for posting this API. Will this be able to move/convert the text data of one field into another field as number data? Can it move all 1k entries in the database in bulk?

I am looking at it now but am unsure how to implement it into my app. If you could please point me in the right direction on how to implement this, I’d be very grateful!

Thanks again.

Sounds good! Yeah, my path would still allow you to keep your existing records, with the caveat of also creating new ones (with numbers/IDs only) to transfer the data from new to old and then deleting those new, “temp” records. Cumbersome. Hopefully Nigel’s API gets it done more efficiently!

1 Like

Oh, yeah - you’re right. Thanks, didn’t think of that! This may take a few steps but I can see that this can work. Much appreciated!

1 Like

Yes, however you might need to remove the leading $ with a regex as well.

Could you schedule an API call on the list of catalog items, then in the API workflow set a new numeric field by calling the API with the API Connector ?

I think the limit is 1 API call a second on the web task, so that would probably work OK.

3 Likes

Thank you. Looking into it now.

This topic was automatically closed after 70 days. New replies are no longer allowed.