Rollbase unique field combination trigger

I don't know if the uniqueness is supposed to work with blank values, but does that mean you want to be able to have one and only one record with a blank value? If you don't want to have blank value you can specify the field as required.

I do want to have only one record with a blank value.

This is likely more a feature of the underlying RDBMS than the product itself.   Example, NULL (blank) Unique fields are allowed by Oracle but not SQL Server or DB2.  Strictly speaking first normal form does not allow for null values in unique fields anyway.

I'm confused about how not allowing more than one blank in Address2 makes sense since there can be any number of addresses that do not have a second address line.  It sounds more like the way the combination field is being instantiated has issues than the Address 2 field.  

Nevertheless, can you just implement a simple validation script that queries the database for null values in that field to solve the issue?

It's not just the address 2 field, but the combination of all of the address fields that needs unique enforcement.  From what I can tell, the "problem" isn't tied to the address 2 field, it just manifested in that field first because my sample data included several addresses without an address 2 value.  If I enter two address records that are identical in all of these key fields (including a blank address 2 field), I would expect the uniqueness trigger to reject the second entry.  

To clarify, the uniqueness trigger I have uses the name, address 1, address 2, country, city, state, and zip fields.  When I enter two identical addresses with values in all fields, the trigger rejects the second address.  When I enter two identical addresses with nothing specified in the address 2 fields the trigger does not reject the second entry.  If someone can tell me that this is the expected behavior of the uniqueness trigger (null/non-specified values aren't evaluated), then I'll investigate a validation script.  I'm new to RB and really want to make sure I understand how everything works before we consider migrating our (rather large) enterprise system to RB.

Try putting the Address2 field last in the order?  The null value may be the cause of the problem if it is in the middle of the combination.

For some reason I did not receive an email with your response!  I'd really hoped this would be the answer, but when I re-order the fields in the field list and click "Save", the fields are automatically re-ordered alphabetically.  Surely someone has found a way to do this.  I'm getting desperate!

Null values are valid in the address2 field.  Here's an example:

name: Papersoft

streetAddress1: 241 Applegate Trace

streetAddress2:

city: Pelham

state: AL

zip: 35124

If I add a new address that is identical to the address above, the trigger does not work.

If I create the original address with a value in the address2 field and then attempt to create a duplicate, the trigger works as expected.

Work around Plan B - create a formula field for the combination of address 1 and address 2 (called address?) and use that in the combination trigger?

Interesting idea.  Have you attempted this yourself?  I just created a formula field that returns a combination of the fields in the address, but my new formula field does not appear in the list of valid fields to use in a uniqueness trigger.

I finally muddled my way through an attempt at using the API to accomplish the same test, but it appears that even the API approach has a problem with blank or null values...  Here's the formula I used:

var res = 0;

res = rbv_api.selectValue("SELECT COUNT(name) FROM ps_address WHERE streetAddr1 = '{!streetAddr1#value}' AND streetAddr2 = '{!streetAddr2#value}' AND city = '{!city#value}' AND state = '{!state#value}' AND zip = '{!zip#value}'")

if (res > 1) return "Address already exists.";

return;

Again, when streetAddr2 is blank, the test does not run as expected.  I really hope someone can help as I'm at a stand-still from an application design perspective.

When working with the Rollbase API,   I find it helpful to assign merge tokens to variables first thing,  then do explicit manipulation of these variables to turn them into the format that your SQL query will except.  You might also have to modify your query conditionally, depending on the values of particular merge tokens

I think that your root issue is that an empty merge token will render as "" (an empty string essentially),  but your SQL query is would like to see "IS NULL" instead.

The following is a very crude example,  but just to give you a visual depiction of what I am trying to convey....  

Also, Rollbase provides you with the "rbv_api.isFieldEmpty" API call to test for the emptiness of fields.  So there are multiple ways to accomplish the same thing.

var res = 0;

var streetAddr1 = '{!streetAddr1#value}';

var streetAddr2 = '{!streetAddr2#value}';

var city = '{!city#value}';

var state = '{!state#value}';

var zip = '{!zip#value}';

var query = "SELECT COUNT(name) FROM ps_address WHERE streetAddr1 = '" + streetAddr1 +

           "' AND streetAddr2 = '" + streetAddr2 +

           "' AND city = '" + city +

           "' AND state = '" + state +

           "' AND zip = '" + zip;

//modify query in the case where streetAddr2 is blank

if (streetAddr2 == "") {

 query = "SELECT COUNT(name) FROM ps_address WHERE streetAddr1 = '" + streetAddr1 +

           "' AND streetAddr2 IS NULL" +

           " AND city = '" + city +

           "' AND state = '" + state +

           "' AND zip = '" + zip;

}

res = rbv_api.selectValue(query);

...hope this helps!!!

This works!