Integromat Search Module Error

Hello,

I am using integromat to import data. I got an issue with the search module of integromat for SuiteCRM. When I search for name or last name there are no issues, but when I search for something else like email, I get the following error in the log, which integromat says it is about the SuiteCRM and not on our end. I am kind of surprised, because if it was on SuiteCRM side, then why I don’t have the same issue with searching for names and last names.

Here is the log when using SuiteCRM Search Module in Integromat to search for email clientemail@hotmail.com, which is an existing contact.


Mon Dec 10 15:26:25 2018 [8557][1][FATAL] Mysqli_query failed.

Mon Dec 10 15:26:25 2018 [8557][1][FATAL] Error running count query for Contact List: Query Failed: SELECT count(*) c FROM contacts LEFT JOIN contacts_cstm ON contacts.id = contacts_cstm.id_c LEFT JOIN users jt0 ON contacts.modified_user_id=jt0.id AND jt0.deleted=0

AND jt0.deleted=0 LEFT JOIN users jt1 ON contacts.created_by=jt1.id AND jt1.deleted=0

AND jt1.deleted=0 LEFT JOIN users jt2 ON contacts.assigned_user_id=jt2.id AND jt2.deleted=0

AND jt2.deleted=0 LEFT JOIN contacts jt5 ON contacts.reports_to_id=jt5.id AND jt5.deleted=0

AND jt5.deleted=0 LEFT JOIN campaigns jt6 ON contacts.campaign_id=jt6.id AND jt6.deleted=0

AND jt6.deleted=0 where (contacts.email LIKE ‘%clientemail@hotmail.com%’) AND contacts.deleted=0: MySQL error 1054: Unknown column ‘contacts.email’ in 'where clause’


I don’t know what Integromat is, or how it works.

but I just tried searching by email in two different SuiteCRM instances and it worked fine.

You can try it yourself in the live demo:

https://demo.suitecrm.com

Which version of SuiteCRM are you running?

1 Like

Thank you for your reply. I use 7.10 version. I believe the latest is 7.11.

Is it alright if I run a test through the demo via integromat?

I understand that no one moves his/her finger without getting paid, and community support died long ago. It is clear from thousands of unanswered questions and unresolved issues on this forum. Is there a way to purchase 1 hour of Support? I can’t afford those expensive packages.

I’m disappointed to hear my 8552 non-paid posts mean little to you. In the past two years I read every single post on these Forums, and I’d say unanswered questions are at around 10% around here. So, my conclusion is: you’re probably having a bad day.

There is no way to purchase 1 hour of support from SalesAgility, but you can find help from other suppliers, many of them frequent these Forums, or you can search Upwork or similar.

About your issue, it would help if you explain what Integromat is, and how it connects to SuiteCRM. You can do whatever you want in the demo, if it let’s you. The demos are refreshed periodically, so if you break anything it will just get back to normal after a while.

If you need admin access you have this other demo:

https://www.softaculous.com/demos/SuiteCRM

1 Like

Hello,

Please do not be disappointed.

1- You have no responsibility to respond to my question. You are doing it voluntarily, and I appreciate that.
2- I am NOT disappointed for not receiving a reply or solution. I am disappointed with that I have to purchase a large package to get paid support.
3- I am not expecting free support, nor do I want someone give me a discount. I just want to be able to pay for the amount of support I need.
4- The statement that community support is dying, or already dead is valid (in my opinion) for too many open source product. Not only SuiteCRM. Interestingly, the reason is not people like you who do their best within their limited time. The reason is people like me who don’t often read the forums when their issues are resolved. However, I have an excuse. My excuse is that, the same way that I don’t always find the time to help, I also don’t expect that people help me for free at anytime. I am ready to pay my fair portion for support.
5- You are right, maybe my comment above was a bit too direct and too honest.
6- I appreciate your response, and THANK YOU (capitalized to express its sincerity not shouting).
7- Integromat is a good alternative to Zapier. It cost much less and allows much more than Zapier. I will test, and post my success or failure here.

I connected the Demo *ondemand. and it gives me the same error. When it searches for name and last name no issues. For anything else, it doesn’t return anything.

I don’t really know if this is an issue on SuiteCRM or Integromat. The way integromat connects to SuiteCMR is getting the URL, username and password. Do you think I need to add anything to the URL?

I will contact integromat and ask them for a copy of the exact request sent to SuiteCRM, and see if anything is wrong with it.

Cool B-)

Integromat is a nice discovery for me, I recently used Zapier with SuiteCRM for a project, but found the pricing really out of touch (at least for my use case).

If Integromat is using the REST API, like I think it is, and if it is using the v4 version (likely), then this might help:

https://suitecrm.com/suitecrm/forum/developer-help/10127-rest-api-select-fields-array-throwing-error

That guy had the same error you’re getting.

I gave the solution you provided many days ago. Still waiting for Integromat to fix the issue. Many days no any reply other than “we will contact you when there is an update.” Maybe they just have one developer and one customer service agent. From what I can see, maybe Integromat will be a history, as no one will wait this long to get a fix for something that advertised as working. At least they could provide with a timeline. This is ridiculous. So don’t get excited too early.

I received the following reply after many months from Integromat.

I hope you are doing well.

Our dev team has investigated a lot and unfortunately, it is a bug on SuiteCRM side and they don´t give sufficient information to solve the issue. 

We still send all parameters as is showed in the documentation you sent us. 

Here you can see the example of the request in which is the problem:
curl -X POST \
  https://%3Chost%3E/service/v4_1/rest.php \
  -H 'Content-Type: application/x-www-form-urlencoded' \
  -d 'method=get_entry_list&input_type=JSON&response_type=JSON&rest_data=%5B%22%3Csession_id%3E%22%2C%22Contacts%22%2C%22contacts.email1%20LIKE%20'\''%25email%40example.com%25'\''%22%2C%22email1%20ASC%22%2C0%2C%5B%5D%2C%5B%5D%2C5%2C0%5D'
Rest_data in detail: 
["<session_id>","Contacts","contacts.email1 LIKE '%email@example.com%'","email1 ASC",0,[],[],5,0]
This returns the error 500

Here you can see the same request with the question to the first name instead of email: 
curl -X POST \
  https://%3Chost%3E/service/v4_1/rest.php \
  -H 'Content-Type: application/x-www-form-urlencoded' \
  -d 'method=get_entry_list&input_type=JSON&response_type=JSON&rest_data=%5B%22%3Csession_id%3E%22%2C%22Contacts%22%2C%22contacts.first_name%20LIKE%20'\''%25name%25'\''%22%2C%22email1%20ASC%22%2C0%2C%5B%5D%2C%5B%5D%2C5%2C0%5D'
rest_data in detail:
["<session_id>","Contacts","contacts.first_name LIKE '%name%'","email1 ASC",0,[],[],5,0]
This returns no error and returns data you expect.

Can I kindly ask you to contact their support? Thank you in advance.

Best regards. 
Iveta
Integromat.com
1 Like

I have almost the same problem.
Have you solved this issue? How do you get in touch with Integromat suppor team?

I have exactly the same issue with Integromat. I am using Cognito Forms to capture Contact data. I then use Integromat to import the NEW entries into SuiteCRM quite happily, including a lot of custom fields. Works a dream. Cognito forms is a superb form creator and it is free if you only have limited numbers of forms and entries. The issue comes when I want to update an entry in the contact database using another form to update the information. The first thing you have to do in Integromat is to search for the correct entry. Since email address is my unique field I am using that to return the ID of the entry in SuiteCRM. I get the same issue. It tries to look for “contacts.email1” in the database. Which if you check the database does not exist. I am assuming that it is part of the join somewhere. But where and can the email address of a contact be referrenced by using contacts.email1? If we could solve this it would be great. I have not tried using first name and last name as a lookup but will try that over the next 24hrs to see if I can get that to work.