Formulamap not formatting correctly

1

Hi all

A kind member on here kindly helped me getting a formula together to find a date that sits between a start and end date and show a certain columns content based on that.

The formula works fantastic but it keeps giving me an error “Cannot convert value to a specified format”.

Here’s the formula:

If([VAT Rates].FormulaMap(If(CurrentValue.[Start Date] <= thisRow.Date And CurrentValue.[End Date] >= thisRow.Date, CurrentValue,false)).filter(CurrentValue).Count() < 1, [VAT Rates].Sort(false(), [Start Date]).Rate.First() , [VAT Rates].FormulaMap(If(CurrentValue.[Start Date] <= thisRow.Date And CurrentValue.[End Date] >= thisRow.Date, CurrentValue,false)).filter(CurrentValue))

It basically takes from percentage formatted column and put back into another percentage formatted column.

Anyone know the issue? Sometimes it appears to work then it doesn’t.

2

Hi @Juanmata,

It is bit hard to figure out what is going wrong with this without looking at document, if you could share document with support@coda.io it would help us to resolve it quickly.

Thank you,
Krunal.

3

@Krunal_Sheth

Hi

I’ve emailed it to you and also shared it on here so others can follow incase they run in to the same issue.

Please see attached my document, you’ll notice that if the ‘VAT Rate’ is 20% then the calculations work, if the ‘VAT Rate’ is anything other than that then it gives the error ‘Cannot convert value to specified format’

It’s driving me crazy.

4

Try this formula instead?

[VAT Rates].Filter(If([End Date].IsNotBlank(),[Start Date] <= thisRow.Date and [End Date] >= thisRow.Date, [Start Date]<=thisRow.Date )).Rate.First()

3 Likes
5

Much nicer, more readable, and much more performant!

@Juanmata I strongly recommend you move over to @Krunal_Sheth’s formula if you haven’t already, which is better in every way.

For future Codanauts who arrive in this thread, the issue with the original formula (as well as being slower and harder to read) is that two different data types were being returned. Under certain conditions, a percentage was indeed being returned, while under others, a row was being returned. It wasn’t immediately apparent that this was the case because the row’s display column was percentage formatted. As a result, some rows displayed an error, others did not.

2 Likes
6

A really nice formula and very simple to understand.

Thank you to both for getting me where I needed to be, I’m still at the very start of my project but I’m now on the right track and already using it to track receipts and invoices.

1 Like
7

@Krunal_Sheth

It hadn’t yet occurred to me to use this construction: Filter(,if(,,))

Most excellent! Coda continues to surprise! :sunglasses:

3 Likes