Lab Sample Tracking - ensure unique form entry combinations, flag error for duplicate entry

[kmschmdt]

Hello all, hopefully someone can assist. I have a sample tracking App built to manage the status and track progress of various samples in my lab.

It is a straightforward sample entry form, with reference tables for:
1. Project # (4 number reference code)
2. Site name (Hundreds of unique sites)
3. Replicate (10 subsamples, labelled A - J)
4. Progress (status report, i.e. "Not Started", "In Progress", "Complete")

In the form you can choose:
Project # -> Site Name -> Replicate -> Progress
Example Input: "2137" "KD/28" "G" "In Progress"

I would like to have a warning message appear when someone enters the same combination by accident. I understand this is a validif and and() statement but I am struggling to wrap my head around the conditional statement that would be required. I do not have any code to show.

Additionally, if it is possible to filter out dropdown entries as you go, that would be greatly beneficial. For instance, if a later entry is "2137" "KD/28" "G" - how do I remove "In progress" as an option, since there already exists an entry with this combination?

I am a new user to the app so would appreciate some guidance. Kind regards

[Markus_Malessa]

Probably a virtual column with a formula like:

[Project #] & "_" & [Site name] & "_" & [Replicate] & "_" & [Progress]

Then a valid if of:

NOT(IN([_THISROW].[Virtual Column], SameTableName[Virtual Column]))

I would have to think about accomplishing your other subquestion.

[kmschmdt]

Thank you so much! This works! Please let me know if you can offer some advice on my other subquestion. Much appreciated!

[Markus_Malessa]

Not sure if this will work for your subquestion but give it a try. This would need to be in the 'suggested values' setting for [Target Column]:

SELECT(SameTableName[Target Column], NOT(CONTAINS([Virtual Column], "In progress")))