Can i flag off duplicate record in Quickbase when i have one program entering data twice on the form page ?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I have two tables here, Providers and Students where many students can be related to many providers, I want to eliminate duplicates and when a program enters a record twice, the form should not save and instead flag it as duplicate. However another program can input this student. The catch is one student can be entered by a program once, If entered twice then QB form flag should not save and flag as duplicate.

Photo of JW

JW

  • 140 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,486 Points 5k badge 2x thumb
If you had a three-table structure:

Table 1: Providers

Table 2: Students

Table 3: Provider Students

and used your Table 1 and 2 as a "master list" of providers and students, so that in each table, each record can only be 1 value (unique).  Then you could populate your Table 3: Provider Students as a "join" table which could have a validation field to prevent a User from adding a Provider/Student combination as a duplicate.

If a unique field is not unique, QuickBase will not save the record; thereby eliminating the possibility of duplicates.

To my knowledge, there is no way to "flag" a duplicate record in a single-table because the records are not related to each other so cannot share information between themselves.

Usually, if I am looking for possible data-duplication; I create a Summary Report with the Row Grouping on the field I need to validate, sort the count field from High to Low and subscribe to it Daily to check for duplicate entries.  QuickBase cannot prevent a record from being saved if it is a duplicate unless a field designated as Unique is duplicated; in which case QuickBase will NOT save the record, it will flag to the User that the record is a Duplicate and that the record will not be saved.
Photo of JW

JW

  • 140 Points 100 badge 2x thumb
Providers are list of providers and they are a list populated, But data in students table will be entered real time and i have an ID field that is unique, And when a student entry is made with the same ID twice the form should not save and instead give an error saying " this student exists in this program", however this student can be entered by another program with same ID. Does the above hold true in this case.

I did a relationship where one provider has many students and again did a reverse look up where many students belong to many providers.
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,486 Points 5k badge 2x thumb
Then what you need is a formula field to string the [Related Program] and the [Student ID#] together.  A Form Rule should populate THAT value into a text-field.  Make the text-field that gets populated by the form-rule Unique.  This will allow a Student to be assigned to more than one program; but not the same program twice.
Photo of JW

JW

  • 140 Points 100 badge 2x thumb
Firstly, Thank you for helping me out here,  I am a little confused, could you elaborate on doing this in same master table or do i have to create a new table to accomplish this.
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,486 Points 5k badge 2x thumb
No, your two-table structure is fine.

1) In your [Students] table, create a formula-text field with the formula:
[Related Provider] & " - " & [Student ID #]

2) In your [Students] table, create a text-entry field - make this field Unique in the table (you will need to pre-populate it for existing records so that it can be set to unique)
3) In your [Students] table Form, write a Form Rule
     When the [Student ID#] is not [ ] (empty)
     Change the [Field value in 2] to the [Field Value in 1]
5)  Make sure that your [Related Provider] and [Student ID#] fields are REQUIRED on the FORM
6)  Make sure your Field in 3 is on the Form, but it does not have to necessarily be visible in the Form to the User (ie. you build a section heading and put the field underneath it and use Form Rules to hide/show that Section based on the User Role)
4) When Users add a new Student record and the [unique field in 2] is identified as NOT being unique; then the record will NOT be saved

Contact me if you have any issues with this.

** note - if your users are adding Student records via Grid Edit; none of this will work
Photo of JW

JW

  • 140 Points 100 badge 2x thumb
Hi for #2 the data entry will be real time, We cannot pre-populate any of the ID's or existing records. So i don't think the above functionality will work, I want some thing like - Say a scenario a program goes in and inputs a student record, say they try to input the same student again QB form needs to throw a message saying record already exists for this program. However this student record can be added by any another program.
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,486 Points 5k badge 2x thumb
The way you have described it, is that you enter a Student ID# (not a QuickBase record ID#) when you are entering the Student.  This is the value in 2).  When you try to save the Student record, QuickBase then looks at your entered Student ID# combined with your Provider ID (already exists) and determines if that join is unique or not - either allowing or preventing the save.  A student can be assigned to one or more Providers (Programs) but cannot be assigned to the same Provider/Program twice.  I'm not sure why you think this won't work.  Have you tried it?
Photo of JW

JW

  • 140 Points 100 badge 2x thumb
The concept is working but the values in ID field don't show up, When i make an entry it defaults to 0 along with the provider name. Is there a way to mitigate this small thing.
Photo of QuickBasePros (Laura Thacker)

QuickBasePros (Laura Thacker), Champion

  • 5,486 Points 5k badge 2x thumb
Perhaps you can contact me offline to resolve