Datatables and searching last item in column & row

Anvil Q&A
1

Good Evening all,

Looking for some assistance please

I am attempting to build a rolling trade blotter and need to extract the last item in a column

I have found the last row number using the

len(app_tables.my_table.search())

However I now need to identify the last item in the column.

How can I pass the value of ‘len’ back into my query to find the last item in the column?

Thank you

2

Knowing the number of rows doesn’t really help you with any queries.

I don’t know how to interpret this.

Are you looking for the last row entered?

Are you looking for a specific column? If column, what do you mean the “last item in the column”.

What is the structure of your data table?

We need a lot more context to even be able to start to offer advice.

3

Hi - thanks for your input

I have a data table called ‘usd_trade’

usd_trade has a column ‘usd_amount’

I need to find the last value that this column has.

4

So you want to find the last row added to the table, and get the value of the usd_amount column in that row?

You didn’t give us the structure of the data table, so I can’t say specifically how you can do that. But, in general, you would need to use tables.order_by to order the results by a column and then get the last row from the results.

You cannot depend on the order of results if you do not use tables.order_by to enforce an order.

2 Likes
5

If I understand your question, I think I should start by saying that:

  • There is no such thing like “the last item in a column”. Columns are attributes of database rows, they don’t contain items (unless we are talking about simple object columns, but I don’t think this is the case)
  • There is no such thing like the last row in a database, because database tables don’t have an order
  • Getting the last thing means going through all the other things and discard them only to keep the last one. It’s better to reverse the order and get the first thing

You should have a column that sets the order, a timestamp, a number, whatever you need, then search ordering by that column and get the first item.

2 Likes
6

Hi @djosephcousins ! Welcome to the forum!

This is the first thing I thought when reading the description and your comments. As @stefano.menci perfectly described, Columns are not list of objects. You don’t add a value to a Column, you add a row which has a value in determined column.

This makes me think that maybe you’re not that familiar with database structures, so I suggest you read more about it.

And as the others said, when you say “the last one” you need to provide a form to know which is the last. The last… based on what? If you want by order of insertion, then you will need another column of maybe Date and Time where you manually add a datetime.now() value together with your usd_ammount. Then, when you want to get the last inserted, you just grab the first result ordered by revered insertion date.

1 Like
7

Thank you all for your inputs

I realise now that I had not read part of the available notes.

Problem now solved.