Not In Query on select List

NewApexCoder

Apex 4.2

Two Tables

SITES

  Site_id (pk)

  Site_name

  Date

ACTIVATION

   Activation_id (pk)

   Activation_date

   Deactivation_date

   Site_id (fk)

* I have a report based on the ACTIVATION table. Upon clicking an edit link on any row of the report, it takes you to a form to edit the information. The form allows you to edit the Site_id. The Site_id is represented as a select list with the following LOV query:

SELECT SITE_NAME, SITE_ID

FROM SITES

* This select list gives me all of the site_names, however, I want to filter this select list by only showing those sites that are not in the report. All of the site names are as follows:

Maryland

Los Angeles

Natchez

Bakersfield

Austin

The report has rows:

Activation ID     Site Name              Activation Date     Deactivation Date

  1                         Natchez                    3/11/2013         

  2                         Los Angeles               5/27/2020

I would like for the list to only display the ones that are not in the report, i.e. Austin, Bakersfield, Maryland.

I have tried to write a NOT IN query. When I click an edit link from the report to navigate to the form, the select list shows the list of entries that I desire, however, the row that I clicked shows the id of the Site Name instead of the actual name. I'm not sure where my error is. I hope all this makes sense. Thanks for your help in advance. Oh, and my query that I wrote is as follows:

SELECT SITE_NAME, SITE_ID

FROM SITES

WHERE SITE_ID NOT IN (SELECT SITE_ID

FROM ACTIVATION

WHERE this = that)

Erick Diaz

The second (LOV):

SELECT SITE_NAME, SITE_ID
FROM SITES
WHERE SITE_ID NOT IN (SELECT SITE_ID
FROM ACTIVATION
WHERE this = that)
OR SITE_ID = :PXX_SITE_ID

Thank you,

Erick

Erick Diaz

Hi,

This is because the query is excluding the current site you are trying to modify since it is actually in the report.

You can include in your query:

or site_id = :Pxx_SITE_ID

Where Pxx_SITE_ID is the item holding the site_id value and xx is your page number.

Thank you,

Erick

NewApexCoder

@ediazjorge

Greetings

Would that statement be included in the first where clause or the second one?

Erick Diaz

The second (LOV):

SELECT SITE_NAME, SITE_ID
FROM SITES
WHERE SITE_ID NOT IN (SELECT SITE_ID
FROM ACTIVATION
WHERE this = that)
OR SITE_ID = :PXX_SITE_ID

Thank you,

Erick

NewApexCoder

Thanks @ediazjorge !!!! That worked like a charm