Hi,
I'm intrigued by the reference to using it to hold an email address looked up from a database. What would the general steps be to accomplish this?
We have 2 databases. For the sake of simplicity, let's say these DB are CSV files.
Database#1 is used as the datafile of a PlanetPress Design document. It contains fields like:
CustomerID,CustomerFirstName,CustomerLastName,etc.
Database#2 is used to link a customer ID with its email address. I contains fields like:
CustomerID,customerEmail
The problemThe problem is the followoing: How can a PlanetPress Design document using database#1 displays the customer's email address in a page, given that the email address comes from database#2?
The solutionUsing PlanetPress Suite 7's metadata tools, we can achieve this. The general steps are the following:
1. In the PlanetPress Design document, define a document marker. A document marker will separate the datafile into logical sets of datapages (and pages). Each set of pages will be considered as a document (in the metadata sense, not as a PlanetPress design document). A metadata document can be considered as an invoice. In our case, each datapage correspond to exactly one document, or 'invoice'.
To define a document marker that separates the invoice in as many documents as there are datapages:
a. Double click on the PlanetPress Design document node, then select 'Metadata' property.
b. Under 'Document Marker', select 'begin' as the Marker and leave the condition empty.
c. Close the properties window.
d. Right-click over the Metadata fields folder and select 'Refresh Metadata'
e. Double click on the Sample data pane, and select the Metadata tab. Examine it to confirm that the DocumentCount attribute behaves as expected.
2. In the PlanetPress Design document, define a metadata field at the document level, holding the customer ID.On the data pane, select the data corresponding to the Customer ID, then drag it on the 'Metadata Fields' folder. This will create a new metadata field. You can rename it 'customerEmail', for example.
Why name this metadata field 'customerEmail' since we have selected the customer ID? The reason is that the value for this field will be changed dynamically by PlanetPress Workflow before the PlanetPress Design document outputs anything.
Again, right click on the Metadata fields folder and refresh the metadata. You can also double click on the data pane and select the metadata tab to see that a field have been created as expected.
3. In the PlanetPress Design document, create a data selection containing the defined metadata fieldYou must make an act of faith here. Create the data selection, and select the metadata field named 'customerEmail'. Of course, it will display the customer ID since it is what we have selected in step 2. But you have to believe that PlanetPress Workflow will change the value of this field before executing the document.
4. Send the PlanetPress Design document to the Workflow tool5. In the Workflow tool, create a new process like the following:Input task: anything.
Action task: Create Metadata. On the plugin's properties, select the PlanetPress Design document you just sent.
Action task: Run Script
Output task: PlanetPress Imaging (or CreatePDF, or anything that uses Optimized PostScript Stream).
6. Create a DSN for database#2In our case, we have created the following file:
C:\metadata_demo\metadata.dsn
7. Edit the run script plugin like the following:Option Explicit
Dim MyMeta, MyMetaField, x, MetaCustID, MyGroup
Dim MyConnection, MyRecordSet, MySQLStatement
Set MyConnection = CreateObject("ADODB.Connection")
MyConnection.Open "DBQ=C:\METADATA_DEMO;DefaultDir=C:\METADATA_DEMO;Driver={Microsoft Text Driver (*.txt; *.csv)}; etc etc...
Set MyRecordSet = CreateObject("ADODB.recordset")
MySQLStatement = "SELECT * FROM [database2.csv]"
MyRecordSet.Open MySQLStatement, MyConnection, 3,3
Set MyMeta = CreateObject("MetaDataLib.MetaFile")
MyMeta.LoadFromFile(Watch.GetMetadataFileName)
Set MyGroup = MyMeta.Job.Group(0)
For x = 1 to MyGroup.Count
MetaCustID = trim(MyGroup.Document(x-1).Fields.ItemByName("customerEmail"))
if MetaCustID <> "" then
MyRecordSet.MoveFirst
MyRecordSet.Find "CustomerID = '" & MetaCustID & "'"
If not((MyRecordSet.BOF) OR (MyRecordSet.EOF)) Then
MyGroup.Document(x-1).Fields.Add "customerEmail",MyRecordSet.Fields("customerEmail").Value
end if
end if
next
MyMeta.SaveToFile(Watch.GetMetadataFileName)It's a little hard to explain, but this code uses the METADATA A.P.I. to perform a database lookup in the database2.csv file, then update the Metadata field called customerEmail with data coming from the database.
I think the important point at this stage is to understand how to work with the Metadata A.P.I.: dot notation, LoadFromFile, etc. In other words, try to make sense of what is done with the object of type
MetaDataLib.MetaFile.
8. The output task should use the same PlanetPress Design document as the one used from the beginning.In other words, the PlanetPress design document is dry runned first by the CreateMetadata plugin, in order to create the initial Metadata structure. The metadata structure then travels along the process with the data file. At the run script stage, the metadata structure is transformed by the script. In fact the script only changes the value of the defined metadata field. In the end, the PlanetPress design document is executed with the original datafile, but with the updated metadata structure. So the result is that the data selection performed on the customerID now displays the customer's email.
Hope this helps!
Benoit
Previous Topic
Index