Jump to content
  • 0

New Formula Field


ray

Question

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.

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

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

 

 

 

 

 

 

 

 

Link to comment
Share on other sites

  • 0

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

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...