Importing participants - several issues - import bug?

[petednz]

I am trying to set up a simple offline system so that people who have signed up for an event, which is a course with several weeks, can have their attendance recorded.

The Custom Data Field is a Participant checkbox field with one box for each week. A test export of this shows it comes as expected as comma separated cell eg "1, 2, 3, 5, 6, 7, 8, 9, 11"

Process at this point will be a spreadsheet of all Participants with a column for each week. If someone attends Week 1, then a 1 is put in that column.

These columns are Concatenated so that each week we can import and not overwrite existing data in the Custom Data checkbox field

ie on week 3 a participant data to import may be - 1,2,3 - and in week 5 (if they skip week 4) would be 1,2,3,,5 or 1, 2, etc if a space is required.

First problems:

On the import I expected I would just need to use the Participant ID (which will be exported at outset for spreadsheet)

When I check what happens on 'update' the help tells me "If you are UPDATING existing records (to update participant status after an event, for example), you will need to include a column containing the unique CiviCRM-assigned participant ID to match to the existing record."

When I attempt the import, first i get the error at step 3 saying "Please correct the following errors in the form fields below:

    * Missing required field: Provide Event ID or Event Title

So I add this (though I am not sure why I should need to).

The import then throws up this error "CiviCRM has detected invalid data or formatting errors"

And when I download the errors I am told "Missing required field(s) :Participant Status"

Again, I don't understand why this field is required.

I thought a Participant ID was unique and would be the only field required to make the match.

Can someone pls clarify and/or tell me where my assumptions are wrong. thanks

Now on to the Data. When I ran the import, it has added data to the field in the database, but this is not showing in the checkbox. The dbase shows (see image below) - is this anything to do with the value/label issue I reported here http://forum.civicrm.org/index.php/topic,7751.0.html

tested and confirmed problem on demo - see Participant ID 57, 63 and 64 for Field = checkbox pd part

There is no problem import "1, 2, 3" and updating records when I do this for a Contact see http://drupal.demo.civicrm.org/civicrm/contact/view?reset=1&cid=103

[Dave Greenberg]

Peter - regarding the actual data - it appears that the expected value separator character is not being included when the value "list" is inserted during import. You should see the same CTRL+A "boxes" between each number as you have for the other rows. This is a bug in the import. Please file an issue - hopefully we can fix for 2.2.8.

Regarding the import requirements - it looks like the import is currently requiring the same fields for update as it does for create (PLUS the participant ID). This seems unnecessary - but since you can work around it - I would say this is an issue that can wait for a future release (unless you can submit a patch to fix it).

[petednz]

Thanks Dave - have posted here http://issues.civicrm.org/jira/browse/CRM-4815

As mentioned in ticket - still unclear to me if the actual problem is that it is trying to import against 'value' rather than against 'label' as it is 'label' that gets exported, and 'label' that is used to import for custom data in core isn't it?

Might that be an easier fix?

And agree the other items are a minor nuisance so will just workaround them.

[Dave Greenberg]

Peter - Not sure whether you should be including value or label in import data - but I AM SURE that the DB should store the VALUE. AND it's supposed to put the special separator characters between each value for multiple value fields (like checkboxes) - NOT COMMAS. If you're importing labels, and you're seeing values in the DB - that seems like good / expected behavior.

I think in some places we've tried to support either value or label on the import side ??

Please append a small (desensitized) sample input file to your issue with instructions on how to recreate on demo.

[petednz]

Profile = Colour
Fields
Label > Value
green > gn
red > red
yellow > yw

Participants = Surname is Adams (4 of them)
Event = Fall Fundraiser

Attached (if I can) is a cvs that is an export of above, plus a column called IMPORT which I think gives enough variation of value/label to give you what you need.

[Kurund Jalmi]

Peter:

Fixed this on htpp://drupal.demo.civicrm.org. Can you check and let us know.

Kurund

[petednz]

Thanks K. will try and get it checked this evening - would be great to have this route rather than just Batch Update for such data

[petednz]

Looks good K. Thanks