Egocentralis
Member
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:
Thanks in advantage
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:
- Where can i find this query in my list/form/group or element so i can change it?
- 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)
- How can i create a view table via fabrikar? or... to be short can someone explain to me how to work with this?
Thanks in advantage