Calc Field (-)

[LWSChad]

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

[Gutchilimansi]

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

[LWSChad]

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