Searching a databable based on time/date

What I’m trying to do:
Trying to build a quick timeclock for a friend as a challange. Website records when a button is pushed, and flips a status indicator

Building it first to give broswer access to the datatables (to make things easy), then I’ll redo it with server code :slight_smile:

I build a two column data table with “in” or “out” in one column, with date/time in a second.

I filled the table with some dummy data just to learn/see how to search it properly.

Step one is to search the data table for the last row, and see if that last row is an “in” or an “out”.

But, being new to python, I’m having trouble getting this
What I’ve tried and what’s not working:

Code Sample:
From the tutorioals and searching I came up with

InOut = [r['In_out'] for r in app_tables.log.search()]
    self.text_box_2.text = InOut

If I print InOut into the >_ output, I get the whole list (I think).

This is just giving me all of the rows…

I would like to either:
– go through the whole list to find the last one
or
– search for the latest time, then see if the column next to that is “in” or “out”.

(help…)
Clone link:
share a copy of your app

Welcome to the forum!

Getting the most recent entry is pretty straightforward, and involves two concepts.

One is asking Anvil to order the results. For example, if your datetime field is when, you could do:

app_tables.log.search(tables.order_by('when', ascending=False))

That gives you the list from the latest date to the earliest date.

The second part is that those results act like a Python list. You know that the most recent date is in the first element of the results, so you can get it like getting the first element of a Python list:

result = app_tables.log.search(tables.order_by('when', ascending=False))
latest = result[0]

That gives you a row, which you can then work with as normal:

self.text_box_2.text = latest['In_out']
1 Like

Thank you @jshaffstall ! I will work to impliment it into my code: it makes sense and should work!

This is the “hint” (well, answer) I needed! I did look for something like a tables.order_by() method, which is tailor made to do what I want… but I had trouble finding that in the docs and a duck duck go search. That method will go into the notebook as a VERY useful thing :slight_smile: Many thanks again.

A question: can the sort() or sorted() methods also be used?

hey @jason Welcome to the forum! :wave:

You probably want to search the docs, between the docs, the old forum posts here found by searching, and the really quick responses you will get from us on the forum, you should be able to get the answers to almost anything Anvil related.
If you are stuck on a concept the tutorials are good too. I saw in the “Introduce Yourself” thread you have/had some basic C/C++ computer science background, and most of the Anvil concepts are python concepts and so very simple basic level CS ideas.

docs_example

1 Like

Thanks: yeah: I see it now in the docs… nothing like being frustrated to not see what is right in front of you!

Glad to see the community is accepting and patient with new people (and questions).

My C/C++ training was in the late 90’s: but…I’m sure it will come back to me. The purpose I listed in my introduction should be motivation enough to relearn what is needed. The 90’s also promised a internet that would liberate humanity… I think we are in the age where tools like Anvil can throw a greater net to more users, realizing that goal and giving everyone a printing press that also does calculus haha.

(philosophy talk over :slight_smile: )

Thanks again. Off to code.

1 Like

Yes, but don’t. Using sort() or sorted() forces Python to iterate over all the data table entries, where using the search iterator like a list allows it to more efficiently represent the entries (e.g. the search iterator returns the first page of results, getting more pages only if needed). Allowing the database to do the sorting (or really any other operation) is more efficient than trying to do it in Python.

1 Like

Thanks. Makes imitation a better way to get “good” at this stuff: not just, “learn python”, then do this.
More going on under the hood. Will rework examples and ask questions. Thanks again.

The differences are more between desktop applications and database backed web apps. Once you put a database into the mix, some of the standard desktop techniques change up, so the database can be efficient.

The Anvil database doesn’t quite expose everything that you’d expect in a standard relational database, so there will be times in an Anvil app that the recommended technique is to pull the data out of the search iterator and work with it in Python.

But yeah, it’s a learning curve to know where the edges are.

2 Likes

And… just like that. THAT portion of the app now works!!! Thank you for the help, and I am better prepared to work on the rest of the app!
:slight_smile:

1 Like

Hi @jason! If @jshaffstall’s or @ianbuywise 's answer solved your issue, can you mark their response as the solution (by clicking the dots menu, then Solution)? That way if others are searching the forum for a similar issue, they’ll see that it’s been solved!

1 Like