Formula

[Donnie]

Hi,

 

Moving to Caspio from Quickbase and I am trying to write a formula that would use my record id which i have a s an auto number field and would like to add the 3 letters SPA in front of it

"SPA"+[@field:record_id] - this says its in valid ans I would also need 0000 zeros so the first number would be SPA00001

This is the formula I used in quickbase:

If([Record ID#]>=10000,"SPA" & ToText([Record ID#]),"SPA" & Right("0000" & ToText([Record ID#]),5))

Any help would be appreciated

[PotatoMato]

Hi @Donnie, may you please try to use this formula instead:

'SPA' + [@field:record_id] 

Make sure that you're also using the correct field name. If you also want to include zeros, you can use prefixed autonumber data type instead.

 

-Potato

[Tubby]

On 5/8/2023 at 11:24 AM, Donnie said:

Hi,

 

Moving to Caspio from Quickbase and I am trying to write a formula that would use my record id which i have a s an auto number field and would like to add the 3 letters SPA in front of it

"SPA"+[@field:record_id] - this says its in valid ans I would also need 0000 zeros so the first number would be SPA00001

This is the formula I used in quickbase:

If([Record ID#]>=10000,"SPA" & ToText([Record ID#]),"SPA" & Right("0000" & ToText([Record ID#]),5))

Any help would be appreciated

I think you are trying to do a conditional prefix, if I am not mistaken. It seems like you are trying to put 0000 between SPA and the autonumber if the autonumber is less than 10,000. If that is right, try this.

CASE WHEN [@field:record_id] >=10000

THEN 'SPA' + [@field:record_id] 

ELSE 'SPA0000' + [@field:record_id] 

END

[Jodie]

If the options above didn't work, maybe try with converting or casting [@field:record_id] as varchar. 

CASE WHEN [@field:record_id] >=10000

THEN 'SPA' +CONVERT(varchar, [@field:record_id]) 

ELSE 'SPA0000' + CONVERT(varchar,[@field:record_id] )

END

or the cast statement CAST([@field:record_id] as VARCHAR)