-
New Commercial Services Section
We have now opened a commercial services section here on the forum for registered users. If you have a Fabrik project that you wish to have someone work on for you, post it under Help Wanted. If you are an application developer and wish to earn some money helping others, post your details under Fabrik Application Developers.
Both of these are unmoderated. It will be up to both parties to work out the details and come to an agreement.
-
Joomla 5.1
For running J!5.1 you must https://fabrikar.com/forums/index.php?wiki/update-from-github/ or include the new file manually https://fabrikar.com/forums/index.php?threads/joomla-5-1-and-fabrik-cannot-find-files-error.54473/post-285151 See also Announcements
Uploading into 2 tables
- Thread starter paxitus
- Start date
cheesegrits
Support Gopher
Can you paste an example of the data from both spreadsheets (one row from each would be enough) along with the headers.
The answer is usually that this isn't easy, as spreadsheets don't have the relational capability of databases.
-- hugh
The answer is usually that this isn't easy, as spreadsheets don't have the relational capability of databases.
-- hugh
cheesegrits
Support Gopher
It depends, that's why I wanted to see some example data.
It may be possible to use the list CSV plugin, to create the join foreign key. So if you have a column in your donations spreadsheet for "Contact", which has a name which exactly matches a the name in the Contact sheet, and they are unique ... then you could import the conatacts, then import the donations, and have a few lines of code in a CSV plugin that looks up the 'id' (primary key value) for that named contact and inserts it into the donation row during import.
Really it depends how much data you have, as to whether it's worth the additional effort.
-- hugh
It may be possible to use the list CSV plugin, to create the join foreign key. So if you have a column in your donations spreadsheet for "Contact", which has a name which exactly matches a the name in the Contact sheet, and they are unique ... then you could import the conatacts, then import the donations, and have a few lines of code in a CSV plugin that looks up the 'id' (primary key value) for that named contact and inserts it into the donation row during import.
Really it depends how much data you have, as to whether it's worth the additional effort.
-- hugh
paxitus
New Member
Doesn't look like the uploads worked, here they are in CSV
Contacts
External ID,First Name,Last Name,E-Mail 1,E-Mail 2,Mail,Address,City,State,Zipcode,Country,Child Sponsor,Child's Name 2016,Child's Name 2015,Child's Name 2014,Recurring Donor,Board Member
Ind3,Paxitus,Jones,someemail@gmail.com,anotheremail@gmail.com,1,10 Main Street,Americus,GA,31709,united States,Yes,Johnny Garcia,,,Yes,No
Donations
external ID,First ,Last Name,Date,Amount,Payment Method,Financial Type
Ind3,Paxitus,Jones,24-Jan-17,50,Stripe,Student
Contacts
External ID,First Name,Last Name,E-Mail 1,E-Mail 2,Mail,Address,City,State,Zipcode,Country,Child Sponsor,Child's Name 2016,Child's Name 2015,Child's Name 2014,Recurring Donor,Board Member
Ind3,Paxitus,Jones,someemail@gmail.com,anotheremail@gmail.com,1,10 Main Street,Americus,GA,31709,united States,Yes,Johnny Garcia,,,Yes,No
Donations
external ID,First ,Last Name,Date,Amount,Payment Method,Financial Type
Ind3,Paxitus,Jones,24-Jan-17,50,Stripe,Student
cheesegrits
Support Gopher
OK, so the only way the donations spreadsheet "links" to the contacts spreadsheet is through "First" and "Last Name". So the only way to automate it would be for each donation row being imported, find the contact with matching first and last name. Which raises several issues:
Do you have any duplicate names? Like "John Smith"? Obviously that would cause problems with trying to automate the creating of the relational keys.
How accurate is the data? By which I mean, was it typed by hand and hence subject to typos and mis-spellings, or were the names programatically added to the donations, so we know they are accurate?
Have names in the contacts been changed since donations were made, ie. for marriages / divorces?
I was kind of hoping your donations table would include the email of the contact, which is a much better way of linking names, as emails are unique. Two people may be called John Smith, but they won't have the same email.
-- hugh
Do you have any duplicate names? Like "John Smith"? Obviously that would cause problems with trying to automate the creating of the relational keys.
How accurate is the data? By which I mean, was it typed by hand and hence subject to typos and mis-spellings, or were the names programatically added to the donations, so we know they are accurate?
Have names in the contacts been changed since donations were made, ie. for marriages / divorces?
I was kind of hoping your donations table would include the email of the contact, which is a much better way of linking names, as emails are unique. Two people may be called John Smith, but they won't have the same email.
-- hugh
paxitus
New Member
Sorry, finally getting back to this. I do appreciate your help!
What about the Extrernal ID field, could we use that? If not I can pull their email address using that field on a spreadsheet using vlookup. The "External ID was created when we were using CiviCRM, so each donation is tied to their contact information using that field.
Amended: Actually we do not have email addresses for everyone, some of their donors don't use email. So if we could use that External ID, that would be great! It is a unique ID in the contacts spreadsheet, and every donation has an associated External ID.
geoffrey
What about the Extrernal ID field, could we use that? If not I can pull their email address using that field on a spreadsheet using vlookup. The "External ID was created when we were using CiviCRM, so each donation is tied to their contact information using that field.
Amended: Actually we do not have email addresses for everyone, some of their donors don't use email. So if we could use that External ID, that would be great! It is a unique ID in the contacts spreadsheet, and every donation has an associated External ID.
geoffrey
Last edited:
cheesegrits
Support Gopher
Ah, if the "external ID" is actually a relational link, so the ID in the donation is a "foreign key" pointing to the ID in the contact, then yes ... we can use that.
Assuming the external ID is a unique numeric value, it might even be possible to assign that as the primary key in the contacts list. I haven't tried importing with primary key for a while, it may not still work, but worth a try. So create the list by doing a CSV import on the backend (button at top of Lists page), and designate that as the PK. I *think* we'll preserve those values, although you'd have to check and make sure we didn't re-write them as auto-incrementing values.
-- hugh
Assuming the external ID is a unique numeric value, it might even be possible to assign that as the primary key in the contacts list. I haven't tried importing with primary key for a while, it may not still work, but worth a try. So create the list by doing a CSV import on the backend (button at top of Lists page), and designate that as the PK. I *think* we'll preserve those values, although you'd have to check and make sure we didn't re-write them as auto-incrementing values.
-- hugh
paxitus
New Member
The thing is, we don't need or want this field after this import, they have been manually creating it using IND = Individual, FAM=Family etc, in the fabrick tables I created a field "type of membership" so we wont want to create those going forward. Would it be easier for me to change them now to a serialized 1,2,3 etc and then import that as the primary key?
Once we have this part worked out, I am assuming it is not that difficult, but I am still not clear on how to do a relational import like this to two tables. Is there a tutorial somewhere you can point me to?
Thanks,
geoffrey
Once we have this part worked out, I am assuming it is not that difficult, but I am still not clear on how to do a relational import like this to two tables. Is there a tutorial somewhere you can point me to?
Thanks,
geoffrey
cheesegrits
Support Gopher
OK, here's the trick ...
When you create the Contacts list, by importing the CSV on the backend, it'll ask you on the first page if you want to create a primary key field. Say No. Then on the second page, where it prompts you to assign a type to the field, leave that "External ID" as a field, and select "Primary key" on it.
Import, and you should get a list with that external ID set correctly, to the values from the CSV. Edit the list, in the Data tab, the external ID should be selected as the PK, but "Auto inc" will be turned off. Set it to "Yes" and save. Then edit the external ID element, and set the type to "internalid".
Then import the donations, but this time, tell it to create a PK field. Once import, edit the "external id" element, and make it a database join, pointing to the contacts table, with value set to "external id" and label to whatever you want.
-- hugh
When you create the Contacts list, by importing the CSV on the backend, it'll ask you on the first page if you want to create a primary key field. Say No. Then on the second page, where it prompts you to assign a type to the field, leave that "External ID" as a field, and select "Primary key" on it.
Import, and you should get a list with that external ID set correctly, to the values from the CSV. Edit the list, in the Data tab, the external ID should be selected as the PK, but "Auto inc" will be turned off. Set it to "Yes" and save. Then edit the external ID element, and set the type to "internalid".
Then import the donations, but this time, tell it to create a PK field. Once import, edit the "external id" element, and make it a database join, pointing to the contacts table, with value set to "external id" and label to whatever you want.
-- hugh
paxitus
New Member
I'm back, this has turned into a long project, but I am back trying to finish it up.
I have followed your instructions exactly 3 times, and in the end, what I end up with is the list of contacts, with an external_id of 1,2,3... etc and in the donations table there is no external id (blank) for each record.
At the point where you say " "Auto inc" will be turned off. Set it to "Yes" " the external ids change to 1,2,3 etc. and when I change the donations table external_id to databasejoin, that is when they go blank, so the records are not related.
Thanks,
geoffrey
I have followed your instructions exactly 3 times, and in the end, what I end up with is the list of contacts, with an external_id of 1,2,3... etc and in the donations table there is no external id (blank) for each record.
At the point where you say " "Auto inc" will be turned off. Set it to "Yes" " the external ids change to 1,2,3 etc. and when I change the donations table external_id to databasejoin, that is when they go blank, so the records are not related.
Thanks,
geoffrey
cheesegrits
Support Gopher
The only way I can help at this point would be to try and do this for you here, and send you the SQL for the created tables. And I'd have to charge for my time.
The method I described worked for me in a test, so I'm not quite sure why it's not working for you.
-- hugh
The method I described worked for me in a test, so I'm not quite sure why it's not working for you.
-- hugh
paxitus
New Member
Basically what happened is as soon as I changed the Auto inc to Yes, it overwrote the external ID to 1,2.3 etc. so I could no longer had the external IDs to work with.
However today I decided to import the contacts, then export them again. Using a vlookup in a spreadsheet I was able to add the created id from Fabrik to their donation record, then upload the donations and use a database join to tie them together.
I would love to hire you, but this is a small non-profit that I am volunteering for in El Salvador, so hopefully I can get what I need from my Fabrikar membership.
Thanks for all your help on this, I am sure you will see me again!
geoffrey
However today I decided to import the contacts, then export them again. Using a vlookup in a spreadsheet I was able to add the created id from Fabrik to their donation record, then upload the donations and use a database join to tie them together.
I would love to hire you, but this is a small non-profit that I am volunteering for in El Salvador, so hopefully I can get what I need from my Fabrikar membership.
Thanks for all your help on this, I am sure you will see me again!
geoffrey