Jump to content
  • 0

Formula


Donnie

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

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 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

Link to comment
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)

 

Link to comment
Share on other sites

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.

Guest
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...
×
×
  • Create New...