If and AND expression issue

Boa_Casa · 03-31-2021 08:40 AM

HI,

I have a field in which I want to perform calculations based on product type so I have written a formula in auto compute as follows :

IF(AND([Choose one of the following]= Mosquito Net,(([Product Cost (Mosquito Net)]*[No. of Mosquito Nets]) + [Installation Cost(Mosquito Net)]))),
IF(AND([Choose one of the following] = Curtain,IF([IS lead weight required] = YES,[Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount] + [Cost of Leadweight]+[Curtain Tape Cost]+[Backloop Tape cost],[Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount]+[Curtain Tape Cost]+[Backloop Tape cost])))

Where in based on type of product choosen the formula needs to be applied but I am getting the following error:
Column Name ‘Total Amount (AB)’ in Schema ‘Measurement Form_Schema’ of Column Type ‘Decimal’ has an invalid app formula '=OR([Choose one of the following]= Mosquito Net,(([Product Cost (Mosquito Net)][No. of Mosquito Nets]) + [Installation Cost(Mosquito Net)])),([Choose one of the following] = Curtain,IF([IS lead weight required] = YES,[Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount] + [Cost of Leadweight]+[Curtain Tape Cost]+[Backloop Tape cost],[Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount]+[Curtain Tape Cost]+[Backloop Tape cost]))’. Condition OR(([Choose one of the following] = “Mosquito Net”), (([Product Cost (Mosquito Net)][No. of Mosquito Nets])+[Installation Cost(Mosquito Net)])) has an invalid structure: subexpressions must be Yes/No conditions

I am unable to understand where I am going wrong if some guidance could be provide?

hru

Try:

SWITCH([Choose one of the following],
	"Mosquito Net", (([Product Cost (Mosquito Net)]*[No. of Mosquito Nets]) + [Installation Cost(Mosquito Net)]),
	"Curtain", 
	(
			IF(
				[IS lead weight required] = YES,
				([Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount] + [Cost of Leadweight]+[Curtain Tape Cost]+[Backloop Tape cost]),
				([Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount]+[Curtain Tape Cost]+[Backloop Tape cost])
			)
	)
)

Boa_Casa

Heru:

SWITCH([Choose one of the following],
	"Mosquito Net", (([Product Cost (Mosquito Net)]*[No. of Mosquito Nets]) + [Installation Cost(Mosquito Net)]),
	"Curtain", 
	(
			IF(
				[IS lead weight required] = YES,
				([Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount] + [Cost of Leadweight]+[Curtain Tape Cost]+[Backloop Tape cost]),
				([Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount]+[Curtain Tape Cost]+[Backloop Tape cost])
			)
	)
)

HI,

Thanks for the reply I am getting the following error :

SWITCH function is used incorrectly: Inputs to SWITCH() must be an initial expression, one or more value-result pairs, and a default result

Aurelien

Hi @Boa_Casa

You need to add something in the last input of SWITCH function.

So:

you have some values for “mosquito net”,
other values for 'curtain"
and you need to add some default value at the end.

Please have a look to this article

Should be something like:

 SWITCH([Choose one of the following],
	"Mosquito Net", (([Product Cost (Mosquito Net)]*[No. of Mosquito Nets]) + [Installation Cost(Mosquito Net)]),
	"Curtain", 
	(
			IF(
				[IS lead weight required] = YES,
				([Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount] + [Cost of Leadweight]+[Curtain Tape Cost]+[Backloop Tape cost]),
				([Product Total (Curtain)] + [Cost of Stitching] + [Lining Amount]+[Curtain Tape Cost]+[Backloop Tape cost])
			)
	),
   "default_Value"
)

If you have only two possible choices, you can either remove the “curtain” line from the what @Heru just wrote, or use a IF(test, expressionIfYes, expressionIfNo) statement.

Boa_Casa

Hi,

Thanks a lot for this and it did resolve my issue.