Only unique records

[tsghost]

I have a database with 87,000 records, within this data I have 75 Unique store numbers.

 

I need to make a proof on only one instance of these 75 Unique stores.

 

Is there a way to have FusionPro go through the data and do this for me?

 

Thanks

 

Tom

[rpaterick]

Tom,

 

I have Office 2007 and one way to do this in Excel is to high-light the column that has the store ids in them, click the DATA tab, click ADVANCED, click OK, check UNIQUE RECORDS ONLY, click OK.

Copy the header to the last row/record and copy and past that into a new worksheet. Save-AS and you should now have your proof data file.

 

Hopes this helps.

[tsghost]

Thanks, I will use it for now but would still like to know if there is way to have FusionPro to do it.

[Dan Korn]

Sure, this is pretty simple with some JavaScript. Two steps: First, from the Rules dialog, click on "JavaScript Globals" and copy this into the editor, then click OK:

var UniqueRecordIDs = new Object;

Second, create the OnRecordStart callback rule and copy this:

var Key = Field("StoreID"); // change to your field name
FusionPro.Composition.composeThisRecord = !UniqueRecordIDs[Key];
UniqueRecordIDs[Key] = 1;

// This is optional:
if (!FusionPro.Composition.composeThisRecord)
 Print("Skipping duplicate: " + Key);
else
 Print("Unique key: " + Key);

Set your own field name in the first line of OnRecordStart, and this should do what you want.

 

Note that it's still going to take FusionPro quite some time to filter through all 87,000 records, however. For better performance, you should consider filtering the data first before composing, as rpaterick suggests.

[tsghost]

Great!

 

Thanks Dan!

[esmith]

Sure, this is pretty simple with some JavaScript. Two steps: First, from the Rules dialog, click on "JavaScript Globals" and copy this into the editor, then click OK:

var UniqueRecordIDs = new Object;

Second, create the OnRecordStart callback rule and copy this:

var Key = Field("StoreID"); // change to your field name
FusionPro.Composition.composeThisRecord = !UniqueRecordIDs[Key];
UniqueRecordIDs[Key] = 1;

// This is optional:
if (!FusionPro.Composition.composeThisRecord)
 Print("Skipping duplicate: " + Key);
else
 Print("Unique key: " + Key);

Set your own field name in the first line of OnRecordStart, and this should do what you want.

Thanks for the fish, Dan. Now I'd like you to break it down so I can fish for myself in the future.

 

I (think I) understand the creation of a new object, and that in each record we are assigning a variable for a field. I also see that we are setting the composeThis Record function to true or false based on the opposite value of UniqueRecordIDs[Key], which appears to be set to true (1) AFTER the comparison is run for a record, presumably to set the default to true for each new record.

 

What I don't get is how we are defining properties or values for UniqueRecordIDs? I assume the code is somehow populating an array with IDs and then comparing each new record's ID against that array, printing the new record only if it isn't in the existing array, but I can't figure out where in the code this is happening.

 

Care to elaborate?

[Dan Korn]

What I don't get is how we are defining properties or values for UniqueRecordIDs? I assume the code is somehow populating an array with IDs and then comparing each new record's ID against that array, printing the new record only if it isn't in the existing array, but I can't figure out where in the code this is happening.

It's happening right here:

FusionPro.Composition.composeThisRecord = !UniqueRecordIDs[Key];
UniqueRecordIDs[Key] = 1;

All JavaScript objects can have properties, and those properties themselves are what's called an associative array (also sometimes called a map or a hash) of unique keys to values. These properties can be accessed with either the "dot" or "brackets" notation. So these two statements are equivalent:

MyObj.foo
// is the same as:
MyObj["foo"]

Properties are added "on the fly" by assigning them values, generally via the dot or bracket notation, like so:

MyObj.foo = "hi";
MyObj["bar"] = "there";

If you try to access a property that hasn't been assigned, it returns an "undefined" value, which when treated as a Boolean (true/false) resolves to false. So, when we query like so:

UniqueRecordIDs[Key]

This means, "Return the value, if any, of the property whose name is the value of the variable Key (the field value for the record)." Then we use the the "!" (Boolean "NOT") operator to give convert it to a Boolean value and return its opposite:

!UniqueRecordIDs[Key]

This is saying, "Does the object UniqueRecordIDs contain a property whose name is the value of the variable Key (the field value for the record) with a (defined, non-zero) value? If so, return its Boolean opposite (using the '!' [boolean 'NOT'] operator)." In other words, if the object already has that property, return false, else return true. This gets assigned to FusionPro.Composition.composeThisRecord, so we only compose the record if the object has not already had that property added to it.

 

Then, in order to ensure that we remember that this field value has already been seen, we add a property with that name to the object, and assign it a value of 1 (or anything that resolves to Boolean true):

UniqueRecordIDs[Key] = 1;

So that next time we encounter that Key value, the test returns true, and we set FusionPro.Composition.composeThisRecord to false.

For more information:

https://developer.mozilla.org/en/Core_JavaScript_1.5_Reference/Global_Objects/Boolean

https://developer.mozilla.org/en/Core_JavaScript_1.5_Guide/Objects_and_Properties

http://www.quirksmode.org/js/associative.html

http://blog.xkoder.com/2008/07/10/javascript-associative-arrays-demystified/

[tobarstep]

What I don't get is how we are defining properties or values for UniqueRecordIDs? I assume the code is somehow populating an array with IDs and then comparing each new record's ID against that array, printing the new record only if it isn't in the existing array, but I can't figure out where in the code this is happening.

 

Care to elaborate?

 

Took me a couple passes reading through this thread to see what was going on.

 

On the initial record for each "StoreID" field, the variable "Key" is assigned that value. Then the variable is used as the element of the array itself, not the value of that element. Since that element does not yet exist, it returns a false or null. JS appears to consider null == false == 0, which is really key to understanding it, I think.

 

So, if in record 1, StoreID == 57, then you are effectively saying that you want to set ComposeThisRecord to the opposite of the value of UniqueRecrodIDs[57]. Since element 57 of that array does not yet exist, it returns a null/false value. The opposite of which would then cause that record to be composed. After that you set UniqueRecordIDs[57] = 1.

 

When record 2 is composed, ComposeThisRecord is again set to the opposite of the value of UniqueRecrodIDs[57]. Only this time, the value is 1, which as you pointed out is effectively "true". Once we get to a new StoreID, the process repeats. In the end you will have composed 1 record for each store ID in the data set.

 

Pretty cool, actually. I'll have to find a place to use a routine like that some day. As Dan indicated though, this is really better accomplished on the database side and only generating the records you intend to compose.

[tobarstep]

I see Dan explained himself better than I could.

[DSweet]

This is a wonderful little script for 1-up proofing. However throw an imposition into the mix for a printed sample, and you get the following error...

 

A body page does not have master page assigned.

A blank page is emitted since no record is composed properly.

PDF Library Document Write Error: Bad parameter.

Job aborted with error.

 

Any ideas on how to get a printed proof on unique record ids?

.

[Kim]

Thanks rpaterick -- that's a very helpful thing to know in Excel.

 

And, as usual, Dan's solution is a thing of beauty.

 

I'm very interested in David's question.

I'd love to know if it's possible to get this to work with an imposed press sheet proof.

Have you figured anything out, David?

 

thanks,

kim