Español
English
Forum
List Manager Support
List Manager supportForums
No se permite escribir sin estar logado. Por favor, login
Filter a list by a user field 18.10.2013 15:44
When users register on my site they fill in several user fields, the most important is the Membership Number. I have a list of data that relates to each member and I want him to be able to add entries to the list, update existing entries and delete them but only for himself.
I have set up a form for editing the list and I have loaded the list using a csv file but the user has not added these records so I cannot use your 'user' option to filter the data so the member only sees his records and not other member records.
Can you suggest how I can filter the records by Membership Number which is a field in the user profile data base records.
I have set up a form for editing the list and I have loaded the list using a csv file but the user has not added these records so I cannot use your 'user' option to filter the data so the member only sees his records and not other member records.
Can you suggest how I can filter the records by Membership Number which is a field in the user profile data base records.
Re: Filter a list by a user field 18.10.2013 21:59
Hello,
ther's no way the list can filter for an external custom field. If you want to filter records by user, we'd suggest to use the user type, and load it manually from the backend, or introducing it at load time when you load csv file. You need to load each user Id for joomla user to match with each record.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
ther's no way the list can filter for an external custom field. If you want to filter records by user, we'd suggest to use the user type, and load it manually from the backend, or introducing it at load time when you load csv file. You need to load each user Id for joomla user to match with each record.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 19.10.2013 13:11
I have 4000 records and 600 users - the user id is selected by the user and I have no way of linking these to records. The member number which is an addition field in the profile is the link. When I produce a view can I include the member number in the selection somehow - perhaps SQL ? This is a good product but it needs some better ways of displaying views of the list data.
I can simulate what I require by creating a view and setting the member number field to a specific member number - this then lists that member records. If I could reference the user field in the user profile from the filter in the view this would provide me with this ability. Is it only numeric or character data that can be used in the filter value?
Edited by moon_1284 - 19.10.2013 13:46
I can simulate what I require by creating a view and setting the member number field to a specific member number - this then lists that member records. If I could reference the user field in the user profile from the filter in the view this would provide me with this ability. Is it only numeric or character data that can be used in the filter value?
Edited by moon_1284 - 19.10.2013 13:46
Re: Filter a list by a user field 21.10.2013 11:28
Hi,
when creating a view you can type any value needed to filter the column, numbers or texts, it doesn't allow a sql query inside the filter to search for another data at your database.
The ability of executing a sql query when filtering the view could be included, but even with this additional function, we think it won't work for your specific case, you need to filter the current column with a field (custom) that you have added to another table of the database filtered again using the proper ID, which is the user logged. This can't be built using a query, you also need some coding (to search for the current user logged ID and then search the profile table with it at the query).
There is another option to show filtered tables using url filters, if you build a url like this:
joomla/pageofyourlist?lmhid=19&lmhval=b
being lmhid the identifier (at LM) of the column you want to filter and lmhval the value to filter with, the list will show already filtered on that condition. You will still need some code to generate automatically the right value for each user.
We are going to think about a new feature, adding the ability to type php code in order to build freely the filter at view configuration, maybe it can be included for next version.
For your case coding is needed at some point. Will you be able to write php code to recover the value from db in order to build the filter with it? Then we could point you the file where that code should be placed in order to add a custom filter to your view.
If you're interested in the tweak, please place a ticket at helpdesk so we could exchange code if needed.
Thank you, best regards
Moonsoft Team
www.moonsoft.es
when creating a view you can type any value needed to filter the column, numbers or texts, it doesn't allow a sql query inside the filter to search for another data at your database.
The ability of executing a sql query when filtering the view could be included, but even with this additional function, we think it won't work for your specific case, you need to filter the current column with a field (custom) that you have added to another table of the database filtered again using the proper ID, which is the user logged. This can't be built using a query, you also need some coding (to search for the current user logged ID and then search the profile table with it at the query).
There is another option to show filtered tables using url filters, if you build a url like this:
joomla/pageofyourlist?lmhid=19&lmhval=b
being lmhid the identifier (at LM) of the column you want to filter and lmhval the value to filter with, the list will show already filtered on that condition. You will still need some code to generate automatically the right value for each user.
We are going to think about a new feature, adding the ability to type php code in order to build freely the filter at view configuration, maybe it can be included for next version.
For your case coding is needed at some point. Will you be able to write php code to recover the value from db in order to build the filter with it? Then we could point you the file where that code should be placed in order to add a custom filter to your view.
If you're interested in the tweak, please place a ticket at helpdesk so we could exchange code if needed.
Thank you, best regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 23.10.2013 16:27
Hi,
ok, then the code should look like this:
backup the file and
edit joomla\components\com_listmanager\models\serverpages.php
you only have to replace XXX and YYY from the code below
XXX is the value you have recovered from your database, your user profile...
YYY is the ID of the column you want to apply the filter to. You can check it at fields configuration, the first field shows the column ID.
about line 772 you will find:
// View Filter
if($isView){
//INSERT YOUR CODE HERE
//get your user value and assign it to $valuecustom
$valuecustom=XXX;
$idfieldcustom=YYY;
//this means the filter is 'equal' to your value
$typecustom=0;
$applyFilters=true;
$query=$this->_buildQuerySelectRecordsApplyFilterView($id, $idfieldcustom, $valuecustom, $typecustom);
$db->setQuery($query);
$arrIntFilter=$db->loadColumn();
$viewfilters=$arrIntFilter;
$filters[]=$arrIntFilter;
$allfilters=array_merge($allfilters,$arrIntFilter);
unset($arrIntFilter);
//END OF TWEAK
foreach ($fieldsView......
This should do the work, please note you will have to use a view to publish your list in order to get this code executed.
Hope this helps,regards
Moonsoft Team
www.moonsoft.es
ok, then the code should look like this:
backup the file and
edit joomla\components\com_listmanager\models\serverpages.php
you only have to replace XXX and YYY from the code below
XXX is the value you have recovered from your database, your user profile...
YYY is the ID of the column you want to apply the filter to. You can check it at fields configuration, the first field shows the column ID.
about line 772 you will find:
// View Filter
if($isView){
//INSERT YOUR CODE HERE
//get your user value and assign it to $valuecustom
$valuecustom=XXX;
$idfieldcustom=YYY;
//this means the filter is 'equal' to your value
$typecustom=0;
$applyFilters=true;
$query=$this->_buildQuerySelectRecordsApplyFilterView($id, $idfieldcustom, $valuecustom, $typecustom);
$db->setQuery($query);
$arrIntFilter=$db->loadColumn();
$viewfilters=$arrIntFilter;
$filters[]=$arrIntFilter;
$allfilters=array_merge($allfilters,$arrIntFilter);
unset($arrIntFilter);
//END OF TWEAK
foreach ($fieldsView......
This should do the work, please note you will have to use a view to publish your list in order to get this code executed.
Hope this helps,regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 26.10.2013 13:00
Hi Guys - I have tried to create the tweak - tested the sql and it worked in phpmyadmin - but syntax may be wrong in php code version.
Here it is
// Tweak inserted by Greg 26102013
$serverName = "db391884461.db.1and1.com";
$username = "dbo391884461";
$password = "My password edited out";
$database = "db391884461";
$connectionInfo = array( "UID"=>$username, "PWD"=>$password, "Database"=>$database);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$user =& JFactory::getUser();
$userId = $user->get( 'id' );
$key = "improved.memberno";
$value_custom = "SELECT profile_value FROM xnwi_user_profiles WHERE user_id = " & $userid & " AND profile_key = " & $key;
$idfield_custom = 2;
//this means the filter is 'equal' to your value
$typecustom=0;
$applyFilters=true;
$query=$this->_buildQuerySelectRecordsApplyFilterView($id, $idfieldcustom, $valuecustom, $typecustom);
$db->setQuery($query);
$arrIntFilter=$db->loadColumn();
$viewfilters=$arrIntFilter;
$filters[]=$arrIntFilter;
$allfilters=array_merge($allfilters,$arrIntFilter);
unset($arrIntFilter);
//END OF TWEAK
When I access the view I have created I get no records and no errors. Getting errors also trying to create a form as the sinple generator does not insert fields - I get field label followe by #field label# rather than a text box for field entry.
Any help on these two problems appreciated...
Greg Ellis
Here it is
// Tweak inserted by Greg 26102013
$serverName = "db391884461.db.1and1.com";
$username = "dbo391884461";
$password = "My password edited out";
$database = "db391884461";
$connectionInfo = array( "UID"=>$username, "PWD"=>$password, "Database"=>$database);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
$user =& JFactory::getUser();
$userId = $user->get( 'id' );
$key = "improved.memberno";
$value_custom = "SELECT profile_value FROM xnwi_user_profiles WHERE user_id = " & $userid & " AND profile_key = " & $key;
$idfield_custom = 2;
//this means the filter is 'equal' to your value
$typecustom=0;
$applyFilters=true;
$query=$this->_buildQuerySelectRecordsApplyFilterView($id, $idfieldcustom, $valuecustom, $typecustom);
$db->setQuery($query);
$arrIntFilter=$db->loadColumn();
$viewfilters=$arrIntFilter;
$filters[]=$arrIntFilter;
$allfilters=array_merge($allfilters,$arrIntFilter);
unset($arrIntFilter);
//END OF TWEAK
When I access the view I have created I get no records and no errors. Getting errors also trying to create a form as the sinple generator does not insert fields - I get field label followe by #field label# rather than a text box for field entry.
Any help on these two problems appreciated...
Greg Ellis
Re: Filter a list by a user field 27.10.2013 09:19
Hi,
about your code, we can see you have set
$value_custom= query;
The value_custom must be the single value already recovered from database. You have to execute your query and recover the value, in order to assign it to $value_custom.
Some useful information about recovering values from db:
http://docs.joomla.org/Selecting_data_using_JDatabase
Regarding the form creation, we'd suggest to double check you're using the internal name of the field inside the ##---## and not the name, and that the internal name only contains [0-9][a-Z] characters, without spaces or symbols.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
about your code, we can see you have set
$value_custom= query;
The value_custom must be the single value already recovered from database. You have to execute your query and recover the value, in order to assign it to $value_custom.
Some useful information about recovering values from db:
http://docs.joomla.org/Selecting_data_using_JDatabase
Regarding the form creation, we'd suggest to double check you're using the internal name of the field inside the ##---## and not the name, and that the internal name only contains [0-9][a-Z] characters, without spaces or symbols.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 27.10.2013 14:03
Thanks for the excellent support - pointing me in the correct direction. I have now modified the code to incorporate JDatabase. If I test the new php module without the view - where I should get the full list I get only the list header telling me I have 403 pages which is correct but no data. I can scroll through but no content is visible.
Here is the new code -
// Tweak inserted by Greg 27102013
$user = JFactory::getUser();
$userId = $user->get( 'id' );
$db = JFactory::getDbo();
$memkey = 'improved.memberno';
$conditions = array(
$db->quoteName('user_id') . ' = '. $userid,
$db->quoteName('profile_key') . ' = ' . $memkey
);
$query = $db->getQuery(true);
$query->select('profile_value');
$query->from($db->quoteName('#__user_profiles'));
$query->where($conditions);
$db->setQuery($query);
$result = $db->loadResult();
$value_custom = $result
$idfield_custom = 2;
//this means the filter is 'equal' to your value
$typecustom=0;
$applyFilters=true;
$query=$this->_buildQuerySelectRecordsApplyFilterView($id, $idfieldcustom, $valuecustom, $typecustom);
$db->setQuery($query);
$arrIntFilter=$db->loadColumn();
$viewfilters=$arrIntFilter;
$filters[]=$arrIntFilter;
$allfilters=array_merge($allfilters,$arrIntFilter);
unset($arrIntFilter);
//END OF TWEAK
I am a JDatabase novice so please bear with me. As I am only testing the full list why has my change caused an issue?
Also the problem with the fields has now dissappeared - might have been due to the old tweak causing problems.
Greg Ellis
Here is the new code -
// Tweak inserted by Greg 27102013
$user = JFactory::getUser();
$userId = $user->get( 'id' );
$db = JFactory::getDbo();
$memkey = 'improved.memberno';
$conditions = array(
$db->quoteName('user_id') . ' = '. $userid,
$db->quoteName('profile_key') . ' = ' . $memkey
);
$query = $db->getQuery(true);
$query->select('profile_value');
$query->from($db->quoteName('#__user_profiles'));
$query->where($conditions);
$db->setQuery($query);
$result = $db->loadResult();
$value_custom = $result
$idfield_custom = 2;
//this means the filter is 'equal' to your value
$typecustom=0;
$applyFilters=true;
$query=$this->_buildQuerySelectRecordsApplyFilterView($id, $idfieldcustom, $valuecustom, $typecustom);
$db->setQuery($query);
$arrIntFilter=$db->loadColumn();
$viewfilters=$arrIntFilter;
$filters[]=$arrIntFilter;
$allfilters=array_merge($allfilters,$arrIntFilter);
unset($arrIntFilter);
//END OF TWEAK
I am a JDatabase novice so please bear with me. As I am only testing the full list why has my change caused an issue?
Also the problem with the fields has now dissappeared - might have been due to the old tweak causing problems.
Greg Ellis
Re: Filter a list by a user field 28.10.2013 09:50
Hi,
we hace tested and we have it working here, as long as the $result value is properly assigned. The tweak only affects to the views, if the tweak is placed after these lines
//View filter
if($isView){
and to every full list as well if its placed before. About the value recovered, we can see you have missed the final ; at
$value_custom = $result
Replace with
$value_custom = $result;
if you can't get it work we suggest to check first how does it work with one fixed value, comment out your code above and set something like
$value_custom ="example";
if you get the expected results, then you can focus on your code above.
Hope this helps, seems you have this change almost ready, but please note we are also ready for custom development, in case you want us to code any additional feature, you can place a ticket at helpdesk and ask for a quote.
Thank you, regards
Moonsoft Team
www.moonsoft.es
we hace tested and we have it working here, as long as the $result value is properly assigned. The tweak only affects to the views, if the tweak is placed after these lines
//View filter
if($isView){
and to every full list as well if its placed before. About the value recovered, we can see you have missed the final ; at
$value_custom = $result
Replace with
$value_custom = $result;
if you can't get it work we suggest to check first how does it work with one fixed value, comment out your code above and set something like
$value_custom ="example";
if you get the expected results, then you can focus on your code above.
Hope this helps, seems you have this change almost ready, but please note we are also ready for custom development, in case you want us to code any additional feature, you can place a ticket at helpdesk and ask for a quote.
Thank you, regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 28.10.2013 11:40
Hi,
Still having some issues. I have put the code after the //View Filter and corrected the missing ; - and I can now see the full list with the modified php module. The form is also correct for this list with no #'s. I have a query about field id. Your product has put a field called Id before the fields I added to the list - thus the fields are id, memberno, from, to, area. If I want to filter on memberno what is the idfield_custom value - is it 0,1 or 2. I believe I still have a code problem as when I change the list to the view the headings have strange values from a previous list.
Still having some issues. I have put the code after the //View Filter and corrected the missing ; - and I can now see the full list with the modified php module. The form is also correct for this list with no #'s. I have a query about field id. Your product has put a field called Id before the fields I added to the list - thus the fields are id, memberno, from, to, area. If I want to filter on memberno what is the idfield_custom value - is it 0,1 or 2. I believe I still have a code problem as when I change the list to the view the headings have strange values from a previous list.
Re: Filter a list by a user field 28.10.2013 15:43
Hi,
the idfieldcustom value is the column id you can see for each field of the list if you go to fields, and select your column as if you were to configure it. You will see a first number ID. That is the value you must set at the code.
Please double check the code we sent you, we can see more typos there. for ex, we defined the function to work with:
$valuecustom=XXX;
variable, but you wrote instead
$value_custom
that will cause this code section break.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
the idfieldcustom value is the column id you can see for each field of the list if you go to fields, and select your column as if you were to configure it. You will see a first number ID. That is the value you must set at the code.
Please double check the code we sent you, we can see more typos there. for ex, we defined the function to work with:
$valuecustom=XXX;
variable, but you wrote instead
$value_custom
that will cause this code section break.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 02.11.2013 14:22
I have made progress and can now get the list I want by inserting the constant in the correct fields.
What I am trying to do is to access the current user information using JFactory::getUser and extract the 'id' field - this I have achieved. Next I want to use this id to access the user profile table with a profile key of 'profile.memberno'. I am having trouble constructing the SQL command in PHP.
I believe I want the command
$result = 'SELECT profile.value FROM #__user_profiles WHERE user_id= $uid AND profile_key LIKE 'profile.memberno'
My php is not good enough to achieve this.
Can you help me to code this - I may be able to get the society to pay a small fee though if I cannot do this I will not continue with your extension.
What I am trying to do is to access the current user information using JFactory::getUser and extract the 'id' field - this I have achieved. Next I want to use this id to access the user profile table with a profile key of 'profile.memberno'. I am having trouble constructing the SQL command in PHP.
I believe I want the command
$result = 'SELECT profile.value FROM #__user_profiles WHERE user_id= $uid AND profile_key LIKE 'profile.memberno'
My php is not good enough to achieve this.
Can you help me to code this - I may be able to get the society to pay a small fee though if I cannot do this I will not continue with your extension.
Re: Filter a list by a user field 04.11.2013 11:54
Hi,
yes, we can quote you the custom code, even it's not related to our extension, we are dedicated to custom development over third party or new extensions as well.
Your query seems ok, we'll write something like
$result = "SELECT ........profile_key = 'profile.memberno'";
But you will need not only the right sql code, but also the php accessing database and recovering value.
It's not a mayor change,in order to get a quote please place a ticket at helpdesk, and let us know exactly which is the database structure of the table you're trying to access, for your comments, we see your table its called:
xxx_user_profiles
but please confirm at the ticket all relevant column names.
Thanks, regards
Moonsoft Team
www.moonsoft.es
yes, we can quote you the custom code, even it's not related to our extension, we are dedicated to custom development over third party or new extensions as well.
Your query seems ok, we'll write something like
$result = "SELECT ........profile_key = 'profile.memberno'";
But you will need not only the right sql code, but also the php accessing database and recovering value.
It's not a mayor change,in order to get a quote please place a ticket at helpdesk, and let us know exactly which is the database structure of the table you're trying to access, for your comments, we see your table its called:
xxx_user_profiles
but please confirm at the ticket all relevant column names.
Thanks, regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 08.11.2013 12:11
I have now obtained the correct values to pass to the view.
Here is the code
$myuser = JFactory::getUser();
$myid = $myuser->get( 'id' );
$myname = $myuser->get( 'name' );
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query="SELECT profile_value FROM #__user_profiles WHERE user_id = $myid AND profile_key = 'improved.memberno'";
$db->setQuery($query);
$valuecustom = $db->loadResult(); // this now returns the member no
print_r($valuecustom); //this prints the member no
// $valuecustom = "4066";
$idfieldcustom = 22;
However the code returns an empty result even though when printed using the print_r function I get $valuecustom is equal to "4066". If I comment out the loadResult line and uncomment the $valuecustom = "4066" line I get a value returned for the member 4066. Is this a php problem I do not understand?
Here is the code
$myuser = JFactory::getUser();
$myid = $myuser->get( 'id' );
$myname = $myuser->get( 'name' );
$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query="SELECT profile_value FROM #__user_profiles WHERE user_id = $myid AND profile_key = 'improved.memberno'";
$db->setQuery($query);
$valuecustom = $db->loadResult(); // this now returns the member no
print_r($valuecustom); //this prints the member no
// $valuecustom = "4066";
$idfieldcustom = 22;
However the code returns an empty result even though when printed using the print_r function I get $valuecustom is equal to "4066". If I comment out the loadResult line and uncomment the $valuecustom = "4066" line I get a value returned for the member 4066. Is this a php problem I do not understand?
Re: Filter a list by a user field 08.11.2013 16:55
Hi,
we can't see anything wrong looking at your code. We'd suggest to print
":".$valuecustom.":"
To ensure there are not empty spaces returned "4610 " is not the same value as "4610" although they may seem equal when shown on screen. Also you can add
$valuecustom=$valuecustom."";
to force the value to be an string, in case it may be affecting the comparison afterwards.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
we can't see anything wrong looking at your code. We'd suggest to print
":".$valuecustom.":"
To ensure there are not empty spaces returned "4610 " is not the same value as "4610" although they may seem equal when shown on screen. Also you can add
$valuecustom=$valuecustom."";
to force the value to be an string, in case it may be affecting the comparison afterwards.
Hope this helps, regards
Moonsoft Team
www.moonsoft.es
Re: Filter a list by a user field 09.11.2013 11:52
I have found the problem but don't know how to solve it. When I trap the values parameter in the buildQuerySelectRecordsApplyFilterView function I get two seperate values for when I pass the constant "4066" and when I pass the $valuecustom variable.
The variable gives [string] values = ""4066"" which does not work and the constant gives [string] values = "4066" which does work.
Using print_r("*".$valuecustom."*") I get two values *"4066"* and *4066*.
I have now discovered that the SQL result contains the " marks as if I do a substring function from 2nd character to 5th I get correct string! I now have the code working but I still don't understand why the quotes are passed as part of the result.
Edited by moon_1284 - 09.11.2013 12:09
The variable gives [string] values = ""4066"" which does not work and the constant gives [string] values = "4066" which does work.
Using print_r("*".$valuecustom."*") I get two values *"4066"* and *4066*.
I have now discovered that the SQL result contains the " marks as if I do a substring function from 2nd character to 5th I get correct string! I now have the code working but I still don't understand why the quotes are passed as part of the result.
Edited by moon_1284 - 09.11.2013 12:09
Re: Filter a list by a user field 09.11.2013 12:28
Hi,
if your result contains the extra double quotes, they are stored that way at your database, maybe the value was typed with them, or the value was quoted a couple of times before being stored. Instead of making a substring, you can also try to use intval function at php, or str_replace to remove double quotes, both of them will ensure your code will work as well when a real number is returned.
Regards
Moonsoft Team
www.moonsoft.es
if your result contains the extra double quotes, they are stored that way at your database, maybe the value was typed with them, or the value was quoted a couple of times before being stored. Instead of making a substring, you can also try to use intval function at php, or str_replace to remove double quotes, both of them will ensure your code will work as well when a real number is returned.
Regards
Moonsoft Team
www.moonsoft.es