5 Replies Latest reply on Apr 9, 2009 2:15 PM by davidanders

    FMP 10 newbie (coming from MYSQL)

    Bauke

      Title

      FMP 10 newbie (coming from MYSQL)

      Post

      Hello,

       

      I'm evaluating FMP 10 and trying to rebuild something I made in MySQL and along with a seperate reportdesigner.

      One of the things I used a lot for reporting was a view from a couple of tables. I already read a lot but I cannot find how I could make a layout with the same records my old view has. At least I'm thinking that a Layout is the answer?!?

       

       

      The query for my view is:

       

      DROP VIEW IF EXISTS resultaten;
      CREATE VIEW resultaten AS
      SELECT w_seizoen
          , r.r_speltype_kort AS w_ronde
          , CONCAT(w_seizoen,'-',  LPAD(w_wnr, 3, "0"),'-', w_speler1,'-', r.r_speltype_kort) AS SWS
          , w_datum
          , CONCAT(DATE_FORMAT(w_datum, '%y'), RIGHT(CONCAT('0',WEEKOFYEAR(w_datum)),2)) AS JaarWeek
          , w_wnr
          , w_brtn
          , w_speler1 AS speler
          , w_speler2 AS tegen
          , w_s1_temaken AS temaken
          , w_s1_car AS car
          , w_s1_hserie AS HSerie
          , (w_s1_hserie/w_s1_temaken)*100 AS HSerieRel
          , w_s1_poedels AS Poedels
          , (w_s1_poedels/w_brtn)*100 AS PoedelsRel
          , ROUND(w_s1_car/w_s1_temaken*100,3) AS Perc
          , ROUND(w_s1_car/w_brtn*s.brtn,2) AS Afwgem
          , IF(w_datum >= '2008-08-01' ,IF(ROUND(w_s1_car/w_s1_temaken*100,3) = ROUND(w_s2_car/w_s2_temaken*100,3), 1,
                  IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 3,
                      IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) < 100,2,0))),
                           IF(ROUND(w_s1_car/w_s1_temaken*100,3) >= 100 AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 1,
                              IF(ROUND(w_s1_car/w_s1_temaken*100,3) = ROUND(w_s2_car/w_s2_temaken*100,3), 1,
                                  IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 3,
                                      IF(ROUND(w_s1_car/w_s1_temaken*100,3) > ROUND(w_s2_car/w_s2_temaken*100,3) AND ROUND(w_s1_car/w_s1_temaken*100,3) < 100,2,0))))) AS berpunten
          FROM wedstrijden w
          LEFT JOIN ronde r ON w.w_ronde =  r.r_speltype
          INNER JOIN systeem  s ON w_seizoen = s.seizoen
      UNION ALL
          SELECT w_seizoen
              , r.r_speltype_kort AS w_ronde
                  , CONCAT(w_seizoen,'-',  LPAD(w_wnr, 3, "0"),'-', w_speler2,'-', r.r_speltype_kort) AS SWS
              , w_datum
              , CONCAT(DATE_FORMAT(w_datum, '%y'), RIGHT(CONCAT('0',WEEKOFYEAR(w_datum)),2)) AS JaarWeek
              , w_wnr
              , w_brtn
              , w_speler2 AS speler
              , w_speler1 AS tegen
              , w_s2_temaken AS temaken
              , w_s2_car AS car
              , w_s2_hserie AS HSerie
              , (w_s2_hserie/w_s2_temaken)*100 AS HSerieRel
              , w_s2_poedels AS Poedels
              , (w_s2_poedels/w_brtn)*100 AS PoedelsRel
              , ROUND(w_s2_car/w_s2_temaken*100,3) AS Perc
              , ROUND(w_s2_car/w_brtn*s.brtn,2) AS Afwgem
          , IF(w_datum >= '2008-08-01' ,IF(ROUND(w_s2_car/w_s2_temaken*100,3) = ROUND(w_s1_car/w_s1_temaken*100,3), 1,
                  IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 3,
                      IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) < 100,2,0))),
                           IF(ROUND(w_s2_car/w_s2_temaken*100,3) >= 100 AND ROUND(w_s1_car/w_s1_temaken*100,3) >= 100, 1,
                              IF(ROUND(w_s2_car/w_s2_temaken*100,3) = ROUND(w_s1_car/w_s1_temaken*100,3), 1,
                                  IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) >= 100, 3,
                                      IF(ROUND(w_s2_car/w_s2_temaken*100,3) > ROUND(w_s1_car/w_s1_temaken*100,3) AND ROUND(w_s2_car/w_s2_temaken*100,3) < 100,2,0))))) AS berpunten
          FROM wedstrijden w
          LEFT JOIN ronde r ON w.w_ronde =  r.r_speltype
          INNER JOIN systeem  s ON w_seizoen = s.seizoen
       

       

      I hope this is clear enough ............... and possible in FMP 10

       

      Thanks in advance for any help.

       

      Bauke P. de Vries





























        • 1. Re: FMP 10 newbie (coming from MYSQL)
          philmodjunk
            

          It's been a while since I've looked at a SQL expression that complex. I won't pretend to understand every last nuance, as that would take more time than I have and my exprience is with other SQL DB's such as MS Access.

           

          I will, however, give you some places to get started in setting up what you need.

           

          Layouts are what you create for reports and data entry forms. You can often use the same layout for both purposes in Filemaker.

           

          I'm guessing that the SQL you posted would be set up as the record source for your report. (That's how it would work in Access anyway.)

           

          In filemaker, you combine two features to set up the "record source" for a given layout.

           

          For each layout, you select a table as the foundation for your report. (Enter Layout Mode, select Layouts | Layout Setup...)

          The fields that you place on your layout, are not, however, limited to just this table. You can place fields on your layout from any related table, with the relationships be determined via the Manage | Database | Relationships. (In SQL expressions, that's where you get your Join expressions linking fields from mutiple tables.)

           

          The second feature is called a "Found set". WHen you perform a find either by hand or through a script, you select your layout, specify find criteria and perform the find to pull up all records that match your criteria. (In SQL this is done by your WHERE clause). Just as you would using SQL, you can sort your records to suit your needs. The records found will link to related tables via their relationships so data from related tables are also available for your report

           

          Thus your layout will display the fields you specify from multiple tables from records selected by performing a find.

           

          Conclusion:

           

          In SQL driven databases, you build a text (SQL) expression that lists all the desired fields, specifying the required relational links between the tables that contain these fields, including find and sort criteria to define a record set for your report.

           

          In Filemaker, you define tables and relationships in Manage | Database, create a layout listing the fields, perform a find, and sort the found records for your report.

          • 2. Re: FMP 10 newbie (coming from MYSQL)
            Bauke
              

            PhilModJunk,

             

            Thank you very much. At least I now know that I'm searching in the right direction and with your explanation I hope to make things work.

             

            Bauke P. de Vries

            • 3. Re: FMP 10 newbie (coming from MYSQL)
              philmodjunk
                 I suggest implementing things one piece at a time while you learn how filemaker works. If at any point, things don't make sense, post your question back to the forum and someone will lend a hand.
              • 4. Re: FMP 10 newbie (coming from MYSQL)
                Bauke
                  

                I will. Thanks!

                 

                Hope to explore some more this weekend.

                 

                Bauke

                • 5. Re: FMP 10 newbie (coming from MYSQL)
                  davidanders
                    

                  I post this link repeatedly, I hope it helps.

                   

                  White Paper for FMP Novices

                  http://www.foundationdbs.com/downloads.html

                   

                  You can create custom Google searches, I created one for Photoshop.

                  http://www.google.com/coop/cse/

                   

                  This is a custom Google Search for Filemaker

                  http://www.searchfilemaker.com/