Discussions

Expand all | Collapse all

Unique Record based on date in form.

  • 1.  Unique Record based on date in form.

    Posted 11-27-2017 01:02
    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.


  • 2.  RE: Unique Record based on date in form.

    Posted 11-27-2017 03:02
    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.


  • 3.  RE: Unique Record based on date in form.

    Posted 11-27-2017 03:32
    I have made the changes and I get an error when trying to make the field unique. It says: 


  • 4.  RE: Unique Record based on date in form.

    Posted 11-27-2017 03:37
    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.)


  • 5.  RE: Unique Record based on date in form.

    Posted 11-27-2017 03:49
    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!


  • 6.  RE: Unique Record based on date in form.

    Posted 11-27-2017 03:52
    Yes, but please post your formula which is unique.  We need to make it null (empty) if that checkbox is checked.


  • 7.  RE: Unique Record based on date in form.

    Posted 11-27-2017 03:54
    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


  • 8.  RE: Unique Record based on date in form.

    Posted 11-27-2017 04:00
    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!


  • 9.  RE: Unique Record based on date in form.

    Posted 11-27-2017 04:02
    Well done and greetings from 30,000 feet somewhere over the middle of the USA enroute to Seattle.


  • 10.  RE: Unique Record based on date in form.

    Posted 11-27-2017 04:00
    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.