Re: Problem with select and variable type number

Giuseppe88 · 01-18-2022 11:25 PM

Hello
I am developing an app for a travel agency, I would like to calculate the commissions in different percentages per agency.
In the table [AGENZIE] I insert the data of the agencies and the percentage of commission for each single agency. ROW [COMMISSIONE]

In the commission table I perform the calculations for each agency and in the total I have this formula:

ROW [TOTALE COMMISSIONE]

IF(
[_THISROW].[AGENZIA]=[AGENZIA],
(AGENZIE[COMMISSIONE]*SUM(SELECT
(ESCURSIONI[TOTALE X COMMISSIONI],[_THISROW].[AGENZIA]=[AGENZIA]))/100),
“”)

The result is:
Arithmetic expression ‘(AGENZIE[COMMISSIONE]*SUM(SELECT(ESCURSIONI[TOTALE X COMMISSIONI],([_THISROW].[AGENZIA] = [AGENZIA]))))’ has inputs of an invalid type ‘Unknown’

but if I substitute a number at AGENZIE [COMMISSIONI] it works

IF(
[_THISROW].[AGENZIA]=[AGENZIA],
(20*SUM(SELECT
(ESCURSIONI[TOTALE X COMMISSIONI],[_THISROW].[AGENZIA]=[AGENZIA]))/100),
“”)

I don’t understand what the problem is, can you help me?

Suvrutt_Gurjar

Hi @Giuseppe88

I believe the above part of the expression produces a list instead of a single value and hence the error. For multiplication you will need two numeric values.

Giuseppe88

OK
How can I get the single AGENZIE[COMMISSIONE]value for each agency?

Suvrutt_Gurjar

You may wish to give it a try based on shared details. Please share more details on eaxtly what you are trying to achieve.

Please elaborate how the tables you have mentioned are related and where you wish the expression to calculate the desired value…

Giuseppe88

Thanks always for your kindness and availability

I solved whit this:

IF(
[_THISROW].[AGENZIA]=[AGENZIA],
(ANY(SELECT(AGENZIE[COMMISSIONE],[_THISROW].[AGENZIA]=[AGENZIA]))
*SUM(SELECT
(ESCURSIONI[TOTALE X COMMISSIONI],[_THISROW].[AGENZIA]=[AGENZIA]))/100),
“”)))

Suvrutt_Gurjar

Excellent. Thank you for the update.

In general , in any expression , more the multi row expressions , such as SELECT() , more the app sync time. You may wish to note that while designing your data schema. The below are some helpful articles related to good practices of app design.