Formula

[Role21]

I'm having a problem with my formula. I want to generate an sub-ID. I am trying to create a formula wherein I put '2018' + school ID + section ID.

Here is the formula I created but not producing a correct result.

'2018'+ (SELECT school_ID FROM Profiles WHERE account_ID=[@field:account_ID])+(SELECT section_ID FROM Profiles WHERE account_ID=[@field:account_ID])

Please advise

[cheonsa]

Hi @Role21,

 

I understand that you want to generate a Sub-ID by concatenating some of your fields. May I please verify if how are you going to generate the sub-ID, is it via formula field on your table or via DataPage? Also, may I ask if what DataTypes you used on your School_ID and Section_ID fields.

 

Regards,

kristina

[Role21]

I am doing it on a Calculated Field.  And I am using Integers as my data types.

[cheonsa]

Hi @Role21,

 

Thank you for your response. You can concatenate fields using this formula. You need to convert the DataTypes of your fields to varchar using CAST  function. You can refer to this link: https://howto.caspio.com/function-reference/

 

Hope this helps,

 

Regards,

kristina

[JolliBeng]

Hi Role21,

Kristina is correct, you need to use "CAST" in your formula so you can convert your Data Type  as a VARCHAR.

If you do not convert that, the Data Types are still Integer, so the values add up.

 

-JolliBeng

[DefinitelyNot31337]

Hello,

 

We can also use the CONVERT function which functions exactly the same but with different syntax. In this case:

'2018' +  CONVERT(VARCHAR, [@field:School_ID]) + CONVERT(VARCHAR, [@field:Section_ID])

 

More information about function reference and Calculated Fields in these links:

https://howto.caspio.com/function-reference/

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

 

Regards,

DN31337