1. Fabrik 3.7 is now available. This version contains critical security updates, please update as soon as possible. Please remember to backup your site before updating, we also recommend testing your updates on development sites if possible.
  2. We had to restore the site around midday (CST) on Wed 7/26 from a backup approximately 8 hours old. So a few posts in the forums were lost. You may want to check to see if you need to re-post anything.

Uploading into 2 tables

Discussion in 'Standard Support' started by paxitus, Jul 27, 2017.

  1. paxitus

    paxitus New Member

    Level: Standard
    Greetings,

    I have a spreadsheet with names and donations. In Fabrik I have a "Contacts" table and a related table "Donations" table. Is there any way I can upload the spreadsheet into the 2 tables matching the donation with the donor?

    Thanks!
    geoffrey
     
  2. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  3. paxitus

    paxitus New Member

    Level: Standard
    Ok, thanks.

    Can you think of any shortcusts other than just uploading the data into the contact table (most data) and then manually entering their donations in the donations table?

    Thanks
    geoffrey
     
  4. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  5. paxitus

    paxitus New Member

    Level: Standard
    Sorry about that, missed the first line of your response.

    There are 219 rows in the Donations table and 833 rows in the Contacts table
    Attached is a sample row in each.
     
  6. paxitus

    paxitus New Member

    Level: Standard
    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
     
  7. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  8. paxitus

    paxitus New Member

    Level: Standard
    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
     
    Last edited: Aug 11, 2017
  9. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  10. paxitus

    paxitus New Member

    Level: Standard
    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
     
  11. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  12. paxitus

    paxitus New Member

    Level: Standard
    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
     
  13. paxitus

    paxitus New Member

    Level: Standard
    Any advice on this? I am stumped.

    Thanks,
    geoffrey
     
  14. cheesegrits

    cheesegrits Support Gopher Staff Member

    Level: Community
    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
     
  15. paxitus

    paxitus New Member

    Level: Standard
    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
     

Share This Page