1. Hugh is now back at work, more or less full time, after a slower than expected recovery from his neck surgery early this month. Obviously we have a backlog of support to clear, and are working as hard as we can to take care of it. If you have an unanswered thread more than 3 days old, please post one (and only one) "bump" on it, to move it in to our "last 3 days" list. If you have an unanswered issue in a forum you no longer have access to due to subscriptions timing out, please use the Contact Us form, and provide us with the URL to the thread.

how to restrict access to items in a dropdown menu that have been selected

Discussion in 'Community' started by hartcapl, Feb 11, 2009.

  1. hartcapl New Member

    Level: Community
    i want to create a dropdown menu of dates and times that registered users can select, but i want subsequent users to only have access to dates and times not yet selected, i.e. i only want one user per time.
  2. cheesegrits Support Gopher

    Level: Community
    How are you storing the dates/times?

    If they are in a table of their own, with each date/time having a unique (primary key) ID, you should be able to do it with a database join element, using a where clause that uses a subquery.

    Assume the main table is called 'mytable', the 'times' table has a PK called 'id' and the date/time column is called 'datetime'

    Create a database join element on 'mytable', joining to the 'times' table, with a key of 'id' and label of 'datetime'.

    Set the "where clause" to ...

    WHERE id NOT IN (SELECT datetime FROM mytable)

    -- hugh
  3. hartcapl New Member

    Level: Community
    no luck

    hugh,

    thanks for responding to my post. the instructions were straightforward but i am having hours of trouble. i don't have much experience with database applications like this and am lost. is there any way of breaking this down into more elemental parts? can you treat me like more of a moron (it would be appropriate)?

    i don't even know where to begin... i created a table called 'mytable'. it seems that when one creates a table there is the automatic creation of a group and a form. i then created an element, a drop down, called 'datetime'. i started out with just three 'values', 1,2,3, with the respective labels Wed April 8 - 1:00pm, Wed April 8 - 1:20pm, Wed April 8 - 1:40pm. i was able to make this a primary key.

    i created a table called 'times' with a PK of 'id' and a date/time column called 'datetime'. since i have absolutely no idea what i am doing, i made the datetime element in 'times' the same as in 'mytable'.

    i then created an element called 'join' which is the database join for 'mytable' to 'times' and included the where statement you suggested.

    i then created a menu item and linked it to the form 'mytable'.

    i have been trying every permutation, but i am getting nowhere. any help would be greatly appreciated.

    regards,

    hart
  4. rob Administrator

    Level: Community
    This bit is ok - I would have done this first though ... see below for why

    so far so good......

    This is the bit where you have gone wrong.
    Instead of making a dropdown element, you want to make a database join element (for the purposes of my post Im presuming its called "selected_dates" . These elements take data from another table and display them in a drop down list.
    You settings would be something like:

    hidden: no
    render join as: dropdown
    join type: simple
    connection: site database
    table: times
    key: id
    label: datetime
    (leave the other more advanced settings as they are for now)

    Save the element

    This should get you to the point where you have a 'mytable' form with a dropdown list (generated by the database join element) with a list of all your possible dates.

    The next part is to get that list to only show dates that hve not yet been selected.

    Go back and edit the datetime database join element
    In the Joins where statement (sql) field type something like this:

    Code (text):

    where id NOT IN (SELECT selected_dates FROM mytable )
     
    This *should* filter out all the already selected dates from the drop down list. It might take a little playing with to get this query right, but in principle I think this is what Hugh meant

    Cheers
    Rob
  5. hartcapl New Member

    Level: Community
    still problems

    thanks very much for the response. i misunderstood the way hugh was trying to help, but i can now see what he was saying after the fact.

    i think i now understand the relationship between the tables and the way joins work. i was able to configure the tables as per your (rob's) instructions but when i created a menu link to the 'mytables' forms the dropdown menu has no information in it, even though it is supposed to be getting info from the 'times' table.

    now i am not sure if i have setup the 'datetime' element in the 'times' table correctly. i have set it up as a dropdown menu. i have tried assigning a specific value to each time and have tried having the label and value being the same with equal results.

    a couple of things i have noticed, and i am not sure if this is a function of this version still being in beta, or something else: i am getting different output in safari and firefox. in firefox i am getting a popup calendar (i assume this is time_date_element) in the form even though it is supposed to be hidden. also, i am having some weird caching problems. i have to manually delete the cache after making any edits in Fabrik, even though i have turned joomla's caching function off.

    in any case, thanks very much for your time and attention.
  6. cheesegrits Support Gopher

    Level: Community
    Are you running the 2.0b3 ZIP, or have you upgraded to latest SVN? Some of the issues you mention above sound like things we've fixed in SVN since the last ZIP was built.

    When you say "delete the cache" you mean in your browser? That certainly shouldn't be necessary. You may occasionally have to do a shift-refresh (in FF) to force reload of changed files, but that's typically only needed after an update from SVN, to force reloading of things like JavaScript files.

    In your 'times' table, your 'datetime' element just needs to be a simple text element ('field'). Then the idea is you add records to the table, one per timeslot. No need for a front end form, just 'view data' on the backend, and add your required timeslots, one at a time, in the order you want them to appear on the dropdown. Use whatever format for the text that makes sense, like "Feb 27th, 10am till 11pm" or whatever.

    As you've created the 'times' table using Fabrik, the primary key will be called fabrik_internal_id (with a label of 'id'). So on your main 'mytable', the database join element ('selected_dates') to 'times' table will use 'id' as the key, and 'datetime' as the label. The 'where clause' will then be ...

    WHERE fabrik_internal_id NOT IN (SELECT selected_dates FROM mytable)

    You may need to add "ORDER BY fabrik_internal_id ASC" the the above to get them to order sequentially rather than being sorted alphabetically by the label.

    -- hugh
    1 person likes this.

Share This Page