Jump to content
• 0

# Formula

## Question

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

## Recommended Posts

• 0

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

##### Share on other sites

• 0
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

##### Share on other sites

• 0

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)

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
• #### Browse

×
• Create New...