Database invoice <-> payments

I did migrate from V4 to V5 a couple weeks ago.
Despite of not really happy with the new version, i discover a strange thing today.

I used to run my own SQL query to get the expenses and payments quarterly ordered by Payment Date e.g:

SELECT c.name, i.number, i.date, pay.date, pay.amount, i.private_notes, i.public_notes
FROM payments pay, clients c, invoices i
WHERE pay.client_id = c.id
AND pay.invoice_id = i.id
AND pay.is_deleted=0
AND pay.payment_date >= ‘2021-10-01’ AND pay.payment_date <= ‘2021-12-31’
ORDER BY pay.payment_date ASC

But it seems the payment table does not have any field named invoice_id any more?
How is the database connection between invoice and payments in V5?

Thanks in Advance,
Stefan

Hi,

If there are specific aspects of v5 you aren’t happy with please provide details so we can improve it.

In v5 payments and invoices are a many-to-many relationship (one payment can be applied to multiple invoices, in v4 this wasn’t supported). Invoices and payments are connected through paymentables.

Thanks for your help. i missed the paymentable_id is actually the same as the invoice_id…
I have updated my SQL query like following:

SELECT pay.id, c.name, i.number, i.date, pay.date, pay.amount, i.private_notes, i.public_notes
FROM payments pay, paymentables glue, clients c, invoices i
WHERE pay.client_id = c.id
AND pay.id = glue.payment_id
AND glue.paymentable_id = i.id
AND pay.is_deleted=0
AND pay.date >= ‘2021-10-01’ AND pay.date <= ‘2021-12-31’
ORDER BY pay.date ASC;

Regarding things i am not too happy about in V5:

  • i feels slower than V4
  • V4 had direct URL endpoints for different actions e.g. https://ninja.example.com/invoices/182/edit
  • in text-fields it is difficult to position the cursor (problem only exits in firefox 97 - chromium 98 is ok) Text-Fields
  • i don’t get how the reports are working at the moment (how do i set the time-range?) btw it would be nice to be able to make some custom reports (e.g. “Profits and Loss” Report sorted by Payment Date not Invoice Date - the reason for doing my custom SQL Queries…)
  • the total amount of money i paid for a vendor is always zero - maybe a bug? (vendor → click on vendor → overview tab → total 0€)
  • had some difficulties with custom design (there is a bug with overlapping footer in custom design already closed in github, but not yet released)
    Generally it would be great to select which invoice is previewed while editing the design - this would help design special features like task-table a lot
  • PDF Preview is not working (tried snap_pdf and hosted_ninja) - had no time to investigate further
  • Need to investigate if/how mailing is working and generally need to get used to V5…

But there is also a thing i really like about V5: there is a setting for the invoice number being generated on “send invoice” and not on “save invoice”. :clap:

Thanks for the feedback!

Have you tried the desktop app, it’s performs much faster than the web app. We’re working on a new React based web app to improve the performance.

You can filter reports using the filter row at the top of the data, to see a date range you need to filter a date column.