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.
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.
I did a relationship where one provider has many students and again did a reverse look up where many students belong to many providers.
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