Data Merge - Change Text/Cell Characteristics based on Data Value

Report · Mar 28, 2011

I am working on a letter that is sent to our diabetic patients during their birthday month...the letter will contain the last known test results and date for several measures and then recommend action based on those results.

The data comes from a text (csv file separated by "^") file generated by our Electronic Medical Record (EMR) database...

What I need is a script or tool that will recognize specific text and then change the font size and cell background for that particular field...

In the first example below, everything is normal, so there is no notification necessary...

In the second example, the BP is too high (calculation is performed by our EMR), so the csv file includes "Due Now" and "Yes" for the final two columns in the table. The script needs to recognize "Due Now," enlarge the font, change the color, and change the background color of the cell, and then recognize "Yes" and do the same.

Is this possible? Can I do this without a third-party add-on?

CSV File Text: ^130/70 (3-28-2011)^Next Visit^No^

InDesign Table:

Measure	Your Goal	Your Results	Next Test?	Contact Doctor?
Blood Pressure	< 130/80	130/70 (3-28-2011)	Next Visit	No




CSV File Text: ^160/90 (3-28-2011)^Due Now^Yes^

InDesign Table:

Measure	Your Goal	Your Results	Next Test?	Contact Doctor?
Blood Pressure	< 130/80	160/90 (3-28-2011)	Due Now	Yes

Thanks,

Mark Hooten

Report · Apr 27, 2011

i know the concept of what you want to do but indesign's shipped data merge feature only puts in the data that it is given. that is, it won't run database queries based on if-else statements (e.g. if data=yes then font=arial....) - it won't do that. i'm assuming that you would want to merge the data directly to a printer or PDF rather than to a new file, so you would really need a third party solution. if you were happy to merge the data to a new file, then the tweaking could be done afterwards using GREP to find the applicable cells and then change the formatting using styles.

my work has been trialling a plug-in called xmpie which i'm very sure would do what you want but it'll cost you an arm and a leg - i'm talking four zeroes here! however, for what my work does (variable data printing) it is well worth it. btw, i am NOT a spokesperson for xmpie, nor am i one of their employees.

If the answer wasn't in my post, perhaps it might be on my blog at colecandoo!

Report · Apr 27, 2011

Hello Mark,

This is a pretty straightforward thing to do with a script. You can define a few different paragraph or character styles, and then use Find/Change to find the specific phrases and then change them to the same phrase plus a new style. There are a couple of examples of things like this in the sample scripting guide scripts. No need to spend four zeroes on anything (although I guess spending four zeroes would be the same as spending a single zero, so it would be free).

But I have two questions:

1. Are you SURE that the phrases are unique in the text? For example, it might not be a good idea to do a find on "Yes" because that's a pretty common word.

2. Why do you need InDesign for a simple letter? It seems a bit overkill, although I'm sure the patients will appreciate the fine typography. Not to be a party pooper, but you could do this kind of thing in Word or perhaps even TextEdit (on a Mac).

Regards,

Chuck

Report · Apr 27, 2011

Another method would be to use a database to manage the information and have it export InDesign Tagged Text. The database can evaluate the data in a field and use different tags accordingly in the export.