Skip to main content

Deducted credit note amount from invoice amount based on document type

  • May 29, 2023
  • 3 replies
  • 57 views
Tiaan_Terblanch
Forum|alt.badge.img+8

Good Day,

We added a CSV file with all our invoice amounts, and we have also added a CSV with all our credit notes.
Is there a formula that will automatically deduct the credit note amount from the invoice amount based on the Document Type column?

So I need a formula that will automatically deduct the credit note amount from the invoice amount based on the document type that they are.

Screenshots are attached to show what it looks like when we import it. 

1 Attachments

3 replies

Rupert_Hoffsch1
Forum|alt.badge.img+21

Hi @Tiaan_Terblanch, you can use an if statement in a formula. So IF({Document Type} = "Credit", "INVOICE AMOUNT" - "CREDIT AMOUNT")

Sorry can't see your exact fields.

Hope that helps!

Tiaan_Terblanch
Forum|alt.badge.img+8

Hi Rupert, thank you so much for your response. I tried the formula, but it does not work, so I am adding more information.

You will see from the Original Amount column until the Tax Amount column are all formula columns, as I had to add this formula: 

{Original Amount [Original]}/100 as Airtable does not provide decimals with a comma.
Okay, now I want to deduct the credit note amount from the invoice amount in all these columns (it can happen separately) so that the total amount at the bottom reflects the deduction.

What would that formula be, and how do I add it at the back of my original formula so it all works in that column?

I hope you can help. Thanks
Tiaan_Terblanch
Forum|alt.badge.img+8

We need the credit amounts to be deducted from the invoice amounts and shown in the sum fields if we group them together.
The screenshot shows that the credit amount is added to the invoice amount to give the group the total amount.
But in reality, the credit amount should be deducted from the invoice amount, and the correct amount should show in the sum amount for the group.



I hope I make sense.

Thanks

Terms & ConditionsAccessibility statement