# NailDyanC

Caspio Rockstar

409

11

## Posts posted by NailDyanC

1. ### How to speed things up

Hi just to add in the previous comments above, here are some documentations that was sent to me by some support members of Caspio about best practices and guidelines to make your application better:

2. ### Weighted Average

Hi just to add some information and to update this post, you can only use the SUM function in Aggregation. You may check this link for more details and to see other formulas you can use inside Caspio: https://howto.caspio.com/function-reference/

3. ### Cell's Text with Apostrophes

Hi, just to add in the previous comment above if you would like to remove apostrophes (') or use it in your formula, you may use 'char(39)' as an alternative for that symbol. Like for example:

CHARINDEX(char(39),[@field:Fieldname])).

4. ### Multiple Consequential Formula Functions

Just to update and add some additional information to this post, what he mean for nested replace function is there is a replace inside a replace function. So the formula should look like this:

(replace(replace([@field:First_Name],'!',''),'\$',''))

5. ### Formatting a mobile phone number

Hi, just to add in the previous comments above, phone formatting is also possible using calculated fields, calculated values or even in Formula fields.  You may use this formula:

CASE WHEN
[@field:Phone_Number] != ' '
THEN
'(' + SUBSTRING([@field:Phone_Number], 1, 3) + ') ' + ' ' + SUBSTRING([@field:Phone_Number], 4, 3) + '-' + SUBSTRING([@field:Phone_Number], 7, 4)
ELSE
[@field:Phone_Number]
END

6. ### Auto format a phone number field javascript

Hi, just to add in the previous comments above, phone formatting is also possible using calculated fields, calculated values or even in Formula fields.  You may use this formula:

CASE WHEN
[@field:Phone_Number] != ' '
THEN
'(' + SUBSTRING([@field:Phone_Number], 1, 3) + ') ' + ' ' + SUBSTRING([@field:Phone_Number], 4, 3) + '-' + SUBSTRING([@field:Phone_Number], 7, 4)
ELSE
[@field:Phone_Number]
END

7. ### caspio capabilities

Hi, just to add in the previous comments above, phone formatting is also possible using calculated fields, calculated values or even in Formula fields.  You may use this formula:

CASE WHEN
[@field:Phone_Number] != ' '
THEN
'(' + SUBSTRING([@field:Phone_Number], 1, 3) + ') ' + ' ' + SUBSTRING([@field:Phone_Number], 4, 3) + '-' + SUBSTRING([@field:Phone_Number], 7, 4)
ELSE
[@field:Phone_Number]
END

8. ### Validating Phone Number

Hi, just to add in the previous comments above, this phone formatting is also possible using calculated fields, calculated values or even in Formula fields.  You may use this formula:

CASE WHEN
[@field:Phone_Number] != ' '
THEN
'(' + SUBSTRING([@field:Phone_Number], 1, 3) + ') ' + ' ' + SUBSTRING([@field:Phone_Number], 4, 3) + '-' + SUBSTRING([@field:Phone_Number], 7, 4)
ELSE
[@field:Phone_Number]
END

9. ### Unique Records Check

Hi, just to add to the previous comments above, you may use a calculated field/calculated value to check if the value/record is already existed in the table using this formula:

CASE WHEN (SELECT COUNT(FIELDNAME) FROM TABLE WHERE CODE=target.[@field:CODE) > 1 THEN 'EXISTING' ELSE 'NOT EXISTING' END

And, you may create now a JavaScript code that will display an error based on the result of the calculation.  I hope this helps.

Hi just to update this post and add to the previous comments above, you can convert your trial account to a free account however, there are features that are available in the Trial version that are not included in the Free Version. As I contacted one of Caspio's support member, they say that Trial version is much more like the Grow plan that they currently have with it comes to features and Free version is much more different from it. So there might be some issues you may encounter once you convert your trial to a free account.

11. ### Help with formula with text

On 7/5/2019 at 4:16 PM, SunakoChan said:

Hi - please try this formula: 'USER' + CAST([@field:UserID] as VARCHAR).

Since the we need to add a text we need to add "" then convert/cast the field number to varchar.

Hi, just to add in this comment, you may also consider using CONVERT function.

'USER' + CONVERT(VARCHAR, [@field:UserID]). If you would  like to put spaces between the two, you may  use this function: + ' ' + instead of using the "+".

12. ### Use a Formula within a Formula

Hi, just to update this post and add in the previous comment above. Yes, by default we cannot call a formula field inside a formula field however you can copy the whole formula that you have in your other formula field so that it can still perform the calculation that  you would like. For example:

Formula 1:

[@field:field1] + [@field:field2]

Formula 2:

[@field:field2] + [@field:field3]

In your other formula field you can use the following formula;

([@field:field1] + [@field:field2]) / ([@field:field2] + [@field:field3])

13. ### Sql Select Statment To Count Only Fields With Data

Just to update this post and add in the previous comment above, you may use CASE STATEMENT on this workflow:

CASE WHEN

[@field:Dropping_Unit] != ' '

THEN

SELECT COUNT(Dropping_Unit) FROM CH_tbl_Base WHERE District= target.[@field:DistrictName]

ELSE

'Your other condition when the field has blank values'

END

14. ### Parsing a Field

Hi,

As I understand your workflow, you want to extract the text "Code" in the word "Code_Description", right? If that is the case, you may try this formula:

SUBSTRING([@field:Code_Description],0, CHARINDEX('_',[@field:Code_Description]))

On 7/26/2019 at 10:46 AM, bbeshlian said:

Hello,

I have a field named Code_Description and I want to parse the code into a separate field. The contents of the field are separated by an "_".

Current State:

Desired State:

 Code Code_Description CDL CDL_Commercial Drivers License

I modified a piece of code that was shared with me when I asked about parsing a comma separated filed. The code validates but nothing is returned. Here is the modified code:

IsNull(Left([@field:Code_Description],(Patindex('%_%', [@field:Code_Description])-1)),' ')

15. ### Parse a Comma Separated Field

Hi,

Here's another way to extract the "Code" text in your feild:

SUBSTRING([@field:Code_Description],0, CHARINDEX('_',[@field:Code_Description]))

On 5/14/2019 at 8:27 AM, bbeshlian said:

George43,

I copied your formula to parse a field called "Code_Description". I just wanted to get the "Code" piece in a separate field and the field format is Code_Description. Here is what I used:

IsNull(Left([@field:Code_Description],(Patindex('%_%', [@field:Code_Description])-1)),' ')

The formula checks out good but is not returning the "Code". Thoughts?

Thanks

16. ### Separate numbers

Hi, just to update this post. It seems that the function that you will need is "Right(string,length)"since you will be getting the last number from the right which is 5.

You may consider this workaround:

Right((CONVERT(VARCHAR, ([@field: Fieldname]))),1)

Just change the field name based on the calculated field that you are using.

17. ### 1 email after submitting several records

Hi, just to add in the previous comment above, it seems that this workflow is possible using Triggered Actions. You can simply add a field(like a checkbox) at the end of your submission that once it is click then a trigger will fire to send an email. You may check this link about triggered actions:

18. ### Allow numbers only in fields?

Hi, just to update this post and add to the previous comment above, you may consider checking this link about restricting values to be number only:

19. ### Limit the number of search

Hi, just to add in the previous comments above, you may also consider checking these video tutorials about user redirection:

×