New Formula Field

[ray]

Hello. I am trying to sort out the capabilities of the new formula data type in v9.4. I'd like to combine text fields and a date field in the formula but I get an invalid formula error when just combine the fields with a "+ Str(1)+". Do I need to convert the date to a string? Also, the formula seems to reference the stored value of a foreign key in the table. Is there a way to use the display value of a related field instead? Thank you.

[MayMusic]

You can only access the real values that are stored in your table and you should be able to concatenate date and text by adding single quote in this format:

 

'[@field:Date]' + '[@field:text]'

[Breee]

Good Morning, 

I'm using the new release 9.8 UniqueID formula field feature to get a Unique_Value for data restriction purposes. I have a field in a table where the unique value is working properly. The field datatypes are data/time and text. I used the following script to get the unique value in the table. 

Convert(varchar,Month([@field:TruckDate])) +'/' +Convert(varchar,Day([@field:TruckDate])) + '/' +Convert(varchar,Year([@field:TruckDate])) + [@field:Project_Name]

 

I'm attempting to set up another Unique Value in a separate table. The field types are 2  number fields and 1 text field. The formula is verified by the caspio formula checker but the values are not showing up in the table properly. The fields are as follows. 

[@field:number1] (number data type), [@field:number2](number data type), [@field:Text] (text data type).

 

 

The above method '[@field:Number1]' + '[@field:Number2]' adds the number fields which is not the result that's needed. It also will not allow the concatenate of the text field [@field:text] with the number data.

1000102 (result Unique Value)

 

If I use  '[@field:Number1]''[@field:Number2]' the formula is valid but the values show as the formula fields themselves.

[Number1][Number2]

 

I need for the number fields to display as the true value received in the table and not to be altered in any way. I'd like the Unique Value to display in the table as follows: 

[@field:Number1] [@field:Number2] [@field:Text]

1000001 101 HelpProject

 

I'm not sure how to display the formula to get the above result. I'm certain that my formula is way off. I reviewed the formula fields help section that's linked to the 9.8 release update information; however it does not demonstrate how to concatenate fields with two different data types (number/ text) using the new method. I also reviewed the forums but the information provided is pre-release where forms were used to create the composite ID's. 

 

Thank you for taking a look. 

Bre

 

 

 

 

 

 

 

 

[Breee]

I was finally able to generate the Unique Value using str. 

 

(Str([@field:Number1])+Space(1)+Str([@field:Number2]))+Space(1)+[@field:Text]

 

The Number 1 field is and will always be a 7 digit number. The str uses a default of 10 digits. I'd like to correct the spacing of the Number 1 field to 7 digits for table formatting purposes. I'm not sure how to lessen the default number to 7 in the above formula. 

 

Thanks. 

Bre