List Manager Forum

Hi,

once you have set your events table field with an option type, and filled its possible values with the mysql select from the other table, you should always see all values from the category table at the time of inserting new records. Now, for filtering using that same mysql query, the setting that will decide if the values will be only the actual ones added to the records at the events table or all the values from the category table is the

display->autofilter

switch.

Please upgrade first to latest version 5.5.4 because it contains changes about this autofilter option for some configurations, and then try to enable/disable this setting to show/hide the different option sets.

About the date filter, you need to select the filter type 'range'. For fields typed date, the filter will display a couple a calendar fields to filter dates (from-to).

Hope this helps, regards
Silvia Martín
Moonsoft Team

Hi,
ok, please try this. Fill the values of the option with this query instead:

SELECT `category`,`category` FROM `vkctp_erlebniskultur_kategorie` WHERE `state` = 1

because the first field is used as the option value, the second one for that option text (same way you fill 2 values when added manually). With this change you should see the comboboxes with the proper texts in.

Now, if you have several categories for a single event, you will need to configure the field type as multiple option (list), with the same configuration as the standard option list, nothing to change there, but that way at the edition form a multiple select will be shown, the list will show several categories for the same record, and the filter will search if ANY of the values is the one you choose.

Last step would be to find out if the 'multiple values' you're inserting at the database follow the same pattern than the multiple option input. If you enter several values for the same record using the List Manager input form, and you choose both
cat1
and
cat2
for the category field, at the database you'll see stored at the column

cat1#cat2

in order to allow both the filters and the data view work properly. If you have any other content at the database it may need other different configuration.


Best regards
Silvia Martín
Moonsoft Team

Hi,
ok, can you please share at the private data of this ticket a temp access so we can check that filter configuration and output? Once you have the proper options at the combobox we'll be able to move forward and look for a solution for the different separator you're using.

Thanks, best regards
Silvia Martín
Moonsoft Team

Hi,
ok, thanks. Our fault, the query must get a couple of values, but if recovered with the same name, the result only will contain one single column, this is the proper query

SELECT `kategorie` as id, `kategorie` as kategorie FROM `vkctp_erlebniskultur_kategorie` WHERE `state` = 1

Now for the multiple values. The proper field type would be 'multiple option', but you will find problems filtering if the values are separated with commas. Do you have intention to manage data using the List Manager form and edition, or this would be a 'read only' list and you will load data using another external method? Because in that case, there is a quick solution using a view that we can discuss.

Regards
Silvia Martín
Moonsoft Team

Hi,
great it worked!
As List Manager also works with database views, one option would be to create a view from your events table, that would contain exactly same data than the source, but replacing the commas for # at that specific column, something like

create view mynewview as(
select col1, col2,REPLACE(kategorie, ',', '#') as kategorie, colx...... from eventstable
)

And configure the List Manager to publish this view instead. That way you could continue working and introducing data at your original source table, but you will have the data properly formatted for the multiple field of ListManager at the view. This would only work for 'read ony' tables as you will surely know, DB views doesn't allow to edit data.

Please let us know if you need further help

Regards
Silvia Martín
Moonsoft Team

Hi,
in fact it should work as you expect, because all filters are combined. We found an issue when doing the combination of the multiple values and options filled with external query. Please upgrade to latest version and then check the field Kategorie, you should set at
Fields configuration->Kategorie->Avanced->Search Type =Exact

so it can match the possible values with the ones at the database.

Regards
Silvia Martín
Moosoft Team

Hi,
don't worry, you're almost there and everything is well configured now. We do use the like expression, but the value you're searching for is not exactly present at the column, except for the first value, the rest have a trailing space that doesn't return results when compared with your 'kategory' values recovered from the table, the value you have stored is
Musik# Theater# Lesung

if you load your view with the exact values

Musik#Theater#Lesung

then you should have the expected result. You can just remove the empty spaces using the replace function when creating your view, something like

REPLACE(kategorie, ', ', '#') as kategorie //with a blank space after the comma
or

REPLACE(REPLACE(kategorie, ',', '#'),' ','') as kategorie
or

should do the work.

Hope this helps, regards
Silvia Martín
Moosoft Team

Hi,
perfect, thank you for the feedback!
We close this ticket now with this solution, we'll continue support with a new ticket if you need to.

Beest regards
Silvia Martín
Moonsoft Team