For relationships (self and otherwise) do you think Sum( rel::constant ) or Count( rel::constant) would be faster, given that constant = 1? I'd like to shave off some time when validating uniqueness of records in a script, i.e.

If [sum( self_client::constant ) > 1] vs If [Count( self_client::constant ) > 1]

Or do you think the difference is probably negligible?

Queue,

Depending what kind of uniqueness test you're into,

IsValid(TheRelationship::TheRecord_ID) or notIsEmpty(TheRelationship::TheRecord_ID)

would be faster I think to determine if there is already one match (>=1)

If really the >1 is the condition you're looking for, why not run another test for fun.

Add this calc to your file :

c_tagdupes= If(Min(SelfJoin::RecordID) = RecordID,1,0)

This does not tag a record with anything if it does not have duplicates, if it does, it tags the first record with a 1 and the remainder with a 0. Ciao Andy

And this script step

If(Sum(:SelfJoin:c_tagdupes) =1)

Hmm...

If the first assumption was OK (you're looking for >=1), why not use the same method with another calc.

c_purgedupes = Case(Record_ID = SelfJoin::Record_ID,1,0)

This tags the first record with a 1 and all others with a 0 (If there is no dupe, the record is tagged with a 1)

So If(Sum(::SelfJoin:c_purgedupes) =1)

You're the one who asked for a test though...so test it

Hmmm. That's a very novel idea to me. I like it! But in this instance, I really am testing for > 1. Basically, users enter task codes for line items, using two fields limited to four digits each. It doesn't matter what combination they choose, as long as for each budget there is only one set of each combination. When they set the budget, I run a script to validate the data and check whether they have entered any duplicate task codes. I know there will be at least one instance for the given record. So I test the Sum() or Count() to ensure there is only one. I'm not sure I'd want to add the calc field if it would slow anything down. This db already has 50,000 records. I think it depends on what I'd be using as the self-join match field. Are you saying I should use a constant relationship?

Well, the max I've had to check for dupes was a 35,000 records, and I can remember I used count...

Now, if you just want to test it, may be a

SetField (g_test, Min(SelfJoin::RecordID))

followed by an If (Record_ID<>g_test)

I doubt this would be quicker, but as we would "script the relationship match"...

I get what you're saying. I think I could use something like this to enhance the user experience more. Right now they receive a message: At least one of these tasks is not unique. Using Min() I could actually highlight the portal record in question and eliminate them needing to search for themselves where the problem is. Thank you, Ugo!

JT,

I actually released 2 successive demo files in the Sample section just about this kind of process, checking for Dulicate Names or Mispellings.

While one isn't quite 100% reliable (but I did have fun with this), the other is exactly what you're describing.

link1

link2

Sweet, Ugo! I'll check them out later and let you know the results.