1. Thank you for all the good wishes. I'm headed in for surgery (lumbar fusion) tomorrow, Thursday July 31st, I'll be in hospital for three days, and out of action for a few days after that. See y'all in about a week! Hugh.

Filter cascading dropdown menu element

Discussion in 'Standard Support' started by dimoss, Apr 14, 2012.

  1. dimoss Member

    Level: Standard
    Hi,

    I want to filter a cascading dropdown menu element to show ONLY these values that haven't been already submited. Is this possible and how?
    Thanks in advance!

    Dimos
  2. dimoss Member

    Level: Standard
  3. cheesegrits Support Gopher

    Level: Community
    Can you give the query you used as a specific example, might help others?

    I suspect it looks something like:

    WHERE {thistable}.id NOT IN (SELECT DISTINCT cdd_name FROM main_table)

    ... where cdd_name would be the CDD you are putting the where clause on, and assuming it uses 'id' from the joined table as the key.

    -- hugh
  4. dimoss Member

    Level: Standard
    Hi Hugh,

    I will try to explain what I did:

    As I said I have a cascading dropdown menu element which takes values watching another element in the same table

    The ccd elemenent named: fab_jbs.player_id
    The watching elelemnt named: fab_jbs.tournament

    In the query field i put the following without the WHERE
    player_id NOT IN (SELECT fab_jbs.player_id FROM fab_jbs WHERE fab_jbs.tournament = '{fab_jbs___tournament_raw}')

    Hope it helps

    Dimos
  5. dimoss Member

    Level: Standard
    Hi Hugh,

    I discovered that although the above SELECT statement works ok however when I click to edit the record I can't find the value I submitted. It shows 'Please select'
    I know that is filtering but how it's possible to show the value I entered?

    Maybe if there was an option to apply the Advance where query filter in New records only. I saw that there is such an option in the database join element where someone can choose to apply the where query in New or Edit or Both.

    Thanks in advance!

    Dimos
  6. dimoss Member

    Level: Standard
    Friendly bump..:)
  7. cheesegrits Support Gopher

    Level: Community
    Yeah, I can see how that query would filter out it's own record, once you've saved it, as the one you selected and saved will now be in the dataset from the subquery.

    You could probably tweak the query to add something like ...

    AND fab_jbs.player_id != '{fab_jbs___player_id_raw}'

    ... to your subquery's WHERE.

    Although yes, we do need to add the same "apply where when" option to the CDD as we have on the main join element.

    -- hugh
  8. dimoss Member

    Level: Standard
    Hi Hugh,

    I added the tweak you gave me in the WHERE subquery but it didn't work!

    Dimos
  9. dimoss Member

    Level: Standard
    Friendly bump..:)
  10. dimoss Member

    Level: Standard
    Ok. Problem solved using Hugh's tweak!
    Thanks Hugh!
  11. bea Active Member

    Level: Standard
    Hi Hugh,

    i have ran into the same problem and tried to use your solution but without success.

    It would be great to have the same "apply where when" option as on join.

    Could this be done as a job ;) ?


    Cheers,
    Bianka


  12. cheesegrits Support Gopher

    Level: Community
    Done.

    -- hugh
    1 person likes this.

Share This Page