I’m sorry, I didn’t understand what and how I should do it
-
Admin / System Settings / Log level change to DEBUG
-
Make the code run, by displaying the subpanel
-
Check your logs for that query, search for
" FROM activity ", for example -
Tell me what the entire query looks like.
Thanks
[DEBUG] Hook called: TEST2_ATTIVITA::before_retrieve
03/17/20 17:14:27 [8180][1][DEBUG] Retrieve TEST2_ATTIVITA : SELECT test2_attivita.* FROM test2_attivita WHERE test2_attivita.id = ‘100’ AND test2_attivita.deleted=0
03/17/20 17:14:27 [8180][1][DEBUG] Limit Query:SELECT test2_attivita.* FROM test2_attivita WHERE test2_attivita.id = ‘100’ AND test2_attivita.deleted=0 Start: 0 count: 1
03/17/20 17:14:27 [8180][1][INFO] Query:SELECT test2_attivita.* FROM test2_attivita WHERE test2_attivita.id = ‘100’ AND test2_attivita.deleted=0 LIMIT 0,1
03/17/20 17:14:27 [8180][1][INFO] Query Execution Time:0.00078392028808594
do not pay attention to the table name, the name is TEST
But I don’t think any of those queries matches this error here 
You don’t even have a COUNT, or a parenthesis…
The query is the one, without count … also, trying to launch the query again, ORDER BY TEST.ID (name of the module) is proposed, while in the query there is the name of the table (activity):
03/17/20 18:16:17 [8180][1][DEBUG] process_union_list_query: (Array) ORDER BY TEST.id asc
Sorry, I’m lost. I don’t understand what’s happening there.
I suggest that you try to get the example from that site working exactly as it is, just to confirm you get it right, and it works. Then you start changing it bit by bit until you get the effect you want.
actually I started from that, as it was, but it didn’t work, then I used what I posted to you at the beginning and it worked…
Sorry,
I would like to ask you, is it possible to use a complex query and make it appear in a subpanel? Of the type:
select a.id, a.date, a.type
when 1 then ‘first’
when 2 then ‘second’
end as type,
(select pg.n_pc where type = 1) as qty_first,
(select pg.n_pc where type = 2) as qty_second,
from pieces as pg, activity as a
where
(pg.id = 33) and a.id = pg.id
and how to make it appear in the subpanel with custom header:
Date First Second
Probably not. At least I don’t know how.
You could probably reduce most of that complexity to a simpler query, and then work on the formatting details and some simple conditions when handling the output:
You can also change the data displayed with that technique - not just the formatting.
Hi
- the query currently refers to the table of the created module, but instead, I would like to use another table in the DB, I tried to replace the name of another table in the SELECT, but it gives me an error
You can’t use another table which is not the module’s table in select query directly.
Table Name depends on subpaneldefs “module” parameter.
Your custom table must have a relation with the Main Module Table and you need to write a join query between them.
- I wish that at the end of each column there was a sum of the data above.
You can achieve this by doing some customization with Logic Hook and Jquery
I hope it will help you.
Thanks
1 Like
ah ok, I understand, thanks… the problem is that I need to import a table with its data, but if I create a form, this creates a table with pre-existing fields that I cannot delete (even the ID that cannot be empty), then I had thought of creating a form, but of using, in the query, a different table that I had imported into the DB.
So from what I understand, I can’t do it, I still have to use the form table
@fab you really should be trying to do things “the SuiteCRM way”. Try to see how the app models data (including related tables) and do things as they are expected. This will make things a lot easier for you. External tables and strange queries will complicate your work.
I don’t really want to do strange and complex queries, but I need to manipulate the data in the way described above and if you have any suggestions for getting this simple, I listen to you
Make Activities a Module, make Pieces a module, create the correct relationship between them, import data appropriately, etc.
That is the general idea, I know sometimes it is not evident the best way to design database., I am just trying to give you some tips. 
So I can’t work on tables that aren’t modules, right?
I just wanted a module that would then manipulate data from other tables.
As you say, I should create a module for each table, but then these will be visible as modules
1 Like
It’s not the only way to do it; you could do something more similar to what you were, trying, but you would have to get away from things based on definition files (vardefs, viewdefs, etc) and go for pure PHP, overriding view classes. In there you are free to do whatever.
But normally people who aren’t familiar with SuiteCRM programming find it harder to work from that sort of place.
Good luck!
Unfortunately I have to get that info, “go for pure PHP” is also ok, but I have to know how:
use the same files that SuiteCRM uses (vardef, viewdefs …), naturally customized? Or is it necessary to reconstruct a new path and structure for that module (hard work and I don’t even know how)?
I take this post for an update.
Let’s say that I reached the original goal, I put this solution of mine so that it could be useful to someone:
I created my module and used the procedure posted at the beginning, with subpaneldefs.php and customSubpanel.php, at this point I modified the default.php file:
$module_name = 'TEST';
$subpanel_layout = array(
'top_buttons' => array(
/* array('widget_class' => 'SubPanelTopCreateButton'),
array('widget_class' => 'SubPanelTopSelectButton', 'popup_module' => $module_name), */
),
'where' => '',
'list_fields' => array(
'date' => array(
'vname' => 'Date',
'widget_class' => 'SubPanelDetailViewLink',
'width' => '45%',
),
'n_piece_produced' => array(
'vname' => 'N pieces produced',
'name' => 'n_piece_produced',
'widget_class' => 'SubPanelNewField',
'custom_link_only' => true,
'displayHeaderCell' => false,
'width' => '10%',
),
'n_piece_discarded' => array(
'vname' => 'N pieces discarded',
'name' => 'n_piece_discarded',
'widget_class' => 'SubPanelNewField',
'custom_link_only' => true,
'displayHeaderCell' => false,
'width' => '10%',
),
);
and in the SubPanelNewField file there will be the query that returns the total I wanted.
Everything works as it should, the problem is that everything is very very slow. Apparently, for each row, the query is performed, also it is also performed for the number of fields present (in this case N pieces produced, N pieces discarded).
ideas to improve or should I change my approach?
Hi fab,
How many of your subpanels are using custom SQL?
I am experiencing the same MySQL error 1054: Unknown column error, but only when i have more than one subpanel using custom SQL. Adding a second panel always throws an error of an unknown column, even though that panel shows fine on its own.
Rearranging the panels will confirm the SQL must be correct (also confirmed with phpmyadmin), because it’s always the second panel that fails.
Hi Bungle,
at the end I changed the approach and followed the flow of SUITE, within the Module created in the studio, there are several subpanels, very often the problem of the manipulated fields is that they have a different name than the one stored in the DB, for example in the DB is stored “field_c”, while via code you look for “field” and from here comes the error “Unknown…”