One record per person per day

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Answered
(First, I'm new to QB.  So, sorry in advance.)  I have been asked to create functionality where employees can check-in/check-out at the beginning/end of day.  I found some apps in the exchange that were a little more complicated than I was hoping.  I'd like a one-table solution.  I'm thinking of a table with a single Text-Multiple Choice field that indicates "Check IN" or "Check OUT" and uses the record owner and the creation date/time for the only other data I need.  Maybe just make two links that pre-fills the in or out values and saves the record.  Simple!

The only thing I think would be nice is to be able to abort the save if that user had created that type of record (in or out) on that day with a message that a check-in or a check-out has already been created that day. Is this possible?  What is the best way to do this?

The first thing that comes to my mind is to create a kind of formula field that queries the database looking for the current user as the record owner and the current date as the creation date and returns a "1" if that combination is found which becomes the trigger to abort the save.  Is there a way to do this?  Is there a complete different method that makes sense?  Thanks!
Photo of Tate Forgey

Tate Forgey

  • 276 Points 250 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I think at a minimum you will need to have 2 tables.
1- Resources (all your employees)
2- Resource Logs

What you will want it to have the "In/Out" status be an option on the employee record.  Then you can utilize an action to add a 'log' every time they check in, and then edit that log to close out when they check out.

Even though it sounds simple.  To get the action to edit the correct record, or use any type of API button requires some guidance.  So don't feel bad if you get stuck.



Matthew Neil - Product Specialist
Photo of Tate Forgey

Tate Forgey

  • 276 Points 250 badge 2x thumb
(Sorry for delay - I had to whitelist emails to get them in my inbox)
Thanks for the reply!  I am, indeed, stuck at a very simple level.  Right now I am just trying to create a formula url with the add record api that fills in just the one check-in/out field and saves the record.  I was hoping to have this happen without the user going to the form page and have to hit save.  Is that possible?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
Tate,
You can have a field for the current date which is a data entry field which defaults to "today". (no data entry required)
You can have a field for the user which defaults to the current user. (no data enruy required.

Your can then make a formula field to concatenate them together.

Lirt("-", ToText(Date]), UsertoName([Userid]))

and set the field properties for that field to be that to be unique.  Then there can only be one record per user per day.
Photo of Tate Forgey

Tate Forgey

  • 276 Points 250 badge 2x thumb
Thanks for the concatenate idea.  That makes perfect sense!

Unfortunately, I'm still stuck just trying to make a URL field that will add a record.  I just took off the requirement in the app for the token, which us why I thought my link was not working, but the link that is generated by this formula just takes me to the dashboard if when I click it in the form and takes me to the form when I click it in the dashboard with no record being created.  What am I doing wrong here?

URLRoot() & "db/" 
& [_DBID_CHECK_IN_OUTS] 
& "?a=API_AddRecord"
& "fid_6=CheckIN"
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
If you are looking to Create a record and then display it

URLRoot() & "db/" 
& [_DBID_CHECK_IN_OUTS] 
& "?a=API_AddRecord"
& "&_fid_6=CheckIN"
& "&disprec=1


If you are looking to put up an Add Record form

URLRoot() & "db/" 
& [_DBID_CHECK_IN_OUTS] 
& "?a=API_GenAddRecordForm"
& "&_fid_6=CheckIN"
Photo of Tate Forgey

Tate Forgey

  • 276 Points 250 badge 2x thumb
That last line made the difference.  I was getting some strange behavior from my code since nothing would show up then 5 minutes later all of sudden 4 records would appear simultaneously.  So, it seems like it needs something at the end there to specify the view.

My desired state would be to put this in a button on the main dashboard, have the user click it to generate the check-in record and look like they do not leave the dashboard.  Is that possible with a redirect or something or do we have to go to the record after saving?  (Thanks for all the help.)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,306 Points 50k badge 2x thumb
Which method do you plan to use? - API_Add Record or API_GenAddRecordForm
Photo of Tate Forgey

Tate Forgey

  • 276 Points 250 badge 2x thumb
Clearly I'm new to all of this.  So, I'm going to get out some presumptions I'm now realizing I'm making and I GREATLY appreciate you disabusing me of my mistaken notions.

First, it seemed to me that the API_GenAddRecordForm was pulling up the form itself although you could specify field values.  I was hoping that the AddRecord API was going to add a record without having to interact with the form.  Is that even close to right?  What is the difference between these two? 

Also, I was presuming I could take the URL that gets generated when I create the Formula URL Field in the form and drop it into a button on a button bar in the dashboard, but I'm now wondering if that will work like that.  How would I put something more than just the stock add a new record button on a dashboard?

All I'm hoping to do is for the user to click a "Check-in" and a "Check-out" button on the dashboard and have a record recorded in the "Check-in/out" table with the one variable field set to check-in or check-out and the owner be the current user and the created date be the date time the record was created, which I was presuming would happen automatically, and have it appear like they never left the dashboard (maybe with a redirect after an automatic save.)  
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I'd recommend that you create one button that has 2 actions (based on the current In/Out status)

(1) Will us the API_AddRecord call to create a new 'time log' record with a date/time of the clock in time.

and 
(2) Use the API_EditRecord call to edit the currently 'clocked in' record to add an 'out' date/time.

What you gain;
- Each day you have a single record.
- Duration calculations are based on the in/out fields
- Summarized data can be filtered easily with different criteria (today, yesterday, last 7 days, etc).
- An easy report that can be added to a dashboard for each user to clock in/out


Tricks:
You will need the Record ID# of the lastest "In" record, and the record ID# of the latest "Out" record.

Because you will need to make the button (formula-rich text) dynamic to Clock In (Add) or Clock Out (Edit) based on the current status.
(Edited)
Photo of Tate Forgey

Tate Forgey

  • 276 Points 250 badge 2x thumb
Thanks for the suggestion, Matthew.  That does sound ideal.  How would I get the last record ID#?  Also, I'm still struggling with adding the form with a simple button press.
Photo of Matthew Neil

Matthew Neil

  • 31,418 Points 20k badge 2x thumb
Open the relationship between the employees and the logs.  Add a summary field that pulls the 'maximum' Record ID# for the 'logged in' log.

For ease of use to you might make a formula checkbox to the log record to be checked if the record is "In".  Then your summary fields are easier to manage.

Example formula: If(IsNull([Check Out Date/Time]), true, false)



Matthew Neil - Product Specialist