how to put in a field a counter of how many records are a client

Code Examples Grid
#1

I’ve three tables (Client, Travel and Bank) and I created a form with link for each table with “Left Join” for to show all records of client:
example: table client (id_client, name, directions); Travel (Id_travel, country, id_client); Bank (Id_bank, name_bank, id_client).
records Table Client (1, Juan, Valpo;2, pedro, Vi?a;3, Sofia, re?aca)
records Table Travel (20, Chile,1; 30, Brasil,3)
records Table bank (100, EdwardsCity, 1;200, Scotiabank,2)

And I created a field for count records for each table, although it don’t have records in other tables, but I don’t know how to create the event for this field.

thanks

#2

The most easy way is to add an onrecord event and put an appropiate sql statement in that like 'select count(*) from … '. After select you can retrieve the value and put it into your custom field:


$check_sql = "SELECT max(*) from mytable where your argument";
sc_lookup(rs, $check_sql);

if (isset({rs[0][0]}))     // Row found
{
    {mycustomfield} = {rs[0][0]};
}
#3

I did the following

Select *
from Client as c
LEFT JOIN travel t ON c.id_client=t.Id_client
LEFT JOIN bank b ON a.id_client=b.Id_client
WHERE a.id_client={myfielfd}

and How I assign this value?

#4

Sorry,

Select COUNT(t.Id_client)
from Client as c
LEFT JOIN travel t ON c.id_client=t.Id_client
LEFT JOIN bank b ON a.id_client=b.Id_client
WHERE a.id_client={myfielfd}

#5

Afaik it should be something like Select COUNT(t.Id_client) as c
from Client

Then follow the described steps.

#6

Thanks. It works