Outputting main form data AND related form data to Excel

Moderators: Phil Winkler, Graham Smith, Pete Tabord

Outputting main form data AND related form data to Excel

Postby Gil Fleming » Fri Jun 17, 2016 12:18 pm

We need to produce a report for our accountants. The report is from our invoice file so a simple invoicenum, invoicedate, invoicenett, invoicevat, invoicegross procedure is fine, and we can easily output this to a csv file and pick it up in Excel.

However, I want to add a little more functionality. Some customers take more than one bite (sometimes 6 payments) to pay an invoice. We store these in ffenics in a file called invpaylineitems.

A simple procedure like this:

Code: Select all
for invoices with InvoiceDate between 01/09/2014 to 31/10/2015 ;
list records
InvoiceNum in order ;
InvoiceDate ;
InvTotalItemsExVAT ; -- nett
InvVATAmount ; -- VAT
InvTotalItemsIncVAT . -- gross
for relinvpaylineitems ;
list records
PayDate ;
TotalAmount£ : item sum ;
PayMethod ;
BankReference .
end
end

Works fine for printed output, but I would like to be able to output this as a csv. I'm not a big fan of Excel so not sure if I can create some kind of related worksheet that stores the payment lines and is somehow linked to the invoice record on a separate sheet (in a RDBMS stylee).

Anyone help?
Gil Fleming
Director
Fleming Technical Limited

You can't think about what you don't know - Mike Fidler
If you can't fight, wear a big hat - John S Fleming
The best way to have a good idea is to have lots of ideas - Linus Pauling
Gil Fleming
 
Posts: 546
Joined: Tue May 15, 2012 10:26 am
Location: Liverpool, UK
Has thanked: 1 time
Been thanked: 2 times
 

Re: Outputting main form data AND related form data to Excel

Postby Adrian Jones » Tue Jun 21, 2016 4:31 pm

Not sure as I type about the Excel side of this, but there are two data structures to look at.

CSV is basically one dimensional, so you need to 'flatten' the data to include both parent and child stuff. Basically, this means that each row for the payments contains all the details about the invoice:

InvoiceNum,InvoiceDate,...,PayDate,TotalAmount£,...

and then leave it to Excel to worry about the totals. You're looking at a pivot table in Excel to get this to display in a parent-child way, though there may be other ways I'm not aware of.

Plan B would be XML formatting, which lets you have heirarchy in your data. I'll have to look at how Excel imports this -- have visitors as I type so will post and come back.
User avatar
Adrian Jones
 
Posts: 2000
Joined: Tue Sep 11, 2007 2:38 pm
Location: Cornwall, UK
Has thanked: 5 times
Been thanked: 4 times
 

Re: Outputting main form data AND related form data to Excel

Postby Gil Fleming » Tue Jun 21, 2016 4:57 pm

It is possible to flatten the data and still display related records. Less than ideal, but workable. Never used pivot tables or XML so I think this will suffice.


Code: Select all
define "r " number .
r := Filedelete ("Giltest.csv") .
r := TextOutLn("Giltest.csv", concat("Fleming Technical Limited - Invoice analysis for period: ", data-entry fromdate, " to ", data-entry todate) ) .
r := TextoutLn("Giltest.csv", "") .
r := TextOutLn("Giltest.csv", concat("Inv Ref", ";", "Inv Date", ";","Customer", ";",  "Nett", ";", "VAT", ";", "Gross", ";", "Pay Date", ";", "Pay Amount", ";", "Pay Method", ";", "Bank Ref", ";") ) .
for invoices with InvoiceDate between data-entry fromdate  to data-entry todate ;
r := TextOut ("Giltest.csv", concat (InvoiceNum, ";")) .
r := TextOut ("Giltest.csv", concat (InvoiceDate, ";")) .
r := TextOut ("Giltest.csv", concat (CustomerName , ";")) .
r := TextOut ("Giltest.csv", concat (numtotext(InvTotalItemsExVAT, "0.00"), ";")) .
r := TextOut ("Giltest.csv", concat (numtotext(InvVATAmount, "0.00"), ";")) .
r := TextOut ("Giltest.csv", concat (numtotext(InvTotalItemsIncVAT, "0.00") , ";")) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextoutLn("Giltest.csv", "") .
for relinvpaylineitems ;
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", ";" ) .
r := TextOut ("Giltest.csv", concat (PayDate, ";")) .
r := TextOut ("Giltest.csv", concat (numtotext(TotalAmount£, "0.00"), ";")) .
r := TextOut ("Giltest.csv", concat (PayMethod, ";")) .
r := TextOut ("Giltest.csv", concat (BankReference, ";")) .
r := TextoutLn("Giltest.csv", "") .
end
r := TextoutLn("Giltest.csv", "'                      ----------------------------------------------------------------------------------------------------------------------------------------------------------") .
end


In this example, I'm listing 6 things from each invoice, and 4 things from each invoice pay line. So, when I am building the csv file, I output 6 field values and 4 blank values per invoice, followed by 6 blank values and 4 field values per invoice pay line. The output is quite readable and shows payments below each invoice, offset to the right. The ----- bit just makes the output more readable. The single ' seems to make the line display beyond its cell.
Attachments
FlatExcel.png
FlatExcel.png (126.71 KiB) Viewed 2635 times
Gil Fleming
Director
Fleming Technical Limited

You can't think about what you don't know - Mike Fidler
If you can't fight, wear a big hat - John S Fleming
The best way to have a good idea is to have lots of ideas - Linus Pauling
Gil Fleming
 
Posts: 546
Joined: Tue May 15, 2012 10:26 am
Location: Liverpool, UK
Has thanked: 1 time
Been thanked: 2 times
 

Re: Outputting main form data AND related form data to Excel

Postby Graham Smith » Wed Jun 22, 2016 2:19 pm

Gil, what you are discussing can be done but it's something I've always resisted doing because it can be a pointless exercise designed for people who persist in using Excel in place of a database or word processor. Excel is great for manipulating data, but as soon as you create something like this, it's entire function becomes reduced to displaying data - you can no longer manipulate it.

If they want something electronic rather than printed, give them a PDF.
Graham Smith
DataSmith, Delaware
"For every expert there is an equal and opposite expert.", Arthur C. Clarke (1917 - 2008)
"X-Clacks-Overhead: GNU Terry Pratchett"
User avatar
Graham Smith
 
Posts: 2501
Joined: Fri Sep 07, 2007 11:31 am
Location: Delaware, USA
Has thanked: 0 time
Been thanked: 1 time
 
 

Return to Ffenics 1.x

Who is online

Users browsing this forum: No registered users and 8 guests

cron