3 Replies Latest reply: May 26, 2012 5:49 AM by Amatu Allah Neveen Ebrahim RSS

    Master-Detail help

    929107
      Hi ,

      I have a master detail block with the relation

      How can I insert where condition in detail block which has group function

      like max(date)
      to explain in detail

      In my detail I have several records of same code but different dates and status,

      I just want to display the latest date's details and not all

      Like master block

      'AA'

      details

      'AA',01/01/2012,'status close';
      'AA',01/05/2012,'status Open';

      Here I just want to display 'status open' since its the latest information

      So i need something like
      where date = max(date) group by ...,..

      Any idea
        • 1. Re: Master-Detail help
          Prabodh
          Hi,
          This is really a SQL query, not much to do with Forms.

          Something like this.
          SELECT ....
          FROM <DETAIL_TABLE>
          WHERE <DETAIL_TABLE>.<DATE_COLUMN> = (SELECT MAX(<DETAIL_TABLE>.<DATE_COLUMN>) FROM <DETAIL_TABLE>
                                           WHERE <DETAIL_TABLE>.<FK_COLUMN> = 'AA' )
          .....
          BTW, DATE is a reserved word and cannot be a column_name.

          The equivalent of the WHERE Clause above is what you need to put into the detail block.

          For any better response, you should post the actual query with actual table/column names.

          Regards,

          Edited by: Prabodh on May 26, 2012 1:02 PM
          • 2. Re: Master-Detail help
            929107
            Thx for the response ..

            Have you ever tried a group function in where clause of any block?
            • 3. Re: Master-Detail help
              Amatu Allah Neveen Ebrahim
              Hi

              Pls use the property bloc-level
               
              DEFAULT_WHERE
              Then,write ur condition statement there...

              Is your block is a query only and you don't have to do DML then pls create a view and use the view as datablock-datasource.

              But if you have to apply DML so u have to use your "group by"-query in a database-view and do the dml in an insted-of-trigger on the view.

              In this case the form would just issue the DML againt the view as if it is a "*normal*" table.


              Hope this helps...

              Regards,

              Amatu Allah