RIGHT() formula help and already exists?

1

Hi

I need help with two issues here.

Firstly, I have a formula I used in Airtable that creates a unique ID based on the row number, see below.

‘RL’ & RIGHT(‘0000000’ & Autonumber,7)

This will give the result: RL0000001 depending on what row number is used

I’ve tried to convert this to Coda but it gives me the output: RL 1
“RL” + " " + RIGHT(“0000000” + Autonumber, 7)

My second issue (well not an issue but more of a request), I would like to create a unique id that takes the first 4 digits of the name followed by a number. i.e. Name: Jo Bloggs Company, would be JOBL01. If this already exists then it would be JOBL02 and so on.

Here’s my formula but it will not add the 0 so it ends up being JOBL1, it won’t also check if it exists on a previous row and add as it get’s the 1234 etc from autonumber.

UPPER(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Company Name]," “,”"),",",""),".",""),"/",""),"?",""),";",""),":",""),"’",""),"-",""),"!",""),"@",""),"&",""),"_",""),"+",""),"+",""),4)) + RIGHT(‘00’ + Autonumber,2)

Thanks

2

Sounds like you would benefit from the LeftPad formula. For the first issue, looks like you want an eight digit number followed by the ‘RL’. so you’d need to use Concatenate("RL",LeftPad(RowId(),8,"0")) (be careful not to just try and use + as type coercion will kick in and you’ll end up with just ‘RL1’).

For the second, are you saying we should just take out the first four characters? If so, the following should work:

Left(RegexReplace("Jo Bloggs Company", "\s", ""), 4).Upper()

I’m not a Regex wizard, so someone can probably fix up my Regex to make it better, but that should do the job.

Combine the two to get your final answer:

Concatenate(Left(RegexReplace("Jo Bloggs Company", "\s", ""), 4).Upper(), LeftPad("1",2,"0"))

Hope that helps!

3

Thank you, I’ll take a look at that when I get to my computer.

How about the the issue if it already exists i.e ROBL01 already exists so use ROBL02, if they exist then use ROBL03 and so on?

Thanks.

4

Bit more complex, but not much.

So we already know how to get the four letter identifying code ([Company Name] is a theoretical company name column):

Left(RegexReplace([Company Name], "\s", ""), 4).Upper().

I’ve improved this a bit, so only alphanumberic characters are included:

Left(RegexReplace([Company Name], "[^a-zA-Z\d]", ""), 4).Upper().

It’s probably a good idea to add this as a column formula, let’s call it ‘Company Code’.

Now what we need to do is filter our table for all rows that start with this 4 letter prefix:

TableOfIDs.Filter([Company Code] = thisRow.[Company Code]).Count()

Now, this will grow for every row each time we add a row, but we want our row numbers to be fixed.

What we’ll do to stop this being a problem, is we’ll add an additional filter condition that only matches rows with an ID less than the current row’s ID. First of all, we’ll need to add a Row ID column with the formula thisRow.RowId().

Now we can add in the filter condition: thisRow.RowId() > [Row ID] with an And. This’ll give 0 for the first match, so we just need to add one to get our final ‘Company Order Number’

TableOfIDs.Filter([Company Code] = thisRow.[Company Code] And thisRow.RowId() > [Row ID]).Count() + 1

We’ve done the heavy lifting now, so all we need to do is add in the Concatenation and the LeftPad to get the proper ID:

Concatenate([Company Code],LeftPad([Company Order Number],2,“0”))

I created an example here:

4 Likes
5

Dear @Joe_Innes,

Thank you sharing your know how and detailed explanation.
Great added value :trophy:

Amicable,
Jean Pierre

6

@Joe_Innes

Wow, thank you. Very detailed and allowed me the chance to dive into your code and workout how things work.

Can I ask, although your code worked perfectly well, when I tried to copy it I was getting issues. The issue being was the formula for: Company Order Number. Here’s the formula:

[Table of IDs].Filter([Company Code] = thisRow.[Company Code] AndthisRow.RowId() > [Row ID]).Count() + 1

The issue appeared to be this part:

AndthisRow.RowId()

For some reason (I’m not sure what the term is called in Coda) thisRow was just text and not highlighted. On this basis, I would delete the text ‘thisRow’ and use [thisRow] which seemed to highlight the text or put a space between ‘And and thisRow’ it would then highlight ‘thisRow’. I would save it then go back in and delete the space between ‘And and thisRow’ but all it does is show 1.

I managed to get it to work in the end, (god know how) as I can’t get it to work when I try again.

Is this a bug in Coda?

7

I’ve noticed this as well, it does seem to be a minor bug, spaces do not always copy.

The syntax is just <expr1> and <expr2> so adding a space before ‘thisRow` should fix it.

The word you’re looking for to describe the little coloured blocks is ‘formula chips’

8

@Joe_Innes

I have worked it out, if you copy the formula into a text editor put a space between 'And and ‘thisRow’ then paste it in the formula field (In a new table), it works. If you paste the formula in the formula field and then edit the formula, I can’t get it to work even if I copy and paste from the text editor like above. It seems once I have pasted the formula in there that is not correct then it will never work.

Either way I have it working now. Thank you.

On another note, I have been playing about with a date table, see below:

Although it works, I f I add a new VAT rate into the table, I will have to edit the formula again, I was hoping to use .filter()). So if [Date] was between a start date and end date, show rate.

I thought I might be able to get it to work something similar to your formula but I can’t figure it out, is it possible using filter()?

9

So if I understand correctly, you want a table of VAT rates with start and end dates, and a table of orders with an order date. Based on the order date in the table, the VAT rate should be correctly selected from the VAT rates table?

10

Yes.

Government has changed the VAT rate 3 times since I’ve been in business so I am just trying to cover myself.

11

OK, I think adding this formula to your expenses table will do what you’re asking for:

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’s not well optimised for speed, what it does is for each value in the ‘Expenses’ table, it scans through the list of VAT rates to see if the date falls between one of the start and end dates for VAT rates. If it doesn’t fall inside any of the dates, it returns the most recent VAT rate.

Again, I’m sure there’s a neater and more elegant way to do this, but this should work.

12

@Joe_Innes

Thank you that works, I could see me getting caught out with my other solution where as now, when I add a new rate it works without me having to do anything to the formula.

Once again, thank you.

P.S I’m gonna play about with your code so hopefully I can get a better grasp on formulas.

Thank you.

1 Like
13

@Joe_Innes

Just been playing about with the formula that you posted above

Although this is near perfect it seems, if you use a date that matches the exact ‘Start Date’ or ‘End Date’ then it gives you 20%. I tried adding the equals signs to the greater than or less than arrows i.e. <=, => but that didn’t seem to work for some reason, any ideas?

Thanks

14

Yeah, I see what you mean. So how do you want it to work? From 00:00 on Start Date until 23:59 on End Date?

15

I think so, I didn’t know you had to go so in depth with the time but yeah.

16

I thought you did, but perhaps not. I tried with the greater than or equal to and less than or equal to and it seemed to work (careful, it’s => but >=):

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))

17

Thanks, I must of put the arrow the wrong way round then, I could have sworn I had them correct.

Thank you.

18

Hi Joe

You’ve already helped so much so I thought I would see if you knew how to do this…

I’m trying to track my receipts. So far I have 2 tables, one called receipts and one called line items.

When I purchase something, I will add a receipt to the receipts table, it will generate a ‘Receipt ID’, I’ll then lookup the supplier from the suppliers table, put the date in and finally upload an image for the receipt.

I then go to the line items table, it will generate an ‘Line Item ID’, I’ll then select the ‘Receipt ID’ from a select list, add a product description, cost, vat and total.

Every product on an invoice will have its own row, to link it to the receipt I simply select the ‘Receipt ID’ . So 4 products on a purchase invoice will be 4 line items. Hope that makes sense.

Now in Airtable, once I added a select list, it automatically reversed the selection so I could easily pull the data back to the other table, in Coda it seems to work differently. If a line item has the ‘Receipt ID’ then I need to calculate the the cost, vat and total and add it to the receipts table.

Here’s my forumla:
If(ID=[Line Items].[Receipt ID],[Line Items].[COST (ea)] ,'No' )

What this is supposed to do (If I’m doing it right is, look in the other table and if the Receipt ID’s match then show the cost otherwise leave blank.))

I’ve attached my table to give you a better understanding.

Thanks

19

Hi Joe,

Thanks for reporting this. We must have a bug in our formula copy paste logic. I’ll dig into this today.

Jason

20

Awesome! Didn’t report because I already know there’s a beta for copy-paste (which I’m not actually involved in), so didn’t want to muddy the waters at all.