Improve lookup behavior #16
Description
Sometimes, we use CiviCRM Data Integration in chaines to perform complex queries. For instance, we can use an Input step to create a query returning the latest contributions and, then, use another step to obtain information about the contacts that made these contributions.
Version 1.2 will correct an issue related with Input chaines (#15) but, in order to empower the lookup function, we'll redefine a few use cases.
Note: Actually, v1.2 won't have no longer two steps. Instead, it will offer one multi-purpose step, that will allows us to perform every kind of action over every CiviCRM entity. We will keep talking about Input steps but the reader must remember that, from now on, when we say Input step, we are talking about this new multi-purpose step.
One row in, one row out
In this use case, one record arrives to the Input step. As usual, we can create filters in function of the data in this record. If this filter returns one record, the Input step will return one only record with both the initial information that arrived to the step and the information returned by the query.
Example:
- One Generate Rows step generates one record with one column called search_contact_id with the value 3 assigned.
- One Input step will obtain the contact with contact_id equals to the value set in the Generate Rows step. Two things can happen: 1) the contact exists or 2) the contact doesn't exist.
If the contact exists, the Input step will return one record with, more or less, this looking:
- search_contact_id = 3
- first_name = Carlos
- last_name = Capote
If the contact doesn't exist, the Input step will return one record with, more or less, this looking:
- search_contact_id = 3
- first_name = [null]
- last_name = [null]
One row in, zero rows out
As we've seen in the preceding example, by default, we will always obtain, at least, one output record for every input record. But, in order to make possible a different behavior:
- We'll add a Do not pass the row if the lookup fails check.
- By default, this check won't be checked.
- If it has been checked the case described before where the contact didn't exist in the database, wouldn't return any record.
One row in, multiple rows out
Sometimes, when the Input step performs a search, it will obtain more than one record. That may happen if, for instance:
- One Generate Rows step generates one record with one column called search_contact_id with the value 3 assigned.
- One Input step will obtain the activities of these contact.
If the contact didn't have any activity, or it had only one, the use case will correspond with the cases described before.
If the contact had more than one activity, then a new option will define what how the step will work:
- The option will be called On multiple records.
- It will offer three options: 1) Return the first one. 2) Return all of them. 3) Fail.
- By default, the option Return the first one will be selected.
The only case where we can obtain more than one record for one input record, is the one where we've chosen Return all of them.
Following the example where we received one row with one column called search_contact_id with the value 3, if it returned two activity records and we had chosen to return them all, the output could be something like:
First record:
- search_contact_id = 3
- activity_id = 1
- activity_subject = Email received
Second record:
- search_contact_id = 3
- activity_id = 2
- activity_subject = Email sent
One row in, throws error
The previous use case generates a new possible output. If the incoming row provokes more than one record to be returned by the Input step filter and we've chosen Fail for the On multiple records option, the step will generate an error.
Multiple rows in
When more than one records are arriving to the Input step, every single record will be treated independently according to the use cases described.