Importing Pledges

[sibro]

We do an annual billing to member clubs.  Each club has different numbers of members and different issues so each amount is unique. I created a file and imported it into civicrm_pledge. I believe i  did the import last year without issue. This time I am being hit with Foreign key issues.

It looks like existing records have NULL for both contribution_page_id and honor_contact_id. I forced a 1 into contribution_page_id and then hit the same problem on honor_contact_id.  The Foreign keys seem to allow null but it is not accepting a blank

I am on civi 4.4.5 and Drupal 7

ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails (`fjmcprod_crm`.`civicrm_pledge`, CONSTRAINT `FK_civicrm_pledge_honor_contact_id` FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)
SQL Statement:
INSERT INTO `fjmcprod_crm`.`civicrm_pledge` (`id`, `contact_id`, `financial_type_id`, `contribution_page_id`, `amount`, `original_installment_amount`, `currency`, `frequency_unit`, `frequency_interval`, `frequency_day`, `installments`, `start_date`, `create_date`, `acknowledge_date`, `modified_date`, `cancel_date`, `end_date`, `honor_contact_id`, `honor_type_id`, `max_reminders`, `initial_reminder_day`, `additional_reminder_day`, `status_id`, `is_test`, `campaign_id`) VALUES ('1945', '32', '5', '1', '550.00', '550.00', 'USD', 'year', '1', '1', '1', '14/09/01', '14/09/01', '', '', '', '', '', '', '1', '5', '5', '2', '0', '')

[Michael McAndrew]

This looks quite like a bug to me.  Are you able to reproduce this on our demo servers to make sure?

If it is a bug, best thing to do would be to file and issue so it can be fixed in an upcoming version.  And if you have resources to fix the bug as well, that would be appreciated (of course) and will help speed up the resolution for your site.

http://civicrm.org/bug-reporting for more info on bug reporting.

Michael

[Eileen]

The API importer may be able to help you get around this problem (no guarantees but the mechanism is a little different although it looks pretty similar so it may not be affected)

https://civicrm.org/extensions/api-csv-import-gui

[Michael McAndrew]

hopefully eileen's workaround will work.

bug report would be appreciated in either case

[sibro]

I was able to resolve my problem. The workbench import function was adding quotes around all constants. Importing 'NULL' is not the same as NULL.  My workaround was to create a full SQL  statement on each line and listing the data in the value clause. Exporting form the Excel file so the statement exports as a csv file got the job done. I wish I remembered about Eileen's API importer, would of made this job much easier.