Searching a data table for multiple criteria

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

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.

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.

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.

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())

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

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))

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
    

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

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