REST API - select_fields array throwing error

Hi,

I’m trying to use the REST API to get_entry_list of account names and IDs from SuiteCRM. I only need these two fields, so have specified them (from what I can see correctly in an array) in the JSON request as follows:


{
   "session":"xxx",
   "module_name":"Accounts",
   "select_fields":[
      "id",
      "name"
   ],
   "max_results":500,
   "deleted":0
}

The query seems to be accepted by SoapHelperWebServices->checkQuery, but as soon as it hits the database, there’s a database failure:

[FATAL] Error running count query for Account List:  Query Failed:  SELECT count(*) c FROM accounts  LEFT JOIN accounts_cstm ON accounts.id = accounts_cstm.id_c   LEFT JOIN  users jt0 ON accounts.modified_user_id=jt0.id AND jt0.deleted=0
 AND jt0.deleted=0  LEFT JOIN  users jt1 ON accounts.created_by=jt1.id AND jt1.deleted=0
 AND jt1.deleted=0  LEFT JOIN  users jt2 ON accounts.assigned_user_id=jt2.id AND jt2.deleted=0
 AND jt2.deleted=0  LEFT JOIN  accounts jt3 ON accounts.parent_id=jt3.id AND jt3.deleted=0
 AND jt3.deleted=0  LEFT JOIN  campaigns jt4 ON accounts.campaign_id=jt4.id AND jt4.deleted=0
 AND jt4.deleted=0 where (Array) AND accounts.deleted=0: MySQL error 1054: Unknown column 'Array' in 'where clause'

Anyone seen anything similar to this before or have any ideas on how to get around it other than just asking it to return all the fields (a pretty heavy response).

Thanks,
Sam

OK, so I’ve worked out that the request is only answered properly if all of the parameters are passed in the JSON (whether empty or not). So, the following worked for me:


{
   "session":"xxx",
   "module_name":"Accounts",
   "query":"",
   "order_by":"",
   "offset":0,
   "select_fields":[
      "name",
      "id"
   ],
   "link_name_to_fields_array":"[]",
   "max_results":50,
   "deleted":0,
   "favorites":0
}

Hope that helps anyone else who is having trouble getting the values they expect out of it!

1 Like

:woohoo:

Thanks Sam, it really helped me right now.
I am trying to get information from SuiteCRM using the REST API.

Would you know how to get the names and IDs of the modules that are related to a contact?
My problem is that I need to update contacts, but I want to add text inside a module connected to the contacts, like “History”, which appears below the contact fields.
All I want to know for now, is how to get the ID of the History module knowing the ID of the corresponding Contact.
As soon as I have that I know what to do…

thanks,
Admino

I was finally able to get this to work.
I had to make sure the “deleted”:“false” and “max_records”:“0” were included, but they had to be in front of the “selected_fields”:[“created_by_name”,“status”]. The quotes must also be double quotes single quotes do not work.

It appears that the API works but is very picky about the placement of each variable.

Here was my final URL. I have separated it out to make it more readable.

http://CRM/service/v4_1/rest.php?
method=get_entry_list&
input_type=JSON&
response_type=JSON&
rest_data={“session”:“ffffffff”,
“module_name”:“AOS_Invoices”,
“query”:“number>=7800”,
“deleted”:“false”,
“max_records”:“0”,
“selected_fields”:[“created_by_name”,“status”,“total_amt”,“billing_contact”]}

The fields do not need to be populated. If the field is misspelled or not existent this still works the bad column name is ignored.