AnsweredAssumed Answered

FMP 10 newbie (coming from MYSQL)

Question asked by Bauke on Apr 8, 2009
Latest reply on Apr 9, 2009 by davidanders

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





























Outcomes