A list what gets data from a view table

[Egocentralis]

Hello,

We have a list/form on our site with one ID field, one name field (werknemer) and five radio button fields. The list is connected to a DB TABLE called ( g4e_orakel_vorige_eindmaand) I have tried to apply filters on this list but nothing seemed to work.

Then, after looking in phpmy admin i discovered that this was a VIEW table. So, it gets the data from somewhere else. I looked every field in the list, all settings in the form, but I couldnt find how the VIEW table gets and returned the data.

Then I exported the table row to see if i could find a clue to create the desired filter. I saw a long query but still i dont know how or where i can change it. I copy the query overhere:

CREATE VIEW `g4e_orakel_vorige_eindmaand` AS select `p`.`id` AS `id`,concat(`p`.`achternaam`,', ',`p`.`roepnaam`,' ',`p`.`tussenvoegsel`) AS `medewerker`,ifnull((select `e`.`uren` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `uren_gesloten`,ifnull((select `e`.`km` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `km_gesloten`,ifnull((select `e`.`klant` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `klant_gesloten`,ifnull((select `e`.`overig` from `g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `overig_gesloten` from `g4e_orakel_personeel` `p` where (`p`.`status` = '1') ;

A few questions:

1. Where can i find this query in my list/form/group or element so i can change it?
2. How can i get the persid or userid in the list so i can create a filter? I do want to show only users/employees belonging to a company_id (bedrijf_id)
   
3. How can i create a view table via fabrikar? or... to be short can someone explain to me how to work with this?

Long story short... HELP.. because i do not know what to do with this list.. or how to edit it.

Thanks in advantage

 

[troester]

1. + 3. You can't. MySQL views are created/managed directly in your database, e.g. with phpMyAdmin. Somebody must have created this MySQL view.

2. persid and userid must be columns in your view and elements in your Fabrik form/list

In general: A MySQL view is readonly, so a Fabrik list pointing to a MySQL view should have record add/edit/delete access=nobody and "Alter field types"=no (in list "Advanced" tab).

 

[Egocentralis]

Oke, so, the only thing what is in the list is the full employee name... no user id or something like that. Then its not possible to create a filter if I understand you right?

The only thing I can do is trying to write a sql query like the one above to include the user id and the company id to make it possible to add a filter?

If so, then i have to put this on my wishlist... because thats abracadabra to me

 

[Nagtegaal]

tabelnamen klinken Nederlands ;-)

Indeed your list is based on a View, not on a Table. But on a list based on a View you should be able to create Filters as well. Just go to an element. You can find it in the Wiki: http://fabrikar.com/forums/index.php?wiki/elements/#filters

 

[hominid4]

To adjust the View within phpMyAdmin - within in the left column, click on your view table ("Your Database >> Views >> Your Table"), and then when your View table is showing in the right window, click on the 'Structure' tab at top and click the "Edit view" text link at the bottom of the table, you can update your 'Select' query there.

You'll then need to add any new columns to your Fabrik elements.

As Nagtegaal mentioned, you can still make Fabrik elements filters no matter that your list is based on a View.

 

[Egocentralis]

Ok, that will be a nice project to do, sooner or later i will start to understand queries, fabrikar, joomla.... its almost a real adventure... never worked with this before... but slowly i manage.

I will try to do this, and if i have questions i will post them here.

And yes the tabelnamen klinken nederlands.

 

[Egocentralis]

Ok, I do have two questions:

Filtering an element:

Is it correct that when filtering an element, people have to give a keyword on the frontend and then the element gets filtered? Because what I need is the list to be allready filtered because i want people not to see details of other companies... But i think, that people have to filter the list themselves?

When editing the view query via edit view in phpmyadmin. I think i can rewrite the query and add an extra column in the view. But, do I have to create an element first in the list with the column name, or do i have to create the element after changing the view table?

 

[lousyfool]

(1) Use a list pre-filter for this: http://fabrikar.com/forums/index.php?wiki/list-pre-filters/
There you can pre-filter by the users' company ID, so that on list load they see only those records.

(2) First edit the MySQL view and add a column. But Fabrik won't know about it, of course. So, next you'll need to add a Fabrik element with the column name to a group in that Fabrik list.

 

[Egocentralis]

Ok, i managed to get this like i want. I had some big issues with undefined algorythm, superuser, etc and how many of ( ) i had to use. but its working..

ifnull((select `e`.`bedrijf_id` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `bedrijf_id`

So, i pasted this line in the query...

select `p`.`id` AS `id`,concat(`p`.`achternaam`,', ',`p`.`roepnaam`,' ',`p`.`tussenvoegsel`) AS `medewerker`,ifnull((select `e`.`uren` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `uren_gesloten`,ifnull((select `e`.`km` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `km_gesloten`,ifnull((select `e`.`klant` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `klant_gesloten`,ifnull((select `e`.`bedrijf_id` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `bedrijf_id`,ifnull((select `e`.`overig` from `dbrqdg58wpf72x`.`g4e_orakel_eindmaand` `e` where ((`e`.`persid` = `p`.`persid`) and (`e`.`maand` = month((curdate() - interval 1 month))) and (`e`.`jaar` = year((curdate() - interval 1 month))))),'0') AS `overig_gesloten` from `dbrqdg58wpf72x`.`g4e_orakel_personeel` `p` where (`p`.`status` = '1')

 

[Nagtegaal]

good job, now add the element to your list with the name 'bedrijf_id'

 

[Egocentralis]

yes i did that.. and it works good...

now i have another question about a mail thing.. do i need to make another topic since it is on another place off the website??

 

[lousyfool]

now i have another question about a mail thing.. do i need to make another topic since it is on another place off the website??

Yes please.