Unique Record based on date in form.

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I am having an employee submit a record on my table. Each record will have the Quickbase user name and it will also have the employee number for them. When creating the record, they have to select a date. I want the table to be restricted so that the employee can only create 1 record for each date. Any time they try to add or modify a record, if the date field matches a record already created by that same employee, I want a message to pop up. At minimum, not being able to save.
Photo of Marc Nelson

Marc Nelson

  • 322 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
No problem.
One employee has many records.  Let's assume that the records are called Overtime Requests.

Let's assume that there is a field named Related Employee on the Over Time Request Table.

Create a formula text field named
[Sorry, but you already have an Overtime Request for this Date]

I know that is a long field name, but there is method to my madness. 

The formula will be with the formula

List("-", ToText([Related Employee], ToText([Overtime Date]))

Mark the field as being Unique in Field Properties.

Make a duplicate entry and save and observe the result.
Photo of Marc Nelson

Marc Nelson

  • 322 Points 250 badge 2x thumb
I have made the changes and I get an error when trying to make the field unique. It says: 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
In that formula you need to use the field which is the reference field for the relationship.  What is the field on the  top right hand side of the relationship where 1 Employee has many over time requests?

(In future, when posting a formula, please post the text of the formula and not a screen shot.  We cannot edit an image.)
Photo of Marc Nelson

Marc Nelson

  • 322 Points 250 badge 2x thumb
That did the trick, I had a different field as the reference field and once I change it, I now get the error for duplicate records.

Next Challenge, do you have a work around for this?

Each employee has the option to "Remove" their request by clicking a check mark. I have the views set up to filter out any request with a check mark. In theory, its deleted without actually using the delete option.

Thanks again!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
Yes, but please post your formula which is unique.  We need to make it null (empty) if that checkbox is checked.
Photo of Marc Nelson

Marc Nelson

  • 322 Points 250 badge 2x thumb
Here is what I came up with for the unique field...

List("-",ToText([Quickbase User Name]),ToText([Overtime Volunteer Date]))


The field that has the check box is "Remove from List"
Photo of Marc Nelson

Marc Nelson

  • 322 Points 250 badge 2x thumb
I figured it out using an IF formula....

If([Remove from List]=false,List("-",ToText([Quickbase User Name]),ToText([Overtime Volunteer Date])),"")

Thanks again for the help and tips!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
Well done and greetings from 30,000 feet somewhere over the middle of the USA enroute to Seattle.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,702 Points 50k badge 2x thumb
IF(not [Remove from List],

List("-",ToText([Quickbase User Name]),ToText([Overtime Volunteer Date]))
)


The effect of this is that if the [Remove from list] is true, then the IF will calculate to the "else ..." portion of the IF.  But there is no "else portion so it will calculate to null.  That is the one value which can be duplicated when a field is set to be Unique. You can have duplicate nulls.