You could use calculations in the context of Table A:
ValueCount ( FilterValues ( Table B::status ; "New" ) )
ValueCount ( FilterValues ( Table B::status ; "No Collection/Quote Only" ) )
I thought this was the answer from heaven, But for some reason its just returning 0 :\
It is working, Its me thats not, Thank you very much Philip, You are a Genius!
Yes, sorry, I missed something.
It should be:
ValueCount ( FilterValues ( List ( Table B::status ) ; "New" ) )
This is perfect, Thank you Philip
Not sure why you'd need such a complicated SQL query. It shouldn't need be more than this:
ExecuteSQL ( "
SELECT Status, Count ( Status ) FROM Child
StoreLocation = ?
GROUP BY Status" ;
Char ( 9 ) ; "" ; A::StoreLocation )
CHAR ( 9 ) puts a tab character between the status name and the status count so that you can use the inspector's paragraph formatting to put a tab stop in place to align your results into two neat columns of status values and their counts.
I have 50-70 users sitting on the dashboard at all times, when they load the layout it fires off the SQL, The problem is Group By on hosted solutions is apparently very very bad
After changing to Philips code, our load times are down from 20-40 seconds down to 3 (very happy bunny as long as the boss never finds out )
The problem is Group By on hosted solutions is apparently very very bad
Care to explain what you mean by that?
I do acknowledge that the interpreted nature of how SQL is evaluated can make it slow in some situations. I'm just wondering if you tested this and found it slow or are referring to a specific source that advised against it.
My main point was that you had stated earlier:
it uses a really really hacky ExecuteSQL Script that does about 40 calls to get the values.
And I didn't see any reason for such a complex query to get the needed subtotals.
Mainly related to
I only know that when I had half the company on the dashboard its bearable, but at full load it was taking a ton of time and Now that I have changed from SQL, pretty sure it was causing the slow down. I cant say for certain that it is the Group by doing it though
That explanation works for me. Like I said, I was wondering....
I always consider the possibility that this function may be slow in a given circumstance and test accordingly. This specific case, is new to me so thanks for furthering my education.