I'm trying to deal w/ script "2_person_groups.sql" and I'm having a devil of a time getting through parts of this one (and I'm sure it'll happen again later on too).
I don't understand why there's a line CONCAT("Facil import: tblPerson.PersonID=", PersonID) AS description, -- it seems to me that this pulls in a user ID number from a member and uses it as the group description. But that line makes sense in a subsequent script, so I think it might be an error due to copy and paste.
Also, the script doesn't work, at least for me at Boston.
It complains of "ERROR 1062 (23000): Duplicate entry 'Facil: 1 Access: Org' for key 'UI_title'" -- "1 Access: Org".
This makes me think it's wrong.
This is the script:
INSERT INTO civicrm_group(
name,
title,
description,
is_active,
visibility
)
SELECT DISTINCT
CONCAT("Facil_", REPLACE(PersonType, '-', '_')) AS name,
CONCAT("Facil: ", PersonType) AS title,
CONCAT("Facil import: tblPerson.PersonID=", PersonID) AS description,
true AS is_active,
"User and User Admin Only" AS visibility
FROM facil_tblPerson
ORDER BY PersonType
;So here is my suggested replacement for the script as a whole:
Since the PersonType field comes from facil_tlkpPersonType anyway, why not get the info from that directly?
So the script should read:
INSERT INTO civicrm_group(
name,
title,
description,
is_active,
visibility
)
SELECT DISTINCT
CONCAT("Facil_", REPLACE(PersonType, '-', '_')) AS name,
CONCAT("Facil: ", PersonType) AS title,
CONCAT("Facil import: tblkPersonType.PersonType=", PersonType) AS description,
true AS is_active,
"User and User Admin Only" AS visibility
FROM facil_tblkPersonType
ORDER BY PersonType
;although I think the ORDER BY and DISTINCT are not needed (but don't hurt).
I'm gonna try this anyway, but does anyone know if I'm on the right track here or just totally out of my league? Has any other Facil station run through all the scripts yet, successfully or otherwise?
Thanks,
-- Lane
Comments
typo on my part
The table I actually meant to use is called facil_tlkpPersonType.
Running the script with the correct table did not generate an error, so we'll see what happens subsequently.
-- Lane
Did your fix resolve the
Did your fix resolve the issue? I have ran the Civi scripts a couple times successfully, but that may be because they were wrote for PCM.
Ah. Yes. The scripts.
I will tell you that my understanding of MySQL and SQL has gone from only being able to spell it to being able to rewrite scripts that were written for PCM -- and having them WORK. :-) There has been much rejoicing in my work area lately. ESPECIALLY when I figure out foreign key constraints problems!!!
There is definitely a lot of PCM-specific stuff in there, but there's also the stuff that's left out which is important.
So, recalling what the hell I did here: Yes, my changes basically worked. BUT I ran into the COLLATE issue again on the second part of the script, so I was so f'ing happy (that's for you, Craig) to learn about the COLLATE command(?), which really made my day:
INSERT INTO civicrm_group_contact(group_id,
contact_id,
status
)
SELECT
cg.id AS group_id,
cc.id AS contact_id,
"Added" AS status
FROM
civicrm_contact cc
INNER JOIN facil_tblPerson fp ON cc.source=CONCAT("Facil import: tblPerson.PersonID=", fp.PersonID)
INNER JOIN civicrm_group cg ON cg.title=CONCAT("Facil: ", fp.PersonType)
COLLATE utf8_unicode_ci
WHERE
fp.PersonType IS NOT NULL
;
There have been other changes I needed to make, some big and some small. I'm hoping they're still enough in my head that I can document them a bit.
I highly recommend this book:
"MySQL" by Paul BuBois, published as part of the Developer's Library by Addison-Wesley, 4th Edition, copyright 2009.
At over 1100 pages, it looks pretty intimidating, but it's been really helpful.