Hi Arnold,

Just for checking, both attribute identifiers are from me same type?

{Kind}.[Id] = {Verwijsbrief}.[Id]

Can you show me both tables with attributes and the relation between them?

Regards,
GM

Hi Goncalo,

I've enclosed the file. That should give you the info. you need.

Regards,
Arnold

Edited by André Vieira on request of user to remove attachment.

Hi Arnold,

I think you should have instead, the following:

SELECT {Verwijsbrief}.[Id], {Kind}.[Voornaam] + ' ' + {Kind}.[TussenvoegselKind] + ' ' +  {Kind}.[Achternaam] + ' ' + {Verwijsbrief}.[Disicpline] + ' ' + convert (varchar, {Verwijsbrief}.[DatumVerwijzing], 105) + ' ' + {Verwijsbrief}.[BehandelingAfgerond]
FROM {Verwijsbrief} INNER JOIN {Kind} on {Kind}.[Id] = {Verwijsbrief}.[KindId]
WHERE {Verwijsbrief}.[BehandelingAfgerond]= 'No'
ORDER BY {Kind}.[Voornaam]

It differs in the join condition:

{Kind}.[Id] = {Verwijsbrief}.[KindId]

Let me know if this solves your problem.

Kind Regards,
Gonçalo M.

Hi Goncalo,

That works! 
In my perception I've also tried this code, so I'll examine it carefully and see at what part I missed something.

Thanks!

One other question I have for which you may have an answer for or that you can guide me into the right direction.
I would like to create invoices. The design I have in place, but I'm struggling how to connect the invoicenumber  to the record(s) of the InvoiceLine.
All entries during a month for the customers (Kind) are enterd into the InvoiceLine table.
By the end of the month I would like to add an invoicenumber to alll the records in the invoiceline that don't have an invoicenumber.

Is there some kind script I can use or is it  individual coding that is necessary?

Regards,
Arnold

Hi Arnold,

First regarding the first query, if this is really the full query (and not an excerpt just for the example) I would advise you to use a simple query. This has the benefit of platform type checking (so the error you made would be visible), and also you could compose the string in an action, which would be more flexible. Also, in the output string, children without a tussenvoegsel have two spaces between first and last name...

With regards to your second question, you can do either of two things:
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).

That said, I think you'd be better off linking the InvoiceLines to an Invoice via an InvoiceId, that is having a separate invoice table whose Id is a foreign key in the InvoiceLines table.

Hi Arnold,

I think that Kilian just explained all the steps beautifully.
Try to follow his advices and let me know if you still need some adittional help.
Good luck on that.

Kind Regards,
Gonçalo M.

Thx Kilian,

For the simple query: how do I get the full string of data in a combobox using a simple query?
ie. ClientName (Arnold), InsuranceCompany (Achmea), Therapy (Physical Therapist)
In this advanced query I also use a structure (Id, Text) on which this code generates alle the records in the combobox.
(I've discussed this before and then I had been advised the advanced query incl. the sql code that I had to use. When using the simple query I only get to pick from the combobox one field, i.e. First Name Client, while I also needed to know what therapy was involved.)

Concerning my 2nd question.
The last option you mention, is the one I already use in my current access application. The downside of it, is that for each client that is in therapy I first have to create an invoicenumber (connecting a new invoicenumber/record to the client).
Some of the employees find that a bit cumbersome; I intend to agree to that.
But at that time it was the easiest way for me to set it up.
Therefore I'll have to think it over including the possible options in code you mentioned -> thanks for that!

Regards,
Arnold

Hi Arnold,

As for the combobox, I could say two things:
1) It's never a good idea to have large amounts of data in a combobox. As soon as you have more than 10-15 combinations of client, insurance co. and therapy, it is very difficult for users to select the right one. Instead, you may opt to have a search filter (some fields the user can fill and press a "Search" or "Filter" button), or an auto-complete field (Input_AutoComplete from RichWidgets). But I realize that's purely about usability, and doesn't answer your question.
2) In the specific case of filling a combobox, you indeed may opt for an advanced query like you have. Alternatively, you may create a second record list, that is created (by ListAppend) while looping (using a For Each) over the query result.

As for the invoices, I'm not sure I understand what you mean. First, if the foreign key is non-mandatory, you need not create an invoice until you are ready to do so (invoice lines will have NullIdentifier() for InvoiceId). Secondly, I don't see why users (if that's what you mean by "employees") would have to be bothered with creating invoices; you can surely do that programmatically, without their involvement. Or am I missing something?

HI Kilian,

Thx for the quick reply.
Let's see if I can get you a clear answer on both topics.

COMBO BOX
For the combobox I need some information in order to select the right record, in this case:
Firstname, Lastname, Therapy, InsuranceCompany (which in a later stadium I can leave out, but later on I need it for testing purposes). And I use a JGoodyWidget that gives me every record that matches the letters I type.

INVOICE
The problem I have is that I don't know how to create the invoicenumber and connect it to the invoicelines.
That's why at this moment (in my access application)I create an invoice and connect that to the client.at the beginning of each month-> indeed not afterwards.
Then at every treatment the employees/users connect the invoiceline to the invoicerecord of the client.
-> a bit cumbersome, but hey......
And indeed the foreign key isn't mandatory, but at some point I have to fill it. I prefer to do it at the end of the month, but I don't know how.
If you can shed a light how to do it afterwards, I would be gratefull

p.s.
Maybe the problem is that I'm not a real programmer :)

Hi Arnold,

Combo box: it seems you indeed use a kind of auto complete widget. Still, if there's no limit on the number of records that can appear in the combo box, over time it may get slow.

Invoice: You have various options to address this problem, I think.
1) If you create an invoice first, you can give it a status that indicates that it is an invoice that is not yet finished. Then, if you create an invoice line (assuming that all new invoice lines need to be on the most recentent, not finished, invoice), you can simply assign the Id of the invoice to the invoice line - no manual interaction by users needed.
2) If you want to create the invoice after creating the invoice lines, just leave the InvoiceId empty (or explicitly assign it NullIdentifier() if you think that's clearer) when saving the invoice line record. Then, when you need to invoice the invoice lines, create the invoice, and assign the new InvoiceId (you get it from the Create entity action on return) to the invoice lines. I've explained in an earlier post how to do that (either by an UPDATE advance query, or by programmatically looping over the invoice lines).

P.S. There are no "real" programmers, just good and lousy ones. If you're the latter, just practice until you're the first :)

Thx for your comments.

For the moment I'm a lousy one :)

For the combobox, I will have it checked on [active clients] so that the list will be limited and therfore should not get slow.

Arnold