Thank you Adrien. Your comments have been very helpful.
I'm running IIS and MySQL on my PC as I do some database work... having moved
from Micrsoft Access... but I don't want to make all our small staff to have to
be running a server and mysql.
The way we have it setup is the gnuCash file is saved locally on our pc's and
mirrored on a special Google Drive account... meaning we setup a special Google
Drive account and sync'd it to our local machines.
Now we can use gnuCash at work or at home as the account file is sync'd to
those computers and available on the local hard drive.
I'll have to think carefully before charging off and trying to create my own
custom queries for gnuCash.
I'll give the PieCash utilities a closer look.
Thanks again,
Fran3
On Wednesday, February 21, 2018, 3:05:19 PM EST, Adrien Monteleone
<adrien.montele...@gmail.com> wrote:
If you need MySQL, you have to install a MySQL server. (on any platform that
doesn’t have it included) IIS is one way to do that on Windows. You can also
install a WAMP stack. (Windows, Apache, MySQL, PHP) There are one-click
installs available online. I would highly recommend installing PhpMyadmin if
you go that route as it makes database admin a bit easier than the CLI
interface. (it uses a web browser)
SQLite is a flat-file db using SQL principles. It has some limitations and
doesn’t work well for all situations. iTunes is probably the most widely used
app to employ it. (I think it was even designed by Apple especially for iTunes,
then later released to the public as a general purpose db) One of the main
limitations is it does not store date/time stamps like MySQL or the XML file.
So you may have issues there with some otherwise well-formed queries.
You can query the file using SQL, and it has some internal representations that
are table-like, but it is not the same as MySQL/MariaDb.
It is not a version of MySQL, but you are correct, it does not require a server
instance. I’m not familiar with its use on Windows, but I’m sure that info
isn’t hard to find.
PieCash is a Python-like interface to the Gnucash data files. (in whatever
format they may be) It gives you functions you can use to write Python scripts
to accomplish your tasks, in this case, retrieving data for custom reporting.
I’ve yet to experiment with it myself, but it is discussed much here on the
list. Until Gnucash becomes a proper database application, it is probably the
easiest path for external reporting, data manipulation, and integration with
other apps. As far as I understand it, it is not a collection of programs to
execute. (though some examples may be included) That would be your own custom
Python scripts I just mentioned. The GitHub link I gave you from ebridges is
just such an example of some report scripts you can write using PieCash.
Though if you’ve saved in a db format (as opposed to XML) you can certainly
write SQL statements directly to read the Gnucash tables without using PieCash.
(just don’t WRITE to those tables!) Again, either via CLI or PhpMyadmin.
You could also write custom XML parsers in some other language if you like, but
PieCash already has that covered, so it’s probably easiest to not re-invent the
wheel.
Unfortunately, that’s all I have to offer. If you need more detailed help, I’ll
have to defer to someone already working with either PieCash or MySQL. (I plan
to, I just haven’t had the time to dig in yet)
Regards,
Adrien
> On Feb 21, 2018, at 11:12 AM, Fran_3 <mailbox0...@yahoo.com> wrote:
>
> Re: Converting to MySQL...
>
> When I develop in MySQL on Windows I install IIS localhost server and
> MySQL... but that is not practical on other office computers.
>
> 1 - I'm not familiar with sqLite but a quick Google makes me think it is a
> way to run MySQL on a local PC without running server software like IIS...
> right?
>
> 2 - Or is sqLite a desktop version of MySQL that requires no server?
>
> 3 - Also, it looks like PieCash is a collection of Python programs... right?
>
> 4 - And if so then you simply have to have the Python interrupter installed
> on your machine and then run the appropriate PieCash program after editing in
> the name and path to the gnuCash file... right?
>
> Thanks again Adrien.
>
> Fran3
>
>
>
>
>
>
> On Tuesday, February 20, 2018, 9:54:08 PM EST, Adrien Monteleone
> <adrien.montele...@gmail.com> wrote:
>
>
> In order to use SQL on the Gnucash data, it needs to be saved in that format.
> This requires the particular backend drivers (sqlite3, MySQL) be installed
> already for the format to be available as a choice in the Save As dialog. See
> the installation notes on the wiki for more info.
>
> There is no built-in facility for writing queries. You’d have to access the
> datafile from something else.
>
> You may be interested in PieCash which allows for accessing either the XML or
> SQL formatted data file for report writing.
> https://github.com/sdementen/piecash
>
> This was also posted to the mailing list recently and is an example of using
> PieCash: https://github.com/ebridges/accounting-reports
>
> Before you get started, you’ll want to look over this:
> https://wiki.gnucash.org/wiki/FAQ#SQL_Database, and
> https://wiki.gnucash.org/wiki/SQL and mind the warnings never to WRITE to the
> data file from outside of Gnucash. (at least for the foreseeable future)
>
> Here’s the SQL table map - yes, it’s ugly:
> https://wiki.gnucash.org/wiki/images/8/86/Gnucash_erd.png
>
> Unless you are comfortable with Scheme, or learning a new language, your best
> bet is definitely to stick with the SQL/PieCash route.
>
>
> Regards,
> Adrien
>
> > On Feb 20, 2018, at 4:59 PM, Fran_3 <mailbox0...@yahoo.com> wrote:
> >
> > Thanks Adrien,
> >
> > Regarding custom reports... I do SQL but am having gnuCash save account
> > files in it's default XML format.
> >
> > Where would I get a list of the tables in gnuCash and their associated
> > fields/columns?
> >
> > Is there a window I can open to just put in an SQL query or what?
> >
> > Can I create an SQL query that will query the default XML format file
> > format?
> >
> > (I've actually never considered using XML for a full fledged database until
> > I just started using gnuCash and discovered that it uses XML)
> >
> > Or do I have all of this wrong?
> >
> > thanks again.
> >
> >
> >
> > On Tuesday, February 20, 2018, 5:14:38 PM EST, Adrien Monteleone
> > <adrien.montele...@gmail.com> wrote:
> >
> >
> > You could leverage a multi-column report perhaps with a customer report in
> > each column, that will get you invoice & payment detail, but no aggregate
> > totals across all customers. (but each customer would have it’s own total)
> >
> > Then there is the P&L/Income Statement, but it will show you revenue by
> > type (account) not by customer. It will have a total however.
> >
> > Finally, there is the Customer Report. The Sales column will show what you
> > want. Though you may not need the other columns, the only one you can turn
> > off is the expense column. I’ve yet to figure out where to put costs or
> > markup figures for that report to make much sense. It will report a sales
> > total per customer with a grand total.
> >
> > If you want to see revenue by type AND customer with totals, you’ll have to
> > make your own report either with SQL or Scheme.
> >
> > All of these reports (as are any others in GnuCash) allow you to set either
> > the date of the report, (such as for a Balance Sheet) or the date range the
> > report covers. There are shortcut date selectors for month, quarter, year
> > and ‘accounting period’ (if your fiscal year differs from the calendar) as
> > well as boxes to let you put in any specific dates you want. You’ll find
> > that under the General tab of any report options.
> >
> >
> > Regards,
> > Adrien
> >
> > > On Feb 20, 2018, at 3:41 PM, Fran_3 via gnucash-user
> > > <gnucash-user@gnucash.org> wrote:
> > >
> > > I'm sure there is a way but I haven't found it yet...
> > > How do we run a report to see how much business we booked/invoiced for a
> > > particular month?Total and/or including a list of customers and amounts?
> > > Ditto for a specified period of time... current quarter, last quarter,
> > > so far this year... so far this date last year... etc?
> > > Thanks for any help.
> > > Fran 3
> >
> > > _______________________________________________
> > > gnucash-user mailing list
> > > gnucash-user@gnucash.org
> > > To update your subscription preferences or to unsubscribe:
> > > https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > > If you are using Nabble or Gmane, please see
> > > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > > -----
> > > Please remember to CC this list on all your replies.
> > > You can do this by using Reply-To-List or Reply-All.
>
> >
> > _______________________________________________
> > gnucash-user mailing list
> > gnucash-user@gnucash.org
> > To update your subscription preferences or to unsubscribe:
> > https://lists.gnucash.org/mailman/listinfo/gnucash-user
> > If you are using Nabble or Gmane, please see
> > https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> > -----
> > Please remember to CC this list on all your replies.
> > You can do this by using Reply-To-List or Reply-All.
>
> _______________________________________________
> gnucash-user mailing list
> gnucash-user@gnucash.org
> To update your subscription preferences or to unsubscribe:
> https://lists.gnucash.org/mailman/listinfo/gnucash-user
> If you are using Nabble or Gmane, please see
> https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
> -----
> Please remember to CC this list on all your replies.
> You can do this by using Reply-To-List or Reply-All.
_______________________________________________
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
_______________________________________________
gnucash-user mailing list
gnucash-user@gnucash.org
To update your subscription preferences or to unsubscribe:
https://lists.gnucash.org/mailman/listinfo/gnucash-user
If you are using Nabble or Gmane, please see
https://wiki.gnucash.org/wiki/Mailing_Lists for more information.
-----
Please remember to CC this list on all your replies.
You can do this by using Reply-To-List or Reply-All.
