Problem with parent_type field

👨‍💻 Development Help
1

Hi,

I have a problem with parent_type field.
I’ve added in the list parent_type_display the module AOS_Product_Categories.
When I use the filter to discover how many calls have a Product Category included I have this error in the query search :

SELECT  calls.id , calls.parent_id , calls.assigned_user_id , calls.recurring_source , 
calls.status , calls.direction , calls.name  , LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,'')))) contact_name, 
jtl0.contact_id contact_id, calls.parent_type , calls.date_start  , jt1.user_name assigned_user_name ,
 jt1.created_by assigned_user_name_owner  , 'Users' assigned_user_name_mod, calls.date_entered , calls.created_by  
FROM calls   
LEFT JOIN  calls_contacts jtl0 ON calls.id=jtl0.call_id AND jtl0.deleted=0

 LEFT JOIN  contacts contacts ON contacts.id=jtl0.contact_id AND contacts.deleted=0
   AND contacts.deleted=0  
LEFT JOIN  users jt1 ON calls.assigned_user_id=jt1.id AND jt1.deleted=0
   AND jt1.deleted=0 where ((calls.parent_type like 'AOS_Product_Categories%' ) AND ( parent_AOS_Product_Categories_aos_product_categories.name like 'aaa%')) AND calls.deleted=0 
ORDER BY calls.direction ASC LIMIT 0,21

: MySQL error 1054: Unknown column 'parent_AOS_Product_Categories_aos_product_categories.name' in 'where clause'

It seems that suite is not able to add the join with this table.
Anyone had the same problem ?

I’m working with 7.11.12 version.

Thanks.

2

Hi

can you please share the exact “steps to reproduce” this error? I’d like to try it here

Thanks!

3

Hi,
thank you for your reply. I’ll try to be more comprehensive as possible.

I’ve created a call related to a record in Product-Categories module.

call_suite
call_suite1851Ă—948 66.1 KB

In the listview the record is visible and field related to is correctly populated.

So I’ve tried to filter this call using only the field related to.

But when I click on button search, the listview appears empty.

If you search in the suitecrm.log you’ll find the wrong query I wrote in the first comment. It seems that after the function generateSearchWhere in the file include/SearchForm/SearchForm2.php , suite doesn’t add the join for the table Aos_Product_Categories. If you try with other modules it works, but with this one no.

Maybe do you have any idea how to fix it?
Thanks.

4

This is because there is no Relationship between Product Categories module and Calls module (it doesn’t make much sense to me for this to exist, but ok…).

Adding it as a member in the list does not make the relationship appear, it merely adds a string in the Dropdown…

You can check Studio / Procuct Categories / Relationships.

5

It’s the same thing I thought.
But if you try to add a one-to-many or a many-to_many relationship in studio, starting from Product-Categories module it doesn’t work anyway.

6

I am not sure why that doesn’t work, but you have two choices:

  • forget about this and try to do things in a different way. This sounds wise to me, because I don’t think you should really be connecting phone Calls to Product Categories… that’s just not what these concepts are for.

  • work through it by going “under the hood” and changing PHP code until this works. I’m not sure how to do this, though.

Your choice!

7

Sorry if I insist.
Is not possible consider this problem a bug to resolve ?
We are talking about standard modules and generic functions that should be used in all modules without these type of problems.

If it’s not possible I’ll try to solve it in another way and when I found it I’ll post it.

8

It’s ok to insist, especially because you are right. Ideally this should be solved.

The only reason I didn’t suggest it (opening an Issue on GitHub etc) is because I fear this is too much of an edge case, and possibly quite hard to diagnose and fix, so I expect nobody would work on it… :frowning: