Searching a data table for multiple criteria

Anvil Q&A
#1

I am trying to use the record = app_tables.clockinoutsystem.search(tables.order_by(‘Clock_In_Start’, ascending=False))[0] to get the last row from my data table. The problem is that this app is suppose to be multiuser so if two people start clocking in/out at the same time, it save the timestamp correctly. How would I be able to to use the app_tables.clockinoutsystem.search for multiple categories. What I want to do is to search for the last record of that particular user.

def clock_in_button_click(self, **event_args):
    Start_time = self.Current_Time()
    record = app_tables.clockinoutsystem.add_row(Clock_In_Start=Start_time,Clock_Out_Lunch=None,Clock_In=None, Clock_Out_End=None,Users=self.user_name.text)
    
    #Start_time = self.Current_Time()
    self.Start_clock_in.text = Start_time
    return Start_time
    

  def clock_out_button_click(self, **event_args):
    clockout_time = self.Current_Time()
    record = app_tables.clockinoutsystem.search(tables.order_by('Clock_In_Start', ascending=False))[0]
    record['Clock_Out_Lunch'] = clockout_time
      
      
    #Out_for_lunch = self.Current_Time()
    self.Out_lunch.text = clockout_time
    return

Clone link:
app_tables.clockinoutsystem.search

#2

In table clockinoutsystem, do you have a column to identify the currently-logged-in user? You’ll need something like that to distinguish one user’s clock-out from another user’s.

#3

I do have a USER column and I have a label that takes the logged-in user’s email. I then assign it to the USERS column.

image
image1127×62 4.56 KB

#4

Then you can filter on that column, based on the current user. See:

get_user

Searching (querying) a Table.

Multiple filters/conditions are supported.

Edit: There are also query examples under Learn above.

#5

Does this look about right for what I am trying to do?

record = app_tables.clockinoutsystem.search(tables.order_by('Clock_In_Start', ascending=False))[0] and app_tables.clockinoutsystem.search(Users=anvil.users.get_user())
#6

First, let’s break it down and format it for readability:

iterator_over_all_my_records = app_tables.clockinoutsystem.search(
      Users=anvil.users.get_user()
   )
most_recent_record_from_some_user = app_tables.clockinoutsystem.search(
      tables.order_by('Clock_In_Start', ascending=False)
   )[0]
record = most_recent_record_from_some_user and iterator_over_all_my_records

The short answer is “no”. Consider the type of each of the two values being handed to Python’s and, and how Python’s and actually works. What you get won’t be a database table row, much less the row you want.

We need to pack all the criteria into a single search. That is, we need to combine criteria, inside search’s parameter list. That gives the database server all the criteria at once. This way, it can make sure that the rows it returns meet all the criteria at the same time.

When you visited Searching (querying) a Table , you probably stopped well short of the example that shows you how to combine query conditions.

Edit 1: tell your browser to search that documentation page for Combining.

Edit 2: Also see Querying Data Tables

1 Like
#7

This has to be correct hehe. It supposes to search for both criteria in a single search (If I did it correctly), however, I don’t know how to reference a specific column to update afterward.

basically, after I find that row, I need it to update a column of that row to a specific date. I have no clue as to how to update it. I would think record[‘column name’] would work but it does not.

    record = app_tables.clockinoutsystem.search(q.all_of(Users=self.user_name.text,Clock_In_Start=clock))
#8

I believe I got it.

for row in app_tables.clockinoutsystem.search(
q.all_of(Users=self.user_name.text,Clock_In_Start=clock)
):
      row['Clock_Out_Lunch'] = clockout_time
#9

You don’t need q.all_of (it doesn’t hurt, but is doing nothing extra for you). The search function itself supports multiple query criteria by passing multiple arguments.

app_tables.clockinoutsystem.search(Users=self.user_name.text,Clock_In_Start=clock)

At some point you also lost the tables.order_by ordering. If you still need that, it goes before any of the query parameters, e.g.:

app_tables.clockinoutsystem.search(tables.order_by('Clock_In_Start', ascending=False), Users=self.user_name.text)
2 Likes
#10

It’s probably a good idea to limit all Client-side table access to the rows owned by the current user. For help with that, look up Views, in the documentation. Specifically, Client-Readable and Client-Writable views. While there, also skim the Security model.

2 Likes
#11

How would you have the same parameter more than once? I’m currently getting an error if the same field is added to the search function multiple times. The error is “syntax error keyword argument repeated”.

My example:
app_tables.mytbl.search(tables.order_by(“average_price”, ascending=True), average_price=q.less_than(high), average_price=q.greater_than(low))

#12

You can use q.all_of.

Se here for some examples: Querying Data Tables

Welcome to the forum. Next time please create a new question instead of responding to a question that already has the answer with another question.