Inputscreen : select attribute filtered based on another select attribute

[charled]

Hello,

I still got my 3 MM : mm_Seasons, mm_Events and mm_Meetings. mm_Meetings is linked to mm_Events (many meetings for one event) and both mm_Meetings and mm_Events are linked to the same text attribute in mm_Seasons.

In mm_Meetings, I got 2 select attributes : linkToSeason (linked to mm_Season.Name) and linkToEvent (linked to mm_Event.Name). In my inputscreen, I just need linkToEvent list to be filtered based on witch season is first selected in linkToSeason. Something like "select the mm_Events records where mm_Events.linkToSeason = the actual mm_Meetings.linkToSeason.

I've created a filter in mm_Events and trying to write the right sql filter but the best result I got is a blank list (-) when not an error.

Thanks for your help.

[zonky]

I don´t have a "crystal ball"

post:

* Link to your site
* Screenshot from your settings
* the SQL-Query


may be, I can see more...

[charled]

Hi Zonky,

Here is the scheme of my mm_tables
Scheme for listing filtering in inputscreen.png

and my input screen
inputscreen.png

I give access to my test site by PM.

Thanks

[zonky]

this is "evil" - you can set meeting A to season 15/16 and Event xy to season 16/17 and Meeting A...

make a "linear relation" between season - meeting - events

I mean events have select to meeting and meeting have select to seasons

so you can make a clean sql query to select all events form season 15/16

[charled]

It can look like evil but it is not.

Because the user must be able to work on many seasons at a time : the actual one, which is published and can have new events and meetings and the next one(s) (not published). And the former seasons won't be deleted but used as archives. So, in some years, the events list in a meeting inputscreen will become very (very, very…) long if not filtered first by the season.

[zonky]

anyway...

o.k. I found your SQL-Filter-Rule and your frontend output - but I don´t understand your goal of filtering...

may be you will have a frontend filter with select your season and we can see all events of this season?

[zonky]

please activate your .htaccess and cxchange the settings to work without index.php at your URL

[zonky]

to respect at your own sql queries

MM "accept" after SELECT only * or id - the id is the ONLY ONE!! to add the select array and MM "move" this array to the next filter rule

write

SELECT *
or
SELECT id 

[charled]

In the main pages (Accueil (=Home) Événements, Invitations d'auteurs, Formations and Agenda) all events and meetings will be filtered on mm_saisons.enCours=1. (That's why I asked in another discussion how to limit for there could be only one record with enCours=1)

Former seasons will be displayed in Archives perhaps with a FE filter but that's not sure.

My goal is only for BE users experience. They create a season. Then they can create events linked to this season. Then they can create one or more meeting(s) for each event. In meeting's input screen, if the events list isn't filtered by season, all events of all seasons will be displayed.

[charled]

please activate your .htaccess and cxchange the settings to work without index.php at your URL

done

[zonky]

if yu wish a frontend filter, you dont use filter items with slash e.g. 2015/2016

just try the sql at your phpmyadmin, compete it and copy to the filter rule

SELECT * FROM mm_evenements WHERE 
lienSaisonEvenement IN (
SELECT id FROM mm_saisons 
WHERE enCours = 1) 

[zonky]

at http://XXXX/test/lnhv3/evenements.html

you can see the items with "Saison en cours" if you uncheck the checkbox - you see 0 item

[charled]

just try the sql at your phpmyadmin, compete it and copy to the filter rule

Ok. I've tried but but didn't success :-(

With this code :

SELECT id FROM mm_evenements WHERE mm_evenements.lienSaisonEvenement = 2

I got only the corresponding mm_evenements records

With this code :

SELECT id FROM mm_evenements, mm_rencontres WHERE mm_evenements.lienSaisonEvenement = mm_rencontres.lienSaisonRencontre

I got all the mm_evenements records.

So I guess the problem is to limit mm_rencontres.lienSaisonRencontres to THE ACTUAL record. but I don't fond how.

[zonky]

I implement the Filter from #11 at your filter rule - and works fine...

in your sql you should work with "IN (SELECT..." or "LEFT JOIN"

[charled]

I know it works but this is not what I'm looking for. Here is an example :

mm_saisons :
Saison 1 - id=1
Saison 2 - id=2

mm_evenements
Evenement 1 - id=1 - lienSaison=1
Evenement 2 - id=2 - lienSaison=1
Evenement 3 - id=3 - lienSaison=2

mm_rencontres
Rencontre 1 - id=1 - lienSaison=1 - in input screen, lienEvenement displays Evenement 1 and Evenement 2 (not 3 as lienSaison = Saison 1)
Rencontre 2 - id=2 - lienSaison=2 - in input screen , lienEvenement displays Evenement 3 (not 1 nor 2 as lienSaison = Saison 2)

In other words :
- mm_rencontres is linked to mm_saison and to mm_evenements
- mm_evenements is linked to mm_saisons too
- in mm_rencontres input screen, this could be possible, when a saison is selected, to only display the evenements linked to the same saison.

[zonky]

In other words :
- mm_rencontres is linked to mm_saison and to mm_evenements
- mm_evenements is linked to mm_saisons too
- in mm_rencontres input screen, this could be possible, when a saison is selected, to only display the evenements linked to the same saison.

one question: we talk about the backend (input) or frontend (output) ???

[charled]

Input screen so the BE.
When creating or editing a row in mm_rencontres, I need first to choose a saison (lienSaisonRencontre, select, linked to mm_saisons) then I can choose an événement (lienEvenementRencontre, select, linked to mm_evenements) between those linked to the same saison as I've juste selected in lienSaisonRencontre.

[zonky]

Input screen so the BE.
When creating or editing a row in mm_rencontres, I need first to choose a saison (lienSaisonRencontre, select, linked to mm_saisons) then I can choose an événement (lienEvenementRencontre, select, linked to mm_evenements) between those linked to the same saison as I've juste selected in lienSaisonRencontre.

O H H !!!!!!!!!

at the Backend! this is the other side of the coin ;-)

o.k. - I thinking...

[zonky]

o.k. - look at your "Rencontres" input screen and change the season...

[charled]

That's it but there is a little problem :
- when creating a new rencontre, one saison is displayed but not saved. So there's nothing to select in evenement. You have to select another saison for evenement's list to load then change again the saison if the first one was the good one ; I've checked "blank option" for the user must click on it and choose something.
- when editing a rencontre, the Saison is ok but the Evenement is empty.

[zonky]

o.k. - and now => you can see your saved attribute

... but if you create a item the select box iis not empty ... you can experiment with the filter...

[charled]

Thanks but now the evenements list is no more changing. Only the evenements of 2015|2016 are displayed.

[zonky]

try a little bit with the filter...

[charled]

Ok. I'll try. Where could I find informations on {{table}}, {{param::post?…]] and so on ?

[zonky]

klick at the small icon in the head of sql input panel