List Box

[kev6brown]

Hi,

I have a list box, Drugs, but I only wanted to see the values that are being populated against patient_no. So rather than see every drug that has no associated patient i only want to see the drugs that have associated patients, how do i do that?

Kev

[QFabian]

Hi @kev6brown , if you want to do that in script, you can do something like this, the key is find a field present in both tables, in this case a use ID_Drug, but it can be patient_no if it is present in both tables.

EXISTS is a great function to filter as you want

Patient:

Load

patient_no,

Id_Drug,

more fields...

From yoursource;

Drug:

Load

Id_Drug,

Drug

From yoursource

Where

exists (Id_Drug, Id_Drug);

Please let my know if this is an acceptble solution for you

[kev6brown]

I can't access the script to do this, can it be done from the front end?

[QFabian]

In that case, please check this :

[marcus_sommer]

You could try it with an expression as listbox-source - something like this:

aggr(only({< Patient = {"*"}>} Drug), Drug)

- Marcus

[kev6brown]

That's perfect, thank you so much. Just to throw a curveball in, I have the following too -

=subfield([Drug],'!',1)

How do I make the expression include this?

[marcus_sommer · Accepted solution]

Try it with:

subfield(aggr(only({< Patient = {"*"}>} Drug), Drug), ,'!',1)

- Marcus