Skip to main content

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

  • July 11, 2023
  • 3 replies
  • 86 views
A
Forum|alt.badge.img+1

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

    H
    Forum|alt.badge.img+4
    • Harsh2
    • Participating Frequently
    • July 11, 2023

    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!

    A
    Forum|alt.badge.img+1
    • AntonioR
    • Author
    • New Participant
    • July 11, 2023

    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.

    TheTimeSavingCo
    Forum|alt.badge.img+31

    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

    Adam, Airtable Consultant - thetimesaving.company
    Terms & ConditionsAccessibility statement