getting trace back when browsing contributions for a given record

Is this the same as #3134 (closed) (https://github.com/civicrm/civicrm-core/pull/23027) ?

Thanks @DaveD for the tip. That's definitely the same family of issue.

But, the error I'm getting is thrown in the foreach loop below that one.

I think #3134 (closed) is about a price field id being defined, but the code not selecting the right value. Whereas my struggle is with a line item in which the price field id itself is undefined.

I tried a similar approach - skipping the call to setPriceSetIDFromSelectedField() if there is no price field ID - but then I just get another error complaining that getPriceSetID() is returning NULL instead of an integer.

That was my clue that perhaps getPriceSetID() has to be set on every line item and the problem is with our data.

Another thought - maybe if price field id is not defined we should default to the default price field id for the given type of line item?

I've added a possible PR to fix the problem here: https://github.com/civicrm/civicrm-core/pull/23087

But I'm really not sure if we should fix the code or fix the data?

Eileen reported on the proposed PR that line items should always have a price_field_id set so I'm closing this one and will fix our data.

closed

Just for completeness I'm adding this note here as well:

I tracked the problem down to groups receiving recurring contributions via a webform who did not have the transaction legacy extension installed, which feels like a very solid explanation for the problem.

@jamie how did you end up fixing the data? Just picking field id 1? update civicrm_line_item set price_field_id = 1 where price_field_id is null?

I'm seeing this in a few places too (transactlegacy is installed but I'm not sure when it was installed, and it's not just recurring).

just filling price_field_id not sufficent, it also required then financial_type_id and price_field_value_id

For immediate fix , i used temparary patch

diff --git a/CRM/Financial/BAO/Order.php b/CRM/Financial/BAO/Order.php
index 29cfc279..2f1d2b60 100644
--- a/CRM/Financial/BAO/Order.php
+++ b/CRM/Financial/BAO/Order.php
@@ -811,6 +811,16 @@ class CRM_Financial_BAO_Order {
       // Set the price set id if not set above. Note that the above
       // requires it for line retrieval but we want to fix that as it
       // should not be required at that point.
+      // PATCH: fill missing value
+      if (empty($lineItem['price_field_id'])) {
+        $lineItem['price_field_id'] = 1;
+        if (empty($lineItem['financial_type_id'])) {
+          $lineItem['financial_type_id'] = 1;
+        }
+        if (empty($lineItem['price_field_value_id'])) {
+          $lineItem['price_field_value_id'] = 1;
+        }
+      }
       $this->setPriceSetIDFromSelectedField($lineItem['price_field_id']);
       // Set any pre-calculation to zero as we will calculate.
       $lineItem['tax_amount'] = 0;

Hi @DaveD - This is tricky! I have found that the price field ids vary on different sites.

This query should help:

SELECT ps.id AS price_set_id, ps.name, pf.id AS price_field_id, pf.name, pf.label FROM civicrm_price_field pf JOIN civicrm_price_set ps ON pf.price_set_id = ps.id WHERE ps.name LIKE 'default%';

I get results like this:

+--------------+--------------------------------+----------------+---------------------+---------------------+
| price_set_id | name                           | price_field_id | name                | label               |
+--------------+--------------------------------+----------------+---------------------+---------------------+
|            6 | default_contribution_amount    |             14 | contribution_amount | Contribution Amount |
|            7 | default_membership_type_amount |             15 | 7                   | Membership Amount   |
+--------------+--------------------------------+----------------+---------------------+---------------------+

This would indicate that a line item with the entity_table value set to civicrm_contribution and no value for the price_field_id should have the price_field_id set to 14, where as the line items with entity_table set to civicrm_membership should have it set to 15.

Also, it seems that the function call to find a template contribution for a given recurring contribution is what triggers the bug, so I decided to fix only the line items attached to recurring contributions. That might set me up for problems in the future but I decided to be conservative on this first pass.

Thanks @jamie.

Just 2 cents, but if CiviCRM does not require a price_field_id to create a line item, then there shouldn't be errors in other places, when it is NULL.

We had also theorized that this could be from when a price field is deleted, except I couldn't find a way (in the UI) to delete a price field that had existing contributions. But regardless, if it shouldn't be null, then the ON DELETE should probably be RESTRICT:

show create table civicrm_line_item;

CONSTRAINT `FK_civicrm_line_item_price_field_id` FOREIGN KEY (`price_field_id`) REFERENCES `civicrm_price_field` (`id`) ON DELETE SET NULL,
CONSTRAINT `FK_civicrm_line_item_price_field_value_id` FOREIGN KEY (`price_field_value_id`) REFERENCES `civicrm_price_field_value` (`id`) ON DELETE SET NULL

While doing a larger rollout to fix the data ran into a situation where a contribution might have multiple line items, and one has null but the other has the default price field already, so when you try to update the null to set it to the default id, it tells you "DB Error: already exists".

And while I haven't seen any like this for entity_table=civicrm_participant, there is no built-in default price field for that so you'd have to figure out some other way to decide what price field id to put in there.

getting trace back when browsing contributions for a given record

Child items 0

Activity