Correct workflow to create a join

SuiteCRM Forum - English Language Developer Collaboration
1

Hi, sorry for question,

I would like to ask you experts, what is the way forward when you want to create a join between two tables. it is correct to do this:

  • I create a new module
  • here I create a subpanel with the join to another table, this join must be like:
    select a.id, a.data, b.qty1, b.qty2 (select b.qty1 where type = 1) as Quantity 1, (select b.qty2 where type = 2) as Quantity 2 FROM activity a INNER JOIN work b ON a.id = b.id_activity

Now, does the result of this query go into a new table and then create a new form, just for this query?
Or is it enough to make the result appear on the screen?

According to SuiteCrm, how should we proceed?

Thanks

2

I know it’s a trivial question, but it’s important to me

3

Hi @fab

What is the goal you would like to accomplish?

Regards

4

Hi andopes,
I’d like to do this:
inside a module, I created a subpanel that goes in relation to another module.
now, I would like to make the information I need appear in this subpanel and taken from the join of both modules.
example:

Module A:
date id id_module_b

Module B:
id id_module_a type n_piece

what will appear in the subpanel (the select seen above):
date Quantity 1 Quantity 2

5

I am continuing to study to find a solution and would like to know:
when in the subpanel I create a relationship with another module, a new empty table is created. Now how do I fill it? I expected it to fill automatically

6

Hi,
Point number 1, how did you created relationship between these 2 tables? From Studio? Which type of relationship is that? One to One? One to Many or Many to Many?

Point number 2, in a subpanel, if we create it by any relationship from Studio, we can only include 1 (name) field from parent module to be shown into child module data in a subpanel/list view. To show other fields, there are different approaches.

If you have created any Custom subpanel, then this is simple, you can modify your query to achieve this. The above query is not correct. Check this out to get an idea.

select a.id, a.data, b.qty1, b.qty2,IF(b.type=1,"Quantity 1","Quantity 2") AS Quantity 
FROM activity a 
INNER JOIN work b ON 
a.id = b.id_activity
WHERE a.deleted=0 AND b.deleted=0 AND (b.type= 1 OR b.type=2)

Please first clear your requirements with screenshot and then we can say something.

7

Hi, I created a one-to-many relationship from Studio and edited the subpanels / default file
and this is the result:

works
works1106×460 7.62 KB

now I would like to know how to fill this relationship table

8

But is my question really so complicated? Nobody can answer me?

9

It looks like your database design is just wrong. This makes it difficult to understand your request, or to help.

Whenever you have fields called x1, x2, x3, x4, x5 (in your case: quantity), you’re not getting your data design correct.

If that’s a list of related values, then it should be an additional module, with a one-to-many relationship.

10

as per the initial question of the post, my question is what is the correct way to proceed in SuiteCRM? Keeping in mind that I have to show data, contained in tables, but manipulated at my pleasure (sum, etc.).
Method 1:
I create a module in which there is a subpanel that relates to another module which contains the data already manipulated. Example:
in Module A I create a Subpanel with relation to a Module B which contains data manipulated by Module A and Module C etc … (therefore Module B would be the one in the figure above)

Method 2:
I create a module that contains a subpanel that relates to another module and here I manipulate the data I need and make them appear as I want
Example:
in Module A I create a Subpanel that relates to a Module B and the result I make it appear directly in the Subpanel (without intermediate modules)

Which method is more correct? Method 1 or Method 2?

I really hope my question is clearer now, if it’s not please ask, it’s important