Skip to main content
Question

Unique Record based on date in form.

  • November 27, 2017
  • 0 replies
  • 75 views
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.
    This topic has been closed for replies.

    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.
    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.)
    Yes, but please post your formula which is unique.  We need to make it null (empty) if that checkbox is checked.
    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.
    Terms & ConditionsAccessibility statement