Method and apparatus for generating multiple documents using a template and a data source

[0001] Word processing applications provide a mechanism to generate multiple documents from a single template document. This document-generation mechanism is commonly referred to as “mail merge.” The mail merge process begins when a user creates a template document. The template document typically contains text and mail-merge fields which are placeholders for data. The mail merge process uses a data source to associate the mail-merge fields with a set of records. After creating the template document, the user executes mail merge. For each record in the data source, mail merge creates a replica of the template document and populates the mail-merge fields within the replica with the data contained in the record. In this manner, mail merge can generate multiple documents using a template and a data source.

[0002] Unfortunately, mail merge cannot be used for generating documents by using a data source that contains hierarchical records. For example, suppose we want to send letters to customers with a list of items that they bought during the past year. The data source may include a set of sales receipts, which often contain hierarchical records. Specifically, a sales receipt usually includes parent records which contain data related to the transaction as a whole, such as, the customer's name, the customer's address, etc. The sales receipt may also include one or more child records which contain information related to an individual item that a customer purchased, such as, the item description, the item price, etc. The letter template usually contains mail-merge fields for the customer's name, the customer's address, etc. which can be resolved using the parent records. However, note that the letter template cannot contain a list of mail-merge fields that describe the items that a customer bought during the past year because each customer typically buys a different set of items. Hence, mail merge cannot be used to generate letters to customers that contains a list of items that the customer bought during the past year.

[0003] One embodiment of the present invention provides a system that generates multiple documents using a template document and a data source. During operation, the system receives the data source, which includes parent records, child records, and metadata which describes the data stored in the parent and child records, wherein a parent record is associated with one or more child records. Next, the system receives the template document which includes one or more tables. The system then generates a flat data source from the data source, wherein the flat data source includes column names for the one or more tables in the template document. Next, the system generates an intermediate document using the template document by concatenating multiple replicas of the template document to obtain an initial version of the intermediate document. The system then uses the flat data source to populate the column names in the tables within the initial version of the intermediate document. Next, the system generates a final document by using the child records to populate rows in the tables within the intermediate document.

[0004] In a variation on this embodiment, while generating the intermediate document using the template document, the system uses mail merge functionality to concatenate multiple replicas of the template document to obtain the initial version of the intermediate document and to use the flat data source to populate the column names in the tables within the initial version of the intermediate document.

[0005] In a variation on this embodiment, after obtaining the initial version of the intermediate document, the system uses the parent records to populate the fields that are not in the tables within the intermediate document.

[0006] In a further variation, the system uses the parent records to populate the fields that are not in the tables by performing the following actions: for each parent record whose data is to be included in the final document, the system identifies a section of the intermediate document that is associated with the parent record, and populates fields that are not in the tables within the identified section using data from the parent record.

[0007] In a further variation, while generating the flat data source, the system imports values for the fields that are not in the tables from the parent records into the flat data source, and imports column names for the tables from the metadata into the flat data source.

[0008] In a variation on this embodiment, after generating the intermediate document, the system generates a mapping between the columns in the tables within the template document and the columns in the child records.

[0009] In a further variation, while generating the final document, for each parent record whose data is to be included in the final document, the system identifies a section in the intermediate document that is associated with the parent record. For each table in the identified section, the system uses the mapping to identify columns in the child records associated with the parent record that are to be imported into the table, and imports the values stored in the identified columns into the table.

[0010] In a variation on this embodiment, the records can include a purchase order document, a customer order document, a receiving document, a sales receipt, a transfer document, an adjustment memo, a work order document, or a lay away document.

[0028] The following description is presented to enable any person skilled in the art to make and use the invention, and is provided in the context of a particular application and its requirements. Various modifications to the disclosed embodiments will be readily apparent to those skilled in the art, and the general principles defined herein may be applied to other embodiments and applications without departing from the spirit and scope of the present invention. Thus, the present invention is not limited to the embodiments shown, but is to be accorded the widest scope consistent with the principles and features disclosed herein.

[0029] The data structures and code described in this detailed description are typically stored on a computer-readable storage medium, which may be any device or medium that can store code and/or data for use by a computer system. This includes, but is not limited to, volatile memory, non-volatile memory, magnetic and optical storage devices such as disk drives, magnetic tape, CDs (compact discs), DVDs (digital versatile discs or digital video discs), or other media capable of storing computer readable media now known or later developed.

Overview

[0030] One embodiment of the present invention generates multiple documents from a template document and a data source. In this embodiment, the data source includes one or more parent records, wherein each parent record includes one or more child records. In one embodiment of the present invention, the template document includes one or more tables which are to be populated with data from the child records.

[0031] In one embodiment of the present invention, the parent and child records are generated from a transaction between a vendor and a customer.

[0032] One embodiment of the present invention uses mail merge to populate mail merge fields that are not in tables with data from the parent records. Another embodiment of the present invention uses mail merge to populate mail merge fields in tables with the name of the mail merge field. In one embodiment of the present invention, the name of the mail merge field is the name of a column in the child record that is to be imported into the mail merge field.

[0033] In one embodiment of the present invention, after mail merge populates the mail merge fields within the template document with data from the parent records and with names for the mail merge fields in the table, a mapping between the mail merge fields in the tables within the template document and the columns in the child records is created. In one embodiment of the present invention, while importing the data from the child records, the mapping is used to determine which columns within the child records are to be imported into the columns within the tables in the intermediate documents. The final documents are generated after all child records have been imported into the intermediate documents.

Transaction Document

[0034] In one embodiment of the present invention, the parent records are transaction documents, wherein each parent record includes information about a given transaction. In another embodiment of the present invention, the each child record is an item related to a given transaction. In one embodiment of the present invention, a parent record can be associated with one or more child records.

[0035] In one embodiment of the present invention, a transaction document can include, but is not limited to, a purchase order document, a customer order document, a receiving document, a sales receipt, a transfer document, an adjustment memo, a work order document, and a lay away document.

[0036] Note that although the discussion in FIGS. 1 to 7C refers to a sales receipt, the present invention can be applied to any transaction document. FIG. 1 presents an exemplary sales receipt 102 in accordance with an embodiment of the present invention which includes items 104-108 purchased by a customer. As illustrated in FIG. 1, the first row of the table in receipt 102 indicates that the customer purchased one baseball bat (item 104) at a unit price of $6.00 per bat, for a total of $6.00. The customer also purchased four baseball balls each with a unit price of $2.00 per ball, for a total of $8.00 (item 106), and purchased one baseball mitt with a unit price of $10.00 per mitt, for a total of $10.00 (item 108). The total of all items in sales receipt 102 is $24.00. Note that the number of items contained in sales receipt 102 can be more or less depending on the number of items purchased by the customer. Sales receipt 102 also includes vendor name 110, vendor address 112, vendor phone number 114, customer name 116, customer address 118, customer phone number 120, date 122, and invoice number 124. Note that sales receipt 102 can contain more or less fields depending on the fields that the vendor selects to be included in a given receipt.

[0037] In one embodiment of the present invention, a vendor has one or more transaction documents from which the vendor desires to generate multiple final documents.

Data Source

[0038] FIG. 2 presents a block diagram of computer system 200 used to generate multiple documents from template document 232 in accordance with an embodiment of the present invention. Computer system 200 includes data source 202 and application 230. In one embodiment of the present invention, data source 202 and application 230 are on different computer system. For example, data source 202 can be on a database server and application 230 can be on an application server.

[0039] Data source 202 includes parent records 204, 206, and 208. Child records 210, 212, and 214 are associated with parent record 204, child records 216, 218, and 220 are associated parent record 206, and child records 222, 224, and 226 are associated with parent record 208. Note that although data source 202 contains three parent records, the number of parent records can be more or less depending on the number of transactions that have occurred. Also note that although each parent record in FIG. 2 is associated with three child records, each parent record can be associated with more or less child records depending on the number of items included in a given transaction.

[0040] Data source 202 also includes metadata 228, which describes the data structure of data source 202. In one embodiment of the present invention, metadata 228 includes the fields that are present in data source 202 and includes information about the fields. In one embodiment of the present invention, the information about the fields includes, but is not limited to, a field ID, a field name, a field label, and a field hint. Note that a field name is a database-specific name for a field, a field label is a name of a field that a user sees in application 230, and a field hint is text that is displayed when a user places a mouse pointer on top of a field displayed within application 230.

[0041] In one embodiment of the present invention, metadata 228 is stored in an extensible markup language (XML) file. In other embodiments of the present invention, metadata 228 is stored in a flat file, in a database, or in any data structure now known or later developed.

[0042] In one embodiment of the present invention, a vendor generates template document 232. Note that an exemplary template document is illustrated in FIG. 3A in accordance with an embodiment of the present invention.

[0043] In one embodiment of the present invention application 230 uses template document 232 to generate multiple final documents 236 from data source 202. In one embodiment of the present invention, application 230 generates one final document for each parent record stored in data source 202. In another embodiment of the present invention, application 230 generates final documents 236 from multiple data sources.

[0044] In one embodiment of the present invention, application 230 includes a receiving mechanism (not shown), a flat-data-source-generation mechanism (not shown), and a document-generation mechanism (not shown). The receiving mechanism receives the template document and the data source. The flat-data-source-generation mechanism generates the flat data source. The document-generation mechanism generates the final document using the template document and the data source.

[0045] In one embodiment of the present invention, the user uses a filter to select a specified subset of parent records to include in final document 236. In this embodiment, application 230 only generates final documents for the specified parent records.

[0046] In one embodiment of the present invention, application 230 first generates intermediate document 234 before generating final documents 236. In this embodiment, application 230 generates multiple intermediate documents from template document 232. In one embodiment of the present invention, application 230 generates an intermediate document for each parent record in data source 202. In another embodiment of the present invention, application 230 generates an intermediate document for each parent record specified by a filter for data source 202.

[0047] In one embodiment of the present invention, application 230 generates final documents 236 from intermediate documents 234 and data source 202. Note that this process is described in detail in reference to FIGS. 3 to 11.

[0048] In one embodiment of the present invention, application 230 generates a single final document from a single intermediate document and a data source. In this embodiment, application 230 creates a replica of the template document for each parent record to be included in the final document and places the replica on a different page of the final document. Note that the process described in FIGS. 3 to 11 can be applied to this embodiment.

[0049] In one embodiment of the present invention, the template is a template for a letter and the final document is a letter.

[0050] In one embodiment of the present invention, the application is Quickbooks® Point of Sale. Note that QuickBooks is a trademark and service mark of Intuit Inc., registered in the United States and other countries.

Template Document

[0051] FIG. 3A presents an exemplary template document 302 in accordance with an embodiment of the present invention. Template document 302 includes text and one or more mail merge fields. In FIG. 3A, a user inserts the following mail merge fields CUSTOMER NAME, RECEIPTNO, ITEMNO, DESCRIPTION, QUANTITY, UNITPRICE, AMOUNT, TOTAL, VENDOR NAME, and DATE. The mail merge fields correspond to fields in data source 202. In one embodiment of the present invention, fields CUSTOMER NAME, RECEIPTNO, TOTAL, VENDOR NAME, and DATE are fields in parent records. In another embodiment of the present invention, fields ITEMNO, DESCRIPTION, QUANTITY, UNITPRICE, and AMOUNT are fields in child records.

[0052] In one embodiment of the present invention, prior to generating final documents 236, application 230 generates a list of fields present in template document 302. FIG. 3B presents a list of fields extracted from the exemplary template document 302 in accordance with an embodiment of the present invention. The fields in template document 302 include CUSTOMER NAME, RECEIPTNO, ITEMNO, DESCRIPTION, QUANTITY, UNITPRICE, AMOUNT, TOTAL, VENDOR NAME, and DATE. One embodiment of the present invention compares the generated list of fields to a list of all possible fields in the data source to determine whether the mail merge fields within template document 302 are valid.

Flat Data Source

[0053] In one embodiment of the present invention, application 230 generates a flat data source from data source 202. In doing so, values for fields in the parent records are imported into the flat data source. In contrast, column names for the fields that correspond to the child records are imported into the flat data source. FIG. 4 presents an exemplary flat data source 404 in accordance with an embodiment of the present invention. Note that flat data source 404 contains ten columns, of which six are in the upper box, and the remaining four are in the lower box. Although FIG. 4 illustrates a data source with three records, each with ten columns, flat data source 404 can include more or less records and/or columns.

[0054] As illustrated in FIG. 4, the first record corresponds to a customer with customer name 116, who made a purchase from vendor 110 on date 122 recorded in receipt 102. Note that this record corresponds to receipt 102 in FIG. 1. The fields for ITEMNO, DESCRIPTION, QUANTITY, UNITPRICE, and AMOUNT are populated with the column names for the columns in the child records that correspond to the fields, and are not populated with the actual data from the child records. The column names are extracted from metadata 228. Similarly, the second record corresponds to a customer with customer name 404, who made a purchase from vendor 110 on date 410 recorded in receipt 408. The total for receipt 406 is total 408. The third record corresponds to a customer with customer name 412, who made a purchase from vendor 110 on date 418 recorded in receipt 414. The total for receipt 414 is total 416.

Intermediate Documents

[0055] FIG. 5A presents an exemplary intermediate document 502 that corresponds to the exemplary receipt in FIG. 1 in accordance with an embodiment of the present invention. Application 230 populates the mail merge fields within template document 302 to generate intermediate document 502 for receipt 102. As illustrated in FIG. 5A, the mail merge fields CUSTOMER NAME, RECEIPTNO, TOTAL, VENDOR NAME, and DATE are populated with the values customer name 116, 102, $24.00, vendor name 110, and date 122, respectively. The mail merge fields ITEMNO, DESCRIPTION, QUANTITY, UNITPRICE, and AMOUNT are populated with the column names “ITEM #,” “DESCRIPTION,” “QUANTITY,” “UNIT PRICE,” and “AMOUNT,” respectively.

[0056] FIG. 5B presents another exemplary intermediate document in accordance with an embodiment of the present invention. Application 230 populates the mail merge fields within template document 302 to generate intermediate document 504 for receipt 406. As illustrated in FIG. 5B, the mail merge fields CUSTOMER NAME, RECEIPTNO, TOTAL, VENDOR NAME, and DATE are populated with the values customer name 404, 406, total 408, vendor name 110, and date 410, respectively. The mail merge fields ITEMNO, DESCRIPTION, QUANTITY, UNITPRICE, and AMOUNT are populated with the column names “ITEM #,” “DESCRIPTION,” “QUANTITY,” “UNIT PRICE,” and “AMOUNT,” respectively.

[0057] FIG. 5C presents another exemplary intermediate document in accordance with an embodiment of the present invention. Application 230 populates the mail merge fields within template document 302 to generate intermediate document 506 for receipt 414. As illustrated in FIG. 5B, the mail merge fields CUSTOMER NAME, RECEIPTNO, TOTAL, VENDOR NAME, and DATE are populated with the values customer name 412, 414, total 416, vendor name 110, and date 418, respectively. The mail merge fields ITEMNO, DESCRIPTION, QUANTITY, UNITPRICE, and AMOUNT are populated with the column names “ITEM #,” “DESCRIPTION,” “QUANTITY,” “UNIT PRICE,” and “AMOUNT,” respectively.

[0058] In one embodiment of the present invention, intermediate documents 502, 504, and 506 can be represented using a single intermediate document in which the contents of each intermediate document 502, 504, and 506 are separated by a marker, such as a page break.

Field Map

[0059] In one embodiment of the present invention, prior to generating the final documents, a field map is generated which maps mail merge fields in the template document to columns in the child records. FIG. 6A presents an exemplary field map 602 that provides a mapping between mail merge fields in template document 302 and columns in the child records in accordance with an embodiment of the present invention. As illustrated in FIG. 6A, field map 602 maps each column of a given table within template document 302 to a mail merge field. Hence, column 1 in table 1 is mapped to ITEMNO, column 2 is mapped to DESCRIPTION, column 3 is mapped to QUANTITY, column 4 is mapped to UNITPRICE, and column 5 is mapped to AMOUNT.

[0060] In one embodiment of the present invention, for each table within the template document, the field map maps mail merge fields to columns in the child records. FIG. 6B presents another exemplary field map 604 in accordance with an embodiment of the present invention. In this example, the template document that corresponds to field map 604 contains three tables. Field map 604 maps columns in tables 606, 608, and 610 of the corresponding template document as follows. Columns 1-4 in table 606 are mapped to fields 612, 614, 616, and 618, respectively. Columns 1-2 in table 608 are mapped to fields 620 and 622, respectively. Columns 2-4 in table 610 are mapped to fields 624, 626, and 628, respectively.

Final Documents

[0061] In one embodiment of the present invention, a final document is generated for each intermediate document using the data source and the mapping. In this embodiment the columns in the table within a given intermediate document are populated with the data from the columns of the child records associated with the parent record that corresponds to the intermediate document.

[0062] FIG. 7A presents an exemplary final document 702 that corresponds to intermediate document 502 in FIG. 5A in accordance with an embodiment of the present invention. Application 230 generates final document 702 from intermediate document 502, data source 202, and field map 602. As illustrated, application 230 imports data from the appropriate columns of the child records associated with receipt 102.

[0063] FIG. 7B presents an exemplary final document that corresponds to intermediate document 504 in FIG. 5B in accordance with an embodiment of the present invention. In this example, the customer only purchased a single item 706 which has description 708, quantity 710, unit price 712, and amount 714. Similarly, FIG. 7C presents an exemplary final document 716 that corresponds to intermediate document 506 in FIG. 5C in accordance with an embodiment of the present invention. In this case, the customer purchased two items: (1) item 718 which has description 720, quantity 722, unit price 724, and amount 726, and (2) item 728 which has description 730, quantity 732, unit price 734, and amount 736.

[0064] In one embodiment of the present invention, final documents 702, 704, and 716 can be contained within a single document, wherein the contents of each final document 702, 704, and 716 are separated by a marker, such as a page break.

Generating Final Documents

[0065] FIG. 8 presents a flow chart illustrating the process of generating multiple documents from a data source in accordance with an embodiment of the present invention. The process begins when the system receives a data source, which includes parent records, child records, and metadata which describes the data stored in the parent and child records (step 802). In one embodiment of the present invention, a parent record is associated with one or more child records. The system also receives a template document which includes one or more tables (step 804). Next the system generates a flat data source from the data source, wherein the flat data source includes column names for the one or more tables in the template document (step 806). Note that step 806 is discussed in more detail in reference to FIG. 9 below. The system then generates an intermediate document using the template document (step 808). Step 808 is discussed in more detail in reference to FIG. 10 below.

[0066] Next, the system generates a mapping between the columns in the tables within the template document and the columns in the child records (step 810). The system then generates a final document by using the child records to populate rows in the tables within the intermediate document (step 812). Step 812 is described in more detail in reference to FIG. 11 below.

[0067] FIG. 9 presents a flow chart illustrating the process of generating a flat data source from a data source in accordance with an embodiment of the present invention. The process begins when the system imports values for the fields that are not in the tables from the parent records into the flat data source (step 902), and imports column names for the tables from the metadata into the flat data source (step 904).

[0068] FIG. 10 presents a flow chart illustrating the process of generating an intermediate document in accordance with an embodiment of the present invention. The system concatenates multiple replicas of the template document to obtain an initial version of the intermediate document (step 1002). The system then uses the flat data source to populate the column names in the tables within the initial version of the intermediate document (step 1004).

[0069] In one embodiment of the present invention, if the template document includes fields that are not in the one or more tables, after obtaining the initial version of the intermediate document, the system uses the parent records to populate the fields that are not in the tables within the intermediate document. Specifically, for each parent record whose data is to be included in the final document, the system identifies a section of the intermediate document that is associated with the parent record, and populates fields that are not in the tables within the identified section using data from the parent record.

[0070] In one embodiment of the present invention, mail merge functionality is used to concatenate multiple replicas of the template document to obtain the initial version of the intermediate document and to use the flat data source to populate the column names in the tables within the initial version of the intermediate document

[0071] FIG. 11 presents a flow chart illustrating the process of generating a final document from an intermediate document in accordance with an embodiment of the present invention. The process begins when for each parent record whose data is to be included in the final document, the system identifies a section in the intermediate document that is associated with the parent record (step 1102). In one embodiment of the present invention, for a given table within the intermediate document, a table index is calculated. In one embodiment of the present invention, the table index is the sum of: (a) the index of the parent record multiplied by the number of tables in the template document, and (b) the index of the given table within the intermediate document being processed. The system then uses the table index to locate the table within the intermediate document for the parent record. In one embodiment of the present invention, instead of generating a single intermediate document, multiple intermediate documents are generated, each being a replica of the template document. Hence, when using the table index to locate the table that corresponds to a given parent record, the table is located within the multiple intermediate documents. In this embodiment, the system generates a final document for each parent record.

[0072] For each table in the identified section, the system uses the mapping to identify columns in the child records associated with the parent record that are to be imported into the table (step 1104), and imports the values stored in the identified columns of the child records into the table (step 1106).

[0073] In one embodiment of the present invention, the system imports data from the child records into a given table within the intermediate document as follows. The system first calculates a row index for a child record that is to be imported into the given table within the intermediate document. The row index indicates which row of the given table is to receive the data contained in the child record. In one embodiment of the present invention, the row index is incremented sequentially after each child record is imported into the given table. If the row index for a given child record exceeds the number of rows available in the given table, another row is added to the given table. The system then uses the field mapping to determine which columns from the given child record, if any, are to be imported into the given table, and imports those columns into the given table within the intermediate document. The final document is the result of performing this process for all child records to be included in the final document.

[0074] The foregoing descriptions of embodiments of the present invention have been presented only for purposes of illustration and description. They are not intended to be exhaustive or to limit the present invention to the forms disclosed. Accordingly, many modifications and variations will be apparent to practitioners skilled in the art. Additionally, the above disclosure is not intended to limit the present invention. The scope of the present invention is defined by the appended claims.