On Mar 27, 2012, at 11:27 AM, Derek Atkins wrote: > Reuben Cummings <reub...@gmail.com> writes: > >>>> I figured it out. The invoices from the entries table were never entered >>>> into the invoices table. I wrote a script to match the invoices with the >>>> transaction guids and insert them into the invoices table. >>> >>> I don't think I understand this issue. What do you mean by "The >>> invoices from the entries table were never entered into the invoices >>> table"? >> >> I'm referring to tables in the backend database. There are two tables >> that relate to invoices, i.e., entries and invoices. The two tables >> are related by the fields entries.invoice = invoice.guid. > > Umm, let's ignore the database and talk about the GnuCash objects, > please? You shouldn't be modifying the database, you should be using > the GnuCash APIs to enter your data.
I would like to use the API but I haven't gotten the python module to work. See my post at http://gnucash.1415818.n4.nabble.com/python-binding-error-on-macports-td4441220.html >>> In particular I don't understand what you mean by "invoices >>> from the entries table", >> >> This refers to the invoice field of the entries database table, i.e., >> entries.invoice. > > So you mean the gncEntryGetInvoice() result? I'll have to assume we are talking about the same thing since I'm not familiar with the objects. >>> and I don't understand what you mean by >>> "[entries] were never entered into the invoices table." >> >> I guess 'record' is more appropriate. The invoice field of each record >> in the entries table should have a corresponding record in the >> invoices table (joined at the guid field). > > Again, please consider using the object model instead of table. So you > are saying that the gncEntryGetInvoice() is NULL? Yes, that sounds right. > Is this a customer Invoice or a Vendor Bill? Customer invoice. > What about gncEntryGetBill()? I don't know since I've only been entering invoices. >>> Are you saying that the Entries on an invoice were never added to the >>> Invoice? >> >> So, I am saying that the importer created records in the entries table >> that had no corresponding record in the invoices table. You can view >> them with the following sql statement. > > What API did the Importer use to add the entry to the Invoice (or Bill)? I have no idea, I guess Mike Evans would know the answer though. >> SELECT >> entries.invoice >> >> FROM entries >> LEFT OUTER JOIN invoices ON entries.invoice = invoices.guid >> WHERE invoices.guid IS NULL >> GROUP BY entries.invoice > > Do you really need the join here? Yes. Since the orphan entries are identical to the non-orphan entries, the only way to filter them out is to join on the invoices table and exclude all the entries that match. That leaves entries with non-matching invoices... the orphan entries. > What about the entry bill? You mean vendor bills? I don't have any bills so I only need to look for invoices. >> What I did was match these orphan invoices with the appropriate >> transactions in the transactions table (the invoices and transaction >> tables are related by the transaction guid) to create the records that >> needed to be inserted into the invoices table. > > I still don't understand what you mean by "Orphan Invoices" here. I > think it's a terminology issue here. It sounds to me like you have > "Orphan Entries", not "Orphan Invoices", and I'm trying to understand > how these could be orphaned unless there is a bug in the importer. Yes, "Orphan Entries" is what I mean. And yes, there was a bug. >From above in this thread. > Mar 04, 2012; 1:29pm >> The patch has now been applied to 2.4 branch and will be in the next >> release. >> Mike Evans However, I needed a fix now to use until the next release comes out... and since i don't have access to the api (see above) I had no choice but to modify the database directly. > -derek _______________________________________________ gnucash-devel mailing list gnucash-devel@gnucash.org https://lists.gnucash.org/mailman/listinfo/gnucash-devel
