With the help of some Altru staff, I have successfully figured out how to create a query and mail merge group sale invoices saving me several hours a month. It is not perfect by any means but it works in the end.
This is a work in progress and I will have updates once I get time to mess with it again.
PLEASE NOTE: This query only works for group sales order with 1 or 0 payments. Future updates may be an export definition with all payments. If anyone has any updates or great ideas, please add them below!
1. Open/print the group sales revenue report for your selected date range and select "Show only reservations with outstanding balances". You will use this to double check your query.
2. Build reservation query (see below). I have named my output fields for an easy mail merge.
3. Export your query to Excel.
4. Create a new column and call it "balance due". In this new column subtract the "order total" column form the "total paid" column. Paste the formula for all rows.
5. Subtract all rows that have a negative or $0.00 balance due. The remaining is what you will use for your mail merge.
6. Add the total of the remaining "balance due" rows to make sure it matches your Group Sales Revenue Report. If it doesn't, there may be an order with multiple payments on it.
7. Since Altru only allows the reservation name and not the organization you have two options. I. Insert a new column after the Reservation Name. In excel, under the tab "data" select text to columns, select "delimited", unselect "tab", select "other" and put a dash in the empty box. But be careful: If your organization name or contact name has a dash in it it will delimit those as well.
II. If you don't want to do the above, every time a reservation is made, simply delete everything except the organization name.
8. In Word, design your invoice template and create a mail merge using your newly created excel document. We have letterhead so I have left space for our header and footer.
NOTE: Because Excel and Word don't like each other, I found out that even though a column in Excel is set to currency you will need to toggle the merge field in Word and add "\$,0.00" at the end of your merge field. For example, for previous payment it should look like this: {MERGEFIELD Total_Paid \#$,0.00}. To toggle your merge field simply select the merge field, right click and select "Toggle Field Codes".