Evaluating data in a table for lowest value(s)

App Building
1

Hello,

I've been learning retool these past few months. I'm not a developer /coder - so I've been using this forum to great effect and also with the help of chatGPT.

Aside from my work projects - I decided to try and create a small app for myself, which will evaluate the current energy prices - and then send a signal to my EV charger to charge the car when it is below a certain amount..... because I am on an hourly tariff with my energy supplier, that uses something called 'day ahead pricing'.

I'm a long way from there however; I have got as far as to find an API with the energy prices that I need. I have been able to get that data, and then use an XML parser within retool to provide me that data for the day ahead (today + 1) - and then bring all of this into a table (see screen capture).

So - before I can use my EV charger api to send a signal - I first need to run some analysis on the data. But I really don't know how to do this. ChatGPT is giving me some js code - but I'm not sure it is correct, as I am struggling to get it to work.

My question is - how would I be able to evaluate the data in the table (will always be a maximum of 24 rows - one for each hour of the day) and have it return the 4 lowest value of my calculated field (this is the code for the Cost per Kw/h field - it's a calculation that takes the API figure, and divides by 1000 (Mwh > Kw/h) and then display them in a field, individual fields etc?

This was the kind of code that I was trying but with no luck:

Once I figure this part out, I'm thinking I can use a workflow to check each day, and then push me a message (for now) but will evntually send an API to start the charger at a certain time or something.

const lowestRow = _.minBy(energy_prices.data, row => row['cost per kw/h']);
const lowestPrice = lowestRow['cost per kw/h'];
retool.components.lowest_price.setValue(lowestPrice);

Hope to hear,
thank you
Neil

image
image1920×870 128 KB

2

Hey @maillme!

  1. That's super cool. Really curious to see how this app ends up working for you
  2. If you'd like to grab the min 4 results from your custom column directly, you could do something like this. It uses the .columnMappers property on the table, which is basically the only place that custom columns exist in the "backend" / in an accessible data property.

{{table1.columnMappers['Custom Column 1'].sort((a,b) => a - b).slice(0, 4)}}

Would this work for you?

1 Like
3

Hi Victoria,

Thank you for this example. Unfortunately, when I try to implement it - i get an error, which has to do with the .sort I think:

image
image2882×850 209 KB

Do you know why this might be? I thought maybe because my custom column was a currency field (and not a number) but it still didn't work when i changed to number.......
Neil

4

Hmm it seems like it's saying energy_prices.columnMappers['Cost per kw/h'] is either undefined or not an array that has access to .sort(). Mind opening that up in your left panel and screenshot so we can verify what it's evaluating to?

1 Like
5

Thanks Victoria,

Silly me - I could have just done that!!

'Custom Column 1'

And now i'm getting somewhere - thank you! I appreciate your help with this.

image
image1920×795 111 KB

1 Like
6