Unpivot Columns - Creating Record based on 1 column for each record

Topic Labels: Data
766 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

This is for exports from a donor database. I have two columns Credit & Debit. Credit is for the donation amount and Debit is for the CC processing fee amount. Debit is a calculated column based on a formula. Each "Donation" (Record) has both credit and debits but I would like to have a separate record for each of them.

I.e. 1 Record for John has his donation amount as well as the other misc. columns and 1 Record for his CC fee.

I only know the term in PowerBi of unpivoting columns to achieve this. Is there a way to do this in Airtable?

3 Replies 3
6 - Interface Innovator
6 - Interface Innovator

Hi @AntonioR 

This should be possible with the help of an automation script.

Have the script loop over all the records and create the additional record for the CC fee

i.e at the end of this you will end up 2 times the total records you have.

Hope this helps!

Any documentation on how to do this? Not sure how to set this up.

Try creating a new table and linking it to the data table  Then, create an automation that'll trigger when a new record gets created in the data table, and its action will be to create two new records and link it to the triggering record, and give the first record a "Type" value of "Credit", and the second record a "Type" value of "Debit"

In your new table, one lookup field each for the Credit and Debit fields, then create a formula field that'll display the appropriate field's value based on the "Type" value