counting partial result

[Marcus69]

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?

 

 

 

[comment]

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.

[Marcus69]

thanks for answer, but i need to see on the top "how many type "E" have been found .

what I  am not able to do is to count how many  "E" in the field Tipo   ( search by serial I.e. )

[dwdata]

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!

[Marcus69]

thanks for your answer, maybe can help, but i m not able to apply sorry, can explain me hot to insert this function in the field? keep on receive error " no field" 

[dwdata]

Here is a sample file.  You will need to define a "Custom Function". Look at the ONLINE HELP for instructions on how to do that.

Hope this helps!

dwdcSample.fmp12

[eos]

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 October 11, 2015 by eos

[Marcus69]

@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 October 12, 2015 by Marcus69

[Marcus69]

@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?

 

[dwdata]

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

[Marcus69]

you are the man, it works great even on my file, i m studying what u wrote, i want too learn... thanks so much !