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 > > > <firstname.lastname@example.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 > > > email@example.com > > > 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 > > firstname.lastname@example.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 > email@example.com > 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 firstname.lastname@example.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 email@example.com 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.