Jump to content

counting partial result


This topic is 3062 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi everybody, please I need tips to solve this problem :(

in the table you can see the partial result of search operation operated on "serial" field.

 I need to have partial count in the field "tipo" for each "E", "R" "N" type, how many they are?

I tried relation, i tried to count partial, but i not solve it out.

please can anybody suggest me out to fix?

 

 

 

1.png

2.png

Link to comment
Share on other sites

I need to have partial count in the field "tipo" for each "E", "R" "N" type, how many they are?

The simplest way is to use a summary field defined as Count of [Seriale] (or any other field that cannot be empty). Sort the records by Tipo, and show the counts in a sub-summary part.

Link to comment
Share on other sites

You can use this Custom Function called "CustomList:

http://www.briandunning.com/cf/868

and then define a field that uses the ExecuteSQL function:

E_Count  = (Calculation, Number Result, Unstirred) =

Let([

a = "'" & Substitute(CustomList ( 1 ; Get ( FoundCount ) ; "getnthrecord ( dwdcSample::seriale ; [n] )" ); "¶"; "','") & "'";

b = ExecuteSQL ( "SELECT count(*) FROM databasetubi WHERE tipo = 'E' AND seriale IN (" & a & ")" ; "" ; "" ; "" )

];

b

)

Place this field at the top of your list. This calculation will adapt to your found count. 

Hope this helps!

Link to comment
Share on other sites

Here is a sample file.  You will need to define a "Custom Function".

With FM13 and newer, you could use a summary field “ListOf: primaryKey”, rather than a CF, to restrict the query to the found set.

Edited by eos
  • Like 1
Link to comment
Share on other sites

@dwdata

thanks thai is exactly what i need, now i need to understand how you did it :) I want to learn ! thanks a lot.

 

 

@eos

your solution works fine too, I want learn yours too. Thanks for support buddy

 

thanks a lot

Edited by Marcus69
Link to comment
Share on other sites

@dwdata

sorry to bother you again buddy, but the calculation field for E count:

Dichiara([

a = "'" & CercaeSost(CustomList ( 1 ; Get ( ContoTrovati ) ; "getnthrecord ( seriale ; [n] )" ); "¶"; "','") & "'";

b = EseguiSQL ( "SELECT count(*) FROM databasetubi WHERE tipo = 'E' AND seriale IN (" & a & ")" ; "" ; "" ; "" )

];

b

)

 

report the function CustomList does not exist, and do not allow me to proceed. I check all FM and i did not find where to call for this function can you give me a tip please?

 

Link to comment
Share on other sites

Since you are on FMP13 (according to your profile), here is revised version of my sample file (that does not use the custom_list () custom function. It uses the ListOf type for a summary field.

Hopefully you can get this working.

dwdcSample2.fmp12

Link to comment
Share on other sites

This topic is 3062 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.