Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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:

    Code:
    $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]};
    }
    Last edited by aducom; 12-05-2014, 04:13 PM.
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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?

      Comment


      • #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}

        Comment


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

          Then follow the described steps.
          Albert Drent
          aducom software netherlands
          scriptcase partner, reseller, support and (turn-key) development
          www.scriptcase.eu / www.scriptcase.nl

          Comment


          • #6
            Thanks. It works

            Comment

            Working...
            X