• 0
Login to follow this  
ezIQchad

Calc Field (-)

Question

Hi,

I'm stumped.

This code works

Substring([@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2,Charindex('
======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2)

This code breaks with only a minor adjustment: changing + to - at the very end

Substring([@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) + 2,Charindex('
======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) - 2)

This code is a substring in example two that works until I add it to the code above

Charindex('
======',[@field:ScratchPad],Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad]))) - Charindex(': ',[@field:ScratchPad],Charindex('Departure Airport: ',[@field:ScratchPad])) - 2

 

Any help will be appreciated

Share this post


Link to post
Share on other sites

2 answers to this question

  • 2

Hi ezIQchad,

You are receiving an error because the Substring function of Caspio doesn't allow a negative value for the length parameter.

Quote

Substring(string, start, length)

Returns a substring starting at the starting position defined by start, with total length defined by length.

Below are two possible solutions to your issue:

Solution 1

Substring(

Substring([@field:ScratchPad],
                                Charindex(': ',[@field:ScratchPad],
                                                Charindex('Departure Airport: ',[@field:ScratchPad])) ,
                                                
                                                                Charindex('======',[@field:ScratchPad],
                                                                                Charindex(': ',[@field:ScratchPad],
                                                                                                Charindex('Departure Airport: ',[@field:ScratchPad])))
                                                                                                
                                - Charindex(': ',[@field:ScratchPad],
                                                Charindex('Departure Airport: ',[@field:ScratchPad])) ) 
,2,len([@field:ScratchPad])
)

This one uses the same logic as your previous code, only now it has another Substring that encapsulates your whole code.

Solution 2

Substring([@field:ScratchPad],
	(Len('Departure Airport: ') + Charindex('Departure Airport: ', [@field:ScratchPad], 0)),	
	(Charindex('=====', [@field:ScratchPad]) - (Len('Departure Airport: ') + Charindex('Departure Airport: ', [@field:ScratchPad], 0)))
)

This second solution acts somewhat like Javascript's slice() method.

 

Both solutions have been tested using different test data, and it looks like they are both returning the same results.

Let me know if you have any questions. I hope this helps.

- G

Share this post


Link to post
Share on other sites
  • 0

Thanks!

Those are good options.

I ended up using a workaround using ABSOLUTE.

Substring([@field:ScratchPad],
	Charindex(': ',[@field:ScratchPad],
		Charindex('Passport Date of Issue: ',[@field:ScratchPad])
	) + 2,
	Abs(
		Charindex('===',[@field:ScratchPad],
			Charindex(': ',[@field:ScratchPad],
				Charindex('Passport Date of Issue: ',[@field:ScratchPad])
			)
		) - 
		Charindex(': ',[@field:ScratchPad],
			Charindex('Passport Date of Issue: ',[@field:ScratchPad])
		) - 2
	)
)

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Login to follow this