Subject Re: order by on different way
From Dirk <non@non.com>
Date Fri, 19 Nov 2021 20:51:28 +0100
Newsgroups dbase.getting-started

Op 18/11/2021 om 10:46 schreef Mervyn Bick:
> On 2021/11/17 17:53, Dirk wrote:
>
>> Mervyn
>>
>> that's how i do nowadays, i want to modify to by order and parameters
>> for searching
>>
>> the stammdat.dbf has 6 indexes and want to work with only one on
>> idnummer,
>
> The only index I can see on IDNUMMER is used in vraagbak_KONTAKT where
> this is the linking field to IDNUMMER in the parent table in VRaagBak_FIRMA
>
> I don't like having indexes and ORDER BY clauses in the same form so I
> would change the link to mastersource.  It will, however, not cause any
> problems if you leave it as it is.  It won't affect any changes to
> vraagbak_FIRMA.
>
>    this.vraagbak_KONTAKT = new QUERY(this)
>     with (this.vraagbak_KONTAKT)
>     //   onOpen = class::vraagbak_KONTAKT_ONOPEN
>     // Calculated field created in Select statement
>        left = 399.0
>        top = 66.0
>        width = 66.0
>        height = 37.0
>        database = form.databank_klanten
>        sql = 'Select c.*,name||" "||voornaam as Voor_en_naam from
> "communicate" c where idnummer = :idnummer'
>        params['idnummer'] = "" //don't worry if idnummer is numeric
>        masterSource = form.vraagbak_firma.rowset
>        active = true
>     endwith
>
>     with (this.vraagbak_KONTAKT.rowset)
>        with (fields["task"])
>           lookupSQL = "select * from job"
>        endwith
>        autoEdit = false
>     endwith
>
> If there is more than one contact for a specific idnummer you can order
> this rowset.
>
>        sql = 'Select c.*,name||" "||voornaam as Voor_en_naam from
> "communicate" c where idnummer = :idnummer order by name'
>
>
> Stammdat.dbf is only used in form.vraagbak_firma as a parent table.  The
> method used to order this table is quite independent of the method used
> to order any child table.
>
> You can order the rowset by any of the fields in the table.  The table
> opens in natural order (unless you modify the existing constructor code
> by adding an ORDER BY clause) but once you have set an order you need to
> take the ORDER BY clause into account when you change the order.
>
> To set the first order, or change the order
>
>
>      fvf = form.vraagbak_firma //to save some typing :-)
>      fvf.active = false
>      fvf.sql = substr(fvf.sql,1,at('"stammdat"', fvf.sql)+9)
>      fvf.sql += " order by fieldname "
>    //or
>    //fvf.sql += " order by fieldname1,fieldname2 "
>      fvf.active = true
>
> Mervyn.

  Mervyn

   thanks for your comphrensive analyse

    i just wondering using the mastersource

     i you have another form to add and modify contact as mentioned

     this.VRAAGBAK_CONTACT = new QUERY(this)
    with (this.VRAAGBAK_CONTACT)
       left = 112.0
       top = 88.0
       width = 99.0
       height = 37.0
       database = form.databank_gegevklant
       sql = [Select c.idnummer, c.name, c.voornaam, c.task, c.telefon,
c.fax, c.gsm, c.email, c.verlaten, c.info;
                   from "communicate" c where idnummer = :idnummer]
       params["idnummer"] = ""
       masterSource = form.vraagbak_firmas.rowset
       active = true
    endwith


    i use the save()
     form.vraagbak_contact.rowset.fields["idnummer"].value =
form.vraagbak_firmas.rowset.fields["idnummer"].value

     using the masterfield and masterrowset


      a form.vraagbak_contact.rowset.save() is sufficient

Dirk