2 Replies Latest reply on Mar 24, 2017 10:10 AM by beverly

    Operators and Conditions with ESS Reporting

    edwilson

      Is it possible to include operators or conditions such as "Like" and "In" to develop a real-time ESS report? I am trying to develop a real-time report from an external Oracle database based on the below SQL query, but I am unaware of how to generate an ESS report based on specific criteria. Thank you.

       

      -------------------------

       

      Select y.LAST_NAME "LAST NAME", y.FIRST_NAME "FIRST NAME", y.DESCR_LABEL "COURSE", y.LETTER_GRADE_FIN "FINAL GRADE", V.DESCR "HIGH STAKES TYPE", z.descr "HIGH STAKES DESCRIPTION", x.NUMERIC_GRADE "NUMERIC ELEMENT"

      from OUWB_PROD.sr_enroll_el_grade x, OUWB_PROD.SR_V_ENROLL y, OUWB_PROD.sr_crsect_grade_el z, OUWB_Prod.SR_STUDENT w, OUWB_Prod.SR_GRADE_EL_TYPE v

      where x.ENRL_ID = y.ENRL_ID

      and x.CSGREL_ID = z.CSGREL_ID

      and y.NAME_ID = w.NAME_ID

      and z.GETY_ID = v.GETY_ID

      and v.GETY_ID in ('MID', 'FIN', 'RETE', 'LABP') --Final Exam, Midterm Exam, Lab Practical, Retest

      and z.descr not like ('%Prelim%')

      and w.WDRW_REASON_CODE is null

      group by y.LAST_NAME, y.FIRST_NAME, y.DESCR_LABEL, y.LETTER_GRADE_FIN, V.DESCR, z.descr, x.NUMERIC_GRADE

      order by LAST_NAME, DESCR_LABEL;

        • 1. Re: Operators and Conditions with ESS Reporting
          Johan Hedman

          FileMaker just takes the Table as it is. If you want to query a table from another ODBC source

           

          If you want to filter your ESS tables in FileMaker I suggest you use Trigger for OnLayoutLoad to show the records you want to show

          • 2. Re: Operators and Conditions with ESS Reporting
            beverly

            Using ESS, you are looking at the data in the external tables and can use any of the FileMaker functionality on the data (with permissions and other caveats). Relationships are possible (your JOINs).

             

            There is no "LIKE", except FileMaker has some really good "wildcards":

            Because the default is "begins-with" (any word in the field), you can use that fact and any of the symbols to get really good finds.

             

            There is also no "IN", but FileMaker has a very clever little functionality called "Multi-line key". This is a return delimited list and typically a global field that can be indexed and used as a 'Primary key' to match any (OR find) of the values in the list with any records' key (whatever field matches). With multiple find criteria this kind of key isn't so helpful.

             

            Scripted finds are recommended so you can easily edit and make "AND" requests or "OMIT" requests. There are also Constrain and Extend find functions & script steps.

            You can save values (from elsewhere) into variables that can be used with the scripted finds:

            Set Field ( my field ; $myVar )

             

            There is also ExecuteSQL() function which may be able to perform this query on your ESS table(s). You get back a text field with tab-delimited columns and return-delimited rows (or other delimiters). It's not a temp-table like SQL, but many have found ways to "parse" and put into a virtual table. With large datasets and/or complex joins, the usage of ExecuteSQL() may be prohibitive.

             

            If you have ESS and can get a VIEW of the query, you can open the view as if another table (with all you values from the query). You need to have control of the SQL server or have good IT that can create the view for you.

             

            beverly

            FM & SQL db administrator