[Ask] How to Identify Invoice Items from Prorata Invoice and Recurring Invoice

[Mas-J]

Dear All,

My Goal: Select Invoice Items  that come from Prorata Invoice or Recurring Invoice from database

Problem: I didn't find any relation between tblinvoices & tblinvoiceitems with tblorders.

Question: Which table that I should  join so it can be related and I can get my correct result ?

 

[brian!]

3 hours ago, Mas-J said:

Problem: I didn't find any relation between tblinvoices & tblinvoiceitems with tblorders.

for a new order, tblorders can contain reference to an invoice, e.g there is an invoiceid field in the table, and that refers to the id field in tblinvoices.... similarly, there is an invoiceid field in tblinvoiceitems which contains the individual line items for that invoice.

however, bear in mind that an admin could create an order with no invoice, and that renewal invoices are not linked to orders - so there would be no direct link between an order and a later renewal invoice in the tables.

[Mas-J]

19 hours ago, brian! said:

for a new order, tblorders can contain reference to an invoice, e.g there is an invoiceid field in the table, and that refers to the id field in tblinvoices.... similarly, there is an invoiceid field in tblinvoiceitems which contains the individual line items for that invoice.

however, bear in mind that an admin could create an order with no invoice, and that renewal invoices are not linked to orders - so there would be no direct link between an order and a later renewal invoice in the tables.

@brian! Thank you for the insight.
However I've a prepare a 2 different query for last solution and tell my admin team to tick every new order to tick create invoice checkbox and don't send to client if there's need a modification for date period. With that, I can differentiate where's the Invoice come from new order and recurring.

I still curious, how to get the list of item from this order page if when admin create manual order without tick the Create Invoice ?
 

 

[Mas-J]

On 2/2/2021 at 3:02 PM, Mas-J said:

@brian! Thank you for the insight.
However I've a prepare a 2 different query for last solution and tell my admin team to tick every new order to tick create invoice checkbox and don't send to client if there's need a modification for date period. With that, I can differentiate where's the Invoice come from new order and recurring.

I still curious, how to get the list of item from this order page if when admin create manual order without tick the Create Invoice ?
 

 

Got the answer from  Support, may it help someone who want select the query to get detail description on the order page

Quote

The order details description is the product name, which is associated via a query to tblhosting, tbldomains and tblhostingaddons on the orderid field.