Forum Discussion

MarcNelson's avatar
MarcNelson
Qrew Cadet
8 years ago

Unique Record based on date in form.

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.
App builders
Ask about Quickbase functionality
Forms
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.
    • MarcNelson's avatar
      MarcNelson
      Qrew Cadet
      8 years ago
      I have made the changes and I get an error when trying to make the field unique. It says: 
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.)
    • MarcNelson's avatar
      MarcNelson
      Qrew Cadet
      8 years ago
      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!
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    Yes, but please post your formula which is unique.  We need to make it null (empty) if that checkbox is checked.
    • MarcNelson's avatar
      MarcNelson
      Qrew Cadet
      8 years ago
      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
    • MarcNelson's avatar
      MarcNelson
      Qrew Cadet
      8 years ago
      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!
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      Well done and greetings from 30,000 feet somewhere over the middle of the USA enroute to Seattle.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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.