Forum Discussion
MarkShnier__You
Qrew Champion
David,
What do you mean by this?
QB won't run a duplication check on a formula field
You can have a Formula Query look for duplicates and that will happen even before the record is saved.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
DavidSemitekol
2 years agoQrew Trainee
Hi Mark, I'm referring to the "Must be unique" checkbox in the field settings. Here is my formula:
When I did some research, I came across this post in the community:
Enforcing Uniqueness on a Formula Field
------------------------------
David Semitekol
------------------------------
List(ToText([Number Start]),ToText([Number End]),[Address Number],[Predirectional],[Street Name],[Suffix],[Post Directional])​
When I tried to save the field, I got this error:
When I did some research, I came across this post in the community:
Enforcing Uniqueness on a Formula Field
------------------------------
David Semitekol
------------------------------
- MarkShnier__You2 years agoQrew Champion
Perhaps some of their fields are lookup fields? I would need to know the field type of each to suggest which one is the culprit.
If you do a formula query it's also possible to detect duplicates, regardless of what the components are of the unique field. If you need help with that we can muddle through it together. The Formula Query would be if the Size of the result was greater than 1.
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------- DavidSemitekol2 years agoQrew TraineeSorry Mark, yes, the Street Name is a lookup field. I'm okay with the current solution of using a custom data rule.
What I'm really struggling with is the QB Address Field and the formatting of the MapBox address. They just are not using standard address formatting that we expect from the USPS or Google Maps.
This causes 2 issues:
The first is that searching for addresses becomes very difficult because end users are accustomed to entering an address using the standard format. But when the QB Address field changes the way an address gets entered, the user can no longer find the address using the search records tool.
The second is there is no way to prevent duplicate entries easily, if at all, so it becomes a data entry nightmare. Sure, we could write a complex formula that looks for "North" and converts to "N" but that won't account for all variables in an address. Like when "North" is actually the street name and not the predirectional.
Today's end user wants to be able to start entering an address and have a dropdown autofill the result. We need to find a way to duplicate that experience in QB. I think that is why I've been experimenting with this complex address table but I'm not sure if it is future proof, provides a quality user experience, or can scale when we go from 1k to 5k to 10k plus records.
Any help would be greatly appreciated in solving this Rubik's Cube.
------------------------------
David Semitekol
------------------------------