Multi-Field Formula: tried to apply string operator to numeric value (Contain)

[MostafaBouzari]

Hi everyone,

I want to change A/P column based on a condition.

my condition is 

IF ToNumber([Saldo])<0 && Contains(ToString([A / P Allocation]), 'Aktiv') THEN 'Passiv1'  ELSEIf   ToNumber([Saldo])>0 && Contains(ToString([A / P Allocation]), 'P&L' OR 'Passiv') THEN  
'Aktiv2'  
else
[A / P Allocation] ENDIF

Note that i have added ToNumber and ToString after that i got the Error but that didn't solved my problem.

But Multifield Tool is giving me a an error:

Multi-Field Formula (3)	Formula: tried to apply string operator to numeric value(Contains)

Which i can not understand what that means.

Also this is my data

Account	Date	Description	Saldo	U/A	Chart of Accounts	HGB GKV	A / P Allocation
7598	2018-12	ttt	-10	unadjusted	unallocted	xxx	P&L
8764	2016-12	uuu	56.14	unadjusted	xxx	xxx	P&L
3467	2017-12	zzz	148.34	unadjusted	xxx	xxx	P&L
8631	2018-12	xxx	281.4	unadjusted	xxx	xxx	P&L
211	2016-12	eeee	-3800	unadjusted	rr	B.2.1	Aktiv
22345	2017-12	eeee	-9450	unadjusted	rr	B.2.1	Aktiv
64567	2018-12	eeee	-9450	unadjusted	rr	B.2.1	Aktiv
453	2016-12	eeee	28306	unadjusted	rr	H	Passiv

and i have Selected A/P Allocation in the configuration 

What am i missing?

Regards

[patrick_digan]

@MostafaBouzari I don't think you need tostring around A/P, and you can't put an or inside contains. I think it would be:

IF ToNumber([Saldo])<0 && Contains([A / P Allocation], 'Aktiv') THEN 'Passiv1'  ELSEIf   ToNumber([Saldo])>0 && (Contains([A / P Allocation], 'P&L')  OR Contains([A / P Allocation],'Passiv')) THEN  
'Aktiv2'  
else
[A / P Allocation] ENDIF

As for Saldo, you only need the tonumber function if saldo is a string variable. You can click on the line between tools to confirm the data type. 

EDIT: perhaps I'm missing something, but I don't see any reason to use a multi-field formula for your formula. A normal formula tool should work fine. 

[MostafaBouzari]

i still get the same error

[RolandSchubert · Accepted solution]

Hi @MostafaBouzari,

I wonder, why you are using a Multi-Field Formula. It seems, you want to assign a new value to the column 'A/P Allocation', so a Formula tool should do the job.

In additoon, the field [A / P Allocation] seems to be a string field, the formula could be:

IF ToNumber([Saldo]) < 0 AND Contains([A / P Allocation], 'Aktiv') THEN
'Passiv1'
ELSEIf ToNumber([Saldo])>0 AND (Contains([A / P Allocation], 'P&L' ) OR Contains([A / P Allocation], 'Passiv')) THEN
'Aktiv2'
ELSE
[A / P Allocation]
ENDIF

Hope this is helpful.

Best,

Roland