search

progressivetech / net.ourpowerbase.sumfields

The Summary Fields extension creates read-only custom data fields that extend Contact and are automatically populated with up-to-date totals such as total lifetime contributions, last contribution amount, last attended event, etc.
Other
8 stars 29 forks source link

Fields Do Not Update #7

Open stevekessler opened 8 years ago

stevekessler commented 8 years ago

I have installed version 2.0.5 in CiviCRM 4.6.12. We have made sure the scheduled job but none of the fields have been updated. Can you please advise on what we might not have configured?

Please let me know if you need more information.

Thanks, Steve

jmcclelland commented 8 years ago

Hi Steve - before any fields can be updated, you have to configure the extension by going to Administer -> Customize fields and Data -> Summary fields. On that screen, choose the fields you want and how you want them configured. Then, save.

The scheduled job will only run if there is a new configuration saved.

Let me know if that does the trick.

stevekessler commented 8 years ago

We figured out what needed to be done with the fields. If we enable event fields we got "Finished execution of Call SumFields.Gendata API with result: Failure, Error message: DB Error: value count on row" when running the scheduled job. It is working great with the contribution fields which is what we need as of now.

Thanks!

jmcclelland commented 8 years ago

Hi - thanks for the update. Do you by chance have a full SQL statement that caused the errors with the Events? It might be in your ConfigAndLog directory? I'd like to track down what went wrong.

nganivet commented 8 years ago

Jamie - here is the trace for this error. Apparently the query at net.ourpowerbase.sumfields/sumfields.php(490) is incorrect: 1136 \ Column count doesn't match value count at row 1. This happens when it is called from net.ourpowerbase.sumfields/CRM/Sumfields/Form/SumFields.php(266): sumfields_gen_data((Array:0)) [NOTE the empty array argument].

3 /var/www/html/civi46sp-trunk/packages/DB.php(976): PEAR_Error->PEAR_Error("DB Error: value count on row", -22, 16, (Array:2), "INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...")

4 /var/www/html/civi46sp-trunk/packages/PEAR.php(564): DB_Error->DB_Error(-22, 16, (Array:2), "INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...")

5 /var/www/html/civi46sp-trunk/packages/DB/common.php(1905): PEAR->raiseError(NULL, -22, NULL, NULL, "INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...", "DB_Error", TRUE)

6 /var/www/html/civi46sp-trunk/packages/DB/mysql.php(895): DB_common->raiseError(-22, NULL, NULL, NULL, "1136 \ Column count doesn't match value count at row 1")

7 /var/www/html/civi46sp-trunk/packages/DB/mysql.php(328): DB_mysql->mysqlRaiseError()

8 /var/www/html/civi46sp-trunk/packages/DB/common.php(1216): DB_mysql->simpleQuery("INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...")

9 /var/www/html/civi46sp-trunk/packages/DB/DataObject.php(2442): DB_common->query("INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...")

10 /var/www/html/civi46sp-trunk/packages/DB/DataObject.php(1634): DB_DataObject->_query("INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...")

11 /var/www/html/civi46sp-trunk/CRM/Core/DAO.php(321): DB_DataObject->query("INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...")

12 /var/www/html/civi46sp-trunk/CRM/Core/DAO.php(1180): CRM_Core_DAO->query("INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...", TRUE)

13 /home/demo/public_html/civicrm-extend/net.ourpowerbase.sumfields/sumfields.php(490): CRM_Core_DAO::executeQuery("INSERT INTO civicrm_temp_f460a1b031f85d06f47757b4a7028c1d SELECT contact_id...")

14 /home/demo/public_html/civicrm-extend/net.ourpowerbase.sumfields/sumfields.php(1274): sumfields_generate_data_based_on_current_data()

15 /home/demo/public_html/civicrm-extend/net.ourpowerbase.sumfields/CRM/Sumfields/Form/SumFields.php(266): sumfields_gen_data((Array:0))

16 /var/www/html/civi46sp-trunk/CRM/Core/Form.php(345): CRM_Sumfields_Form_SumFields->postProcess()

jmcclelland commented 8 years ago

Thanks for posting - I really need the full SQL statement that fails though. Any chance that's in your ConfigAndLog folder? The sumfields_gen_data should get an empty array (it's passed in by reference).

I'm beginning to suspect that the problem may be that there are missing event settings (like what event types should be included, etc.) but can't tell for sure.

nganivet commented 8 years ago

There you are: [to_string] => [db_error: message="DB Error: value count on row" code=-22 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_temp_f02862bf38982b9794f768f2872e3118 SELECT contact_id, (SELECT civicrm_event.title AS summary_value FROM civicrm_participant t1 JOIN civicrm_event ON t1.event_id = civicrm_event.id WHERE t1.contact_id = t2.contact_id AND t1.status_id IN (2) AND civicrm_event.event_type_id IN (1,2,3,4,5,6) ORDER BY start_date DESC LIMIT 1), (SELECT e.start_date AS summary_value FROM civicrm_participant t1 JOIN civicrm_event e ON t1.event_id = e.id WHERE t1.contact_id = t2.contact_id AND t1.status_id IN (2) AND e.event_type_id IN (1,2,3,4,5,6) ORDER BY start_date DESC LIMIT 1), (SELECT COUNT(id) AS summary_value FROM civicrm_participant WHERE contact_id = t2.contact_id), (SELECT COUNT(e.id) AS summary_value FROM civicrm_participant t1 JOIN civicrm_event e ON t1.event_id = e.id WHERE t1.contact_id = t2.contact_id AND t1.status_id IN (2) AND e.event_type_id IN (1,2,3,4,5,6)), (SELECT FORMAT(IFNULL((SELECT COUNT(e.id) AS summary_value FROM civicrm_participant t1 JOIN civicrm_event e ON t1.event_id = e.id WHERE t1.contact_id = t2.contact_id AND t1.status_id IN (2) AND e.event_type_id IN (1,2,3,4,5,6)), 0) / IFNULL((SELECT COUNT(id) AS summary_value FROM civicrm_participant WHERE contact_id = t2.contact_id), 0), 2) * 100 AS summary_value), (SELECT COUNT(e.id) AS summary_value FROM civicrm_participant t1 JOIN civicrm_event e ON t1.event_id = e.id WHERE t1.contact_id = t2.contact_id AND t1.status_id IN (3) AND e.event_type_id IN (1,2,3,4,5,6)), (SELECT FORMAT(IFNULL((SELECT COUNT(e.id) AS summary_value FROM civicrm_participant t1 JOIN civicrm_event e ON t1.event_id = e.id WHERE t1.contact_id = t2.contact_id AND t1.status_id IN (3) AND e.event_type_id IN (1,2,3,4,5,6)), 0) / IFNULL((SELECT COUNT(id) AS summary_value FROM civicrm_participant WHERE contact_id = t2.contact_id), 0), 2) * 100 AS summary_value) FROM civicrm_participant AS t2 JOIN civicrm_contact AS c ON t2.contact_id = c.id GROUP BY contact_id [nativecode=1136 \ Column count doesn't match value count at row 1]"] )