How to get the new record ID, when adding a record to a multi-value custom field using the API?

Question

The API calls I know of that add a record to a multi-value custom field, don't return the ID of the added record (which you can then use to access the value for the specific record in the form custom_XXX_YY). Is there another way of finding it?

Observed:

$ drush cvapi CustomValue.create entity_id=154621 custom_368=Test                                                                                                                                      
Array
(
    [is_error] => 0
    [version] => 3
    [count] => 1
    [values] => 1
)

$ drush cvapi Contact.setvalue id=154621 field=custom_368 value=test                                                                                                                                   
Array
(
    [is_error] => 0
    [version] => 3
    [count] => 2
    [values] => Array
        (
            [id] => 154621
            [custom_368] => test
        )

)

$ drush cvapi Contact.create custom_368= id=154621                                                                                                                                                     
Array
(
    [is_error] => 0
    [version] => 3
    [count] => 1
    [id] => 154621
    [values] => Array
        (
            [154621] => Array
                (
                    [id] => 154621
                    [contact_type] => Individual
                    ... snipped ...
                    [modified_date] => 2015-05-14 11:20:56
                )

        )

)

Background:

In our custom frontend I want to have an 'Add new qualification' link, which creates a record in the appropriate custom field group and then adds the set of fields to the form. The screen size occupied by a set of fields for one qualification is such that I don't want to always have a blank set of fields on the screen for adding a new record, before I've clicked the link. The record's row ID is necessary to add the form fields so they know which record they apply to. Each field is 'inline editable' / sends an update call immediately.

Answer 1

I don't think it's possible using the api right now.

So what you could do in the mean time is create a custom function to do it and ideally emulate the api call using the same input params and output so you can replace it with an api call when the api is fixed.

Not tested but should look like :

function insertCustomValue($params) {

  // prepare input params
  $dummy = explode('_', $params['field']);
  $cfId = $dummy[1];
  $cfValue = $params['value'];
  $entityId = $params['entity_id'];

  // get table name and column name
  $cf = civicrm_api3('CustomField', 'getsingle', array('id' => $cfId));
  $table_name = civicrm_api3('CustomGroup', 'getvalue', array(
    'id' => $cf['custom_group_id'], 
    'return' => 'table_name')
  );
  $column_name = $cf['column_name'];

  // insert the new value in the database
  $sql = "INSERT INTO $table_name (entity_id, $column_name) values (%1, %2)";
  $dao = CRM_Core_DAO::executeQuery($sql, array(
    1 => array($entityId, 'Integer'), 
    2 => array($cfValue, 'String')
  ));

  // get and return the id of the new row
  $cfId = CRM_Core_DAO::singleValueQuery('SELECT LAST_INSERT_ID()');
  return array(
    'is_error' => 0,
    'count' => 1,
    'values' => array(
      'id' => $cfId,
    )
  );

}

But of course you could also help fix the api which would be better :)

Answer 2

I have expanded upon samuelsov's answer and created a new API function resembling CustomValue.create. It's not as general as the real create/setvalue APIs but it looks like it solves my immediate problem.

https://gist.github.com/futurefirst/9a6b39ddfa34eb01a417

Output:

$ drush cvapi CustomValue.insert entity_id=154621 custom_368=thisisatesthello
Array
(
    [is_error] => 0
    [version] => 3
    [count] => 1
    [id] => 23
    [values] => Array
        (
            [23] => Array
                (
                    [id] => 23
                    [entity_id] => 154621
                    [custom_368] => thisisatesthello
                    [custom_368_23] => thisisatesthello
                )

        )

)