I have an ExecuteSQL question that I'm hoping you could help solve for me [FMP18A on MAC].

The best way for me to outline my question is to show the process I took to get to where I am currently.

[Example data only]

I have the following TABLE [Sales_Database] and COLUMN [FRUIT] setup, and my SQL calculation gives the following result:

------------------

ExecuteSQL (

"SELECT Fruit, COUNT(*)

FROM Sales_Database

GROUP by Fruit

ORDER by 2 DESC

"

; "" ; "")

 

Apple, 77

Oranges, 67

Grapes, 57

Pears, 47

Bananas, 37

------------------

I then broke it down further, wanting the results to only display the top most value (Apple). So, my SQL calculation and result was thus:

------------------

"SELECT Fruit, COUNT(*)

FROM Sales_Database

GROUP by Fruit

ORDER by 2 DESC

FETCH FIRST 1 ROWS ONLY

"

; "" ; "")

 

Apple, 77

------------------

 

So far, so good.

 

HOWEVER, for the purpose of using the result in a HIDE OBJECT WHEN calculation, I need the result to only show the word "Apple" no number attached.

 

Removing the COUNT(*) just results in the calculation ordering the values by the length of their letters.

 

I hope there's a solution to this problem, and thank you all in advance.

Don't forget the other functions available to you. You could set the ExecuteSQL field separator to a carriage return, or take your result and use Substitute -- and then get the first value, so the whole thing would be:

GetValue( Substitute(result;", ";¶) ; 1 )

That said, I would highly advise you not to use ExecuteSQL in any hide condition, tooltip, conditional format, etc. In a hosted, multi-user file, an uncommitted record can cause the client to attempt to retrieve the entire database. Performance will suffer to say the least.

Welcome to the forums!

Hi Fitch, thank you for your reply.

Would you mind elaborating a little on how I implement the "GetValue( Substitute(result;", ";¶) ; 1 )"

Does it goes at the end of the SQL query, or elsewhere?

This is a cross-post:
https://community.claris.com/en/s/question/0D50H00007oQM1MSAW/executesql-how-do-i-sort-my-columns-by-count-value-but-only-display-the-value-in-the-results

The answers you were given there are practically the same as the one given above.

 

58 minutes ago, Fitch said:

I would highly advise you not to use ExecuteSQL ...

I agree. I would suggest a native alternative, but I am not sure what is the exact purpose of this exercise.

 

33 minutes ago, comment said:

This is a cross-post:
https://community.claris.com/en/s/question/0D50H00007oQM1MSAW/executesql-how-do-i-sort-my-columns-by-count-value-but-only-display-the-value-in-the-results

The answers you were given there are practically the same as the one given above.

Apologies, I had no idea that both these websites were connected.

I'll continue this discussion in the other thread.

Appreciate all your help with this.

41 minutes ago, Jay86 said:

I had no idea that both these websites were connected.

They are not, but some of us read both. You are welcome to post on both, but a heads up would be appreciated in order to save duplication of effort.