a hundred degrees

Making Copies

I came up with a clever little solution to a problem at work today. I'm sure that others have used this idea before, but I arrived at it independently and am perhaps disproportionately proud of it. I'm so intent on bragging about it that I promise that even when this gets a little technical you'll be able to follow it, so hang with me.

I got a call from the SVP in charge of loan support today asking me to help them with labels. Each day they create file folders for new loans that have been booked. They have a MS Word document into which they type the names and loan numbers to print labels for the folders. They usually put two labels on the folder and one on the check-out card for each consumer loan. For commercial loans they normally require six labels (I'm guessing that there are two folders).

The SVP had managed to create a query on the AS/400 that returned the loan number and the names associated with it (last, first, and middle in separate fields) for consumer loans. She indicated to me the field where she knew the business names to be for commercial loans, but admitted that some commercial loans were booked under an individual's name.

She told me that they spend up to an hour each day creating these labels and asked if I could automate the process a bit for them. Also, she made it clear that the number of labels wasn't exactly fixed for each type of loan; that it might vary by one or two depending on unspecified factors.

I reworked the query to give me four fields: loan number, primary name, secondary name, and number of copies. Number of copies, and whether it would look first for a business name were dependent on the loan type. I had it look for loans booked yesterday and scheduled it to run Tuesday through Saturday at 7:45 am. In my working table, I also created a "Printed" field that would be marked after the label had been printed. This would allow the user the options to print only the labels that had not been printed and to delete the previously printed labels from the table with the push of a button.

My form looked something like this:

That's cool except for one little problem. The data source that informs my report needs to actually have three copies of Able's record and six of Dinger Corp's. More generally, it needs to have just as many copies of each record as the user chooses in the dropdown in the Copies field. When I create a report based on this table, how am I going to translate the value in the "Copies" field into functionally multiplying the records?

An ugly solution would be to write some code that would read through the table and populate a temporary table line by line. It would work, but surely there's a more elegant way to handle this. After a few minutes consideration, I thought of one.

In a database when we join tables together in a one-to-many relationship, the resulting data set repeats the data in the "one" for each record in the "many." Consider these tables:

ADDRESSES
ID	Street	City	State	ZIP
112	234 Maple	Nantucket	OH	34234
113	3456 Pine	Quietville	RI	65345
114	1502 Sassafrass	Trainstop	UT	86745

If I wished to get all this data in one place, then I could join the tables together based on the address ID fields. Everywhere I have a match between the AddressID field in the CONTESTANTS table and the ID field in the ADDRESSES table I'll put the data together. Perhaps I'd like to mail each participant a certificate. My SQL might look like say
SELECT CONTESTANTS.Name, ADDRESSES.Street, ADDRESSES.City, ADDRESSES.State, ADDRESSES.ZIP
FROM CONTESTANTS INNER JOIN ADDRESSES ON CONTESTANTS.AddressID = ADDRESSES.ID
And my result set would look like this:

MAILING LIST
Name	Street	City	State	ZIP
Eva Franklin	234 Maple	Nantucket	OH	34234
George Franklin	234 Maple	Nantucket	OH	34234
Harry Isaacson	3456 Pine	Quietville	RI	65345
James Kirk	1502 Sassafrass	Trainstop	UT	86745
Lucille Kirk	1502 Sassafrass	Trainstop	UT	86745

See how the address repeats for each of the names where the address ID's match even though I don't actually show the ID fields? That's the key to my solution for the multiplying labels.

Here's what I did. I created another table in my database with a single field called "Copies" and forty-five records. One record has a "1" in the field. Two have a "2", three have a "3", four have a "4", and so on to nine 9's.

Then when I want to print labels I join the tables together where the Copies fields have the same value. That way, if there is a "3" in the field on the Label table, then it links to three separate records in the Copies table so three labels print. If there's a "6" then it links to six records so six labels print. And similarly for the other seven single digits.

Nifty, right?

Okay, I said that I was disproportionately proud of myself.

Posted to Work Life at May 19, 2005 10:00 PM