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
Solved! Go to Solution.
@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.
i still get the same error
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