Issue related to filtering number/money type fields in tables

Hi,

I am having a table with the following fields

  • Name (text field)
  • Budget (number, mapped to money data type)
  • CPL (number, mapped to money data type)

When I use the filters in the table to show the data for CPL < 50, we expect it to treat the value like numbers, and now show values like 100, 200 etc in the result set.

Is there anyway I can fix this?

These columns are coming straight from the database and are mapped directly

Hey @achou! I don't think this should be happening :sweat_smile:

To double check, is your query returning the Budget as a number? If you open up the State tab in your left panel and expand out your query as much as possible to show the Budget column data, does it show numbers?

Hi,

You are right, it is returning the data as strings.
What do I need to do to return the data as numbers? I have tried

select customers.customer_id as ID, CAST(customer_adwords_history.budget as decimal(10,2)) as Budget from customers

Do I need to use transformers here? I am loading around 4,000 rows here and would like to avoid using transformers as it might have an impact on performance

Hmm! Definitely understand not wanting to add another layer to 4k rows :sweat_smile:

Have you tried:

SELECT *, CAST(budget AS DECIMAL) from table

or

SELECT *, budget::INTEGER from table

or maybe even

SELECT *, budget::NUMBER as budget_as_a_number from table

Let me know if none of those work! :slight_smile:

Hi Victoria,

CAST (customer_adwords_history.budget as INTEGER) as Budget

is working and returning data as integers, but I would like to see this working for decimals as well.

SELECT *, budget::NUMBER as budget_as_a_number from table gives me an error. I am using a Postgres database to make these queries.

SELECT *, CAST(budget AS DECIMAL) from table returns me the data in string format. Attached is the screenshot.

Unfortunately i'm still stuck here, trying to retrieve data as numbers.

I'm not sure if I'm missing something in Postgres or a functionality in retool

Hey @achou! I haven't been able to find a viable solution aside from:

  1. Changing your column data type directly in your SQL table
  2. Adding a transformer to your query like:

return formatDataAsArray(data).map(obj => Object.assign(obj, {budget: obj.budget.parseFloat()}))

If you try the transformer, do you notice a big difference in performance?

Hi Victoria,

Thanks. I think it is a problem with the column type in the database.
I am storing it as numeric, and changing it to double-precision did the trick.

Let me double check with my other use cases, before I mark it as closed

Oh, awesome! Definitely let me know :slight_smile:

Hi

This is working. Thanks a lot.
I'm still not sure why it doesn't work with numeric column, but changing the column type works for me for now.

Hi Victoria. I saw your answer to achou but not sure I understand it. I similarly am struggling with a string=>money related error using retooldb. I'm using a CAST on my SQL which returns a good value. But when I try to use it in a form field It returns an error that the value is a string. Any advice would be appreciated.

queryLaborRate is my query that returns unit_cost as a money. extCostInput is a number field where I want the calculation to be used. Error message in the image below.

Hey @Al_Sambar!

If you hover over your queryLaborRate.data.unit_cost, what does it display? Is it a string of a number? If so, you can convert it to a string to be used in the calculation like:

{{inputQty.value * parseInt(queryLaborRate.data.unit_cost)}}

Does that work for you?

Nope. Attaching a screenshot. Is it possible that issue is because your line has parseInt and the number, being a currency is actually a decimal? And, since the data is stored in retool as a money field, anyways, am I doing something wrong with my query that it is converting it to a string?

Potentially! Would you mind sharing a screenshot of your left panel’s state tab showing what queryLaborRate.data.unit_cost is?