Skip to Content (Press Enter)
2013-06-17 13-57-20
Arnold Grippeling
1Views
2Comments
Combo box
Question

To enter a new record in the Invoiceline.Edit I would like to chose the invoice for which {Kind} is already selected.
However I don't get the {Kind} that has been selected in the invoice, but I just get the {Kind} that is the first record in table {Kind}

Can anyone enlighten how to change the SQL statement, that I've used.

SELECT {Invoice}.[Id], {Verwijsbrief}.[Disicpline] + ' - ' + {Kind}.[Voornaam] + ' ' + {Kind}.[TussenvoegselKind] + ' ' + {Kind}.[Achternaam] + ' maand: ' + convert (varchar, {Invoice}.[Maand]) + ' - ' + convert (varchar, {Invoice}.[Jaar])

FROM {Invoice} INNER JOIN {Verwijsbrief} ON {Verwijsbrief}.[Id] = {Invoice}.[Id]
                 INNER JOIN {Kind} ON {Kind}.[Id] = {Verwijsbrief}.[Id] 
ORDER BY {Kind}.[Voornaam]


Another QUESTION for the combobox:
I would like to see in the combobox (in {invoiceline}.edit only the therapost (Physical therapey, Speech therapy or Occupational therapy) that has been connected to the {invoice} by {Verwijsbrief}.
The logic is that {invoiceline} has a relationship with {invoice}
    {invoice} has a relationhip with {verwijsbrief} and in each record of {verwijsbrief} it is indicated which therapist is selected 
        {verwijsbrief} has a relationship with [therapeut -> the table that contains all the therapists}.

For that I've used the following SQL statement, but I get no results. Can anyone help me out/ point me in the right direction?


SELECT {Therapeut}.[Id], {Therapeut}.[Voornaam] + ' ' + {Therapeut}.[Tussenvoegsel] + ' ' + {Therapeut}.[Achternaam] + ' - ' + {Therapeut}.[Discipline] FROM {Therapeut}, {Verwijsbrief} WHERE {Therapeut}.[TherapeutActief] = 'Yes' AND {Therapeut}.[Discipline] = {Verwijsbrief}.[Disicpline]

Edited by André Vieira on request of user to remove attachment.
0
0
06 Oct 2014
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
Hi Arnold,

It's difficult to say what's the problem without knowing the data model, but you are joining Invoice, Verwijsbrief and Kind all on the same Id. Is this really what you want to be doing? It seems more likely you mean to do something like this:

INNER JOIN {Verwijsbrief} ON {Verwijsbrief}.[Id] = {Invoice}.[VerwijsbriefId]
INNER JOIN {Kind} ON {Kind}.[Id] = {Verwijsbrief}.[KindId]

Assuming that an Invoice is linked to one specific referral letter, and each referral is linked to one specific child.

As for the other question, never use multiple tables in the FROM statement. This is old fashioned code predating INNER JOINs and should really never, ever be used. That said, if I understand you correctly, you want to create a list of all applicable therapists so you can select one from a combo box for each invoice line, and the applicable therapists are connected to an invoice via the referral letter, each referral having only one disciplin, and you want all therepists that are applicable for that disciplin (and all therapists have only one disciplin)? In that case, something like this:

SELECT <whatever>
FROM {Verwijsbrief}
INNER JOIN {Therapeut} ON {Therapeut}.[Discipline] = {Verwijsbrief}.[Discipline]
  AND {Therapeut}.[TherapeutActief] = 'Yes'
WHERE {Verwijsbrief}.[Id] = {Invoice}.[VerwijsbriefId]    -- assuming that invoice has such an attribute

Alternatively, you could use a simple query with the same structure, and post-process the list (which will be small, so no speed penalty there) to make a new list for the combo box. The advantage would be that you can have an action that combines the name and discipline of the therapist for display, one you may use elsewhere as well, which is more maintainable than putting it in a query.

As a last point, is this database you are querying set up by someone else or by you? In the latter case, I would advise using booleans for active flags, instead of text litterals "Yes" and "No".
0
0
09 Oct 2014
Copy Link
2020-09-15 13-07-23
Kilian Hekhuis
 
MVP
P.S. For lack of time, I haven't looked at the included eSpace, so some of the assumptions above may be wrong.
0
0
09 Oct 2014
Copy Link
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.