Create Invoice

Hi There,

I'm trying to create invoices.
1. I create several invoicelines (invoicenumber remains 0/Null)
2. For the selection Zorgverzekeraar = True I would like to create the invoice for each different Zorgverzekeaar..
I've created an action:  CreateInvoiceZorgverzekeraar.However If I execute this action on the InvoiceList, the database isn't updated. 

Earlier I received the information below to make this happen, but I'm puzzled a bit how to get any further:
For the invoice to Zorgverzekeraar (=Insurancecompany) I have chosen to do the no. 2)
1) Make an advanced query that contains the right UPDATE statement. It could have as parameter the Id of the child and the invoice number to assign (something along the line of "UPDATE {InvoiceLine} SET {InvoiceLine}.[InvoiceNumber] = @NewInvoiceNumber WHERE {Kind}.Id = @KindId AND {InvoiceLine}.[InvoiceNumber] IS NULL" (or = '' in case it's empty instead of NULL).
2) Do it programatticaly: first make a (simple) query that gets all InvoiceLines for a given child that are empty, then loop over the result set with a For Each, set the invoice number (something like qGetEmptyInvoices.List.Current.InvoiceLine.InvoiceNumber = NewInvoiceNumber) and do an UpdateInvoiceLine (the entity update action available from the entity view).

Does anyone have an idea/suggestion?

Arnold Grippeling
Arnold -

Do it programmatically. The way you have outlined it makes sense.

I've tried it in 2 different ways, but either way I get an error, when testing the advanced query. See attachment for the screen prints. I've set it up for Zorgverzekeraar (= Insurancecompany) and Klant (=Client). Does anyone haven an example for updating a record; this doesn't seem to work in how I've set it up. Regards, Arnold Grippeling

Sorry to ask the obvious but did you try doing this query outside of the OS environment i.e. in SQL manager? (Of course with parameters filled)

Arnold -

I don't use Advanced Queries for UPDATE statements, but I bet that at least *some* of your problem is that Invoice is NOT a valid structure for the output and if it is not the immediate problem, it will become a problem once you solved the immediate problem.

Why are you using an Advanced Query here at all? I would try doing this the standard way and it will work just fine.

Hi Arnold,

So here are my 2 cents.
  1. On your first query you should use is NULL instead of = NULL in the where condition. Check the reasoning here.
  2. You are testing queries that change the database in your development environment using the Test button. The OutSystems Platform on this operation rolls back these operations. Can you check if you have runtime error?
  3. The output of the Advanced Query is irrelevant for UPDATE, INSERT and DELETE queries. The database does not return any result set in this case so regardless of what entities/structures you put in the ouput of the query they'll be empty once you run the query.
  4. When the query is executed if any error occurs it is thrown as an exception that will be caught by your exception handler and registered. You should use the cog icon on the toolbar (the third from the left) to go into the management console and there check the error log to see if you have errors for this operation.
I hope this helps.
Thx all!

I'm going to take a look at your comments.