10 Replies Latest reply on May 27, 2014 4:13 PM by madmike6537

    Stuck on getting data from a filtered portal

    madmike6537

      Hi All,

       

      Edit: I think part of my confusion is I dont understand how the calculation is being ran. Does it run for all related records in that table? Or just the first now?

       

      I have 3 portals and I am trying to hide a green check / red check mark depending on if the user has entered at least one record in each portal, today.

       

      So I am using the FM 13 hide function - which is great. Problem I am running into is if they have entered data into the portal with a different date, it still shows them the green check mark. How can I ensure that they have entered data today specifically? I need to somehow limit my calculation to only the current date, but I cant quite figure out how to get there.

       

      Here is my calculation for the green check mark - any feedback on a better way to do this is welcome. So the green check mark is hidden if: (All these fields are in filtered portal)

       

      IsEmpty(Material_Reading::Material_Name) or

      IsEmpty(Material_Reading::Standard) or

      IsEmpty(Material_Reading::MC Reading) or

      //Material_Reading::Date ≠ Get(CurrentDate) my latest attempt, but obviously doesnt work because not all records = today's date

      or

       

      IsEmpty(Psy_Reading::PsyMetricName) or

      IsEmpty(Psy_Reading::Temp) or

      IsEmpty(Psy_Reading::RH) or

       

      //if there is a peice of equipment, then check these fields, otherwise dont

      If(not IsEmpty(Dehumidifier_Readings::Equipment_Brand_ID);

       

       

      IsEmpty(Dehumidifier_Readings::Temp_Reading) or IsEmpty(Dehumidifier_Readings::RH_Reading); "")

        • 1. Re: Stuck on getting data from a filtered portal
          beverly

          Let's think the other way. What fields must not be empty?

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: Stuck on getting data from a filtered portal
            madmike6537

            All fields must have data in them, if there is a record (today). And I want to require that there be a record entered today for the first two portals:

             

            So -

             

            Material Portal - Must have record entered today, and must have name field, standard, and mc reading all entered.

            PsyMetric Portal - Must have record entered today, and must have name field, temp, and rh reading all entered.

            Equipment Portal - Must have temp and RH fields completed, but only if there is a record there. Sometimes there will not be a record entered today and if that is the case they dont need to fill anything out for that portal.

             

            Thanks for your response!

            • 3. Re: Stuck on getting data from a filtered portal
              erolst

              madmike6537 wrote:

              So I am using the FM 13 hide function - which is great.

              Isn't it just?

               

              Not sure what you mean by “filtered portals”; your code names three different table occurrences, so I assume you mean portals that each point at one of those.

               

              Anyway, what you need is complicated to achieve with the native tools.

               

              But try this (untested, no warranties, especially not for speed):

               

              Let ( [

                cd = Get ( CurrentDate ) ;

               

                 matSQL = " SELECT COUNT ( * ) FROM Material_Reading WHERE \"name field\" IS NOT NULL and \"standard\" IS NOT NULL and \"mc reading\" IS NOT NULL and \"Date\" = ? " ;

                 // 'date' is a reserved word in SQL – as are a bunch of others you wouldn't suspect ('standard'? – probably …)

                 // Material Portal - Must have record entered today, and must have name field, standard, and mc reading all entered.


                psySQL = " SELECT COUNT ( * ) FROM Psy_Reading WHERE \"name field\" IS NOT NULL and \"temp\" IS NOT NULL and \"rh reading\" IS NOT NULL and \"Date\" = ? " ;

                // PsyMetric Portal - Must have record entered today, and must have name field, temp, and rh reading all entered.

               

                dehumSQL1 = " SELECT COUNT ( * ) FROM Dehumidifier_Readings WHERE \"temp\" IS NOT NULL and \"rh reading\" IS NOT NULL and \"Date\" = ? " ;

               

                // Equipment Portal - Must have temp and RH fields completed, …

                dehumSQL2 = " SELECT COUNT ( * ) FROM Dehumidifier_Readings WHERE theDate = ? " ;

                // … but only if there is a record there.

               

                matOKToday = ExecuteSQL ( matSQL ; "" ; "" ; cd ) ;

                psyOKToday = ExecuteSQL ( psySQL ; "" ; "" ; cd ) ;

                 dehumOKToday = ExecuteSQL ( dehumSQL1 ; "" ; "" ; cd ) ;

                 dehumToday = ExecuteSQL ( dehumSQL2 ; "" ; "" ; cd )

                ] ;

               

                 matOKToday AND psyOKToday AND ( deHumOKToday OR not deHumToday )

               

              )

               

              There would also be a native (naïve …?) approach involving three (invisible) portals, filtered along those lines, and summary fields, plus GetLayoutObjectAttribute(), but that is a bit convoluted …

              1 of 1 people found this helpful
              • 4. Re: Stuck on getting data from a filtered portal
                madmike6537

                Thanks!

                 

                You are correct there is 3 portals on the layout that I am referencing.

                 

                Then I have two objects, a red check and a green check that I am trying to hide - or I could just put one on top of the other.

                 

                I am working through your code right now, its a bit over my head but I am getting there.

                 

                One question - with SQL where you list "fieldName" I am guessing you dont need the table name there like TableName::Field name, right?

                • 5. Re: Stuck on getting data from a filtered portal
                  madmike6537

                  Well I am trying to get this to work but so far no luck. I modified it a bit to narrow the portals down to only records in this room, I changed my date field names so as to not interfere with SQL and added another filter in the SQL statement to match more closely my portal filters.

                   

                  Problem is - no matter what it seems to return true, as my checkmark never shows up no matter what fields I delete or add   You might notice I eliminated the DEHU portal for now as I am just trying to narrow down what the issue is. It seems like it should work!

                   

                  Let ( [

                    cd = Get ( CurrentDate ) ;

                    roomID = Room::_Room_PK;

                   

                     matSQL = " SELECT COUNT ( * ) FROM Material_Reading WHERE Room_KF = roomID and WHERE Material_Name IS NOT NULL and Standard IS NOT NULL and MC_Reading IS NOT NULL and Reading_Date = ? or Reading_Date =  Customer::Actual_Setup_Date" ;

                   

                   

                    psySQL = " SELECT COUNT ( * ) FROM Psy_Reading WHERE _RoomScope_KF = roomID and WHERE PsyMetricName IS NOT NULL and Temp IS NOT NULL and RH IS NOT NULL and Reading_Date = ? or Reading_Date = Customer::Actual_Setup_Date" ;

                   

                   

                  /*  dehuSQL1 = " SELECT COUNT ( * ) FROM Dehumidifier_Readings WHERE _Room_KF = roomID and WHERE Temp_Reading IS NOT NULL and RH_Reading IS NOT NULL and Reading_Date = ? " ;

                   

                    dehuSQL2 = " SELECT COUNT ( * ) FROM Dehumidifier_Readings WHERE _Room_KF = roomID and WHERE Reading_Date = ? " ;*/

                   

                    matOKToday = ExecuteSQL ( matSQL ; "" ; "" ; cd ) ;

                    psyOKToday = ExecuteSQL ( psySQL ; "" ; "" ; cd )

                    /* dehuOKToday = ExecuteSQL ( dehuSQL1 ; "" ; "" ; cd ) ;

                     dehuToday = ExecuteSQL ( dehuSQL2 ; "" ; "" ; cd )*/

                    ] ;

                   

                     matOKToday and psyOKToday /* and ( dehuOKToday or not dehuToday )*/

                   

                  )

                  • 6. Re: Stuck on getting data from a filtered portal
                    user19752

                    One thing is

                    You can't use FM variable name in SQL sentence, so roomID must be ? like as cd.

                    You can use multiple parameters for 1 SQL.

                     

                    And

                    You can't use table::field syntax in SQL.

                    Customer::Actual_Setup_Date also should be parameter

                    or use table.field syntax and add table to FROM phrase.

                    (It is depend on your filter settings)

                    1 of 1 people found this helpful
                    • 7. Re: Stuck on getting data from a filtered portal
                      erolst

                      Note these changes:

                       

                      Let ( [

                        cd = Get ( CurrentDate ) ;

                        roomID = Room::_Room_PK ;

                        setupDate = Customer::Actual_Setup_Date ;

                       

                         matSQL = " SELECT COUNT ( * ) FROM Material_Reading WHERE Room_KF = roomID ? " & "/*no underscore here?*/" & " and WHERE Material_Name IS NOT NULL and Standard IS NOT NULL and MC_Reading IS NOT NULL and ( Reading_Date = ? or Reading_Date =  ? ) " ;

                       

                        psySQL = " SELECT COUNT ( * ) FROM Psy_Reading WHERE \"_Room_KF\" = ? roomID and WHERE PsyMetricName IS NOT NULL and Temp IS NOT NULL and RH IS NOT NULL and ( Reading_Date = ? or Reading_Date = ? ) " ;

                       

                      /*  dehuSQL1 = " SELECT COUNT ( * ) FROM Dehumidifier_Readings WHERE \"_Room_KF\" = ? roomID and WHERE Temp_Reading IS NOT NULL and RH_Reading IS NOT NULL and Reading_Date = ? " ;

                         dehuSQL2 = " SELECT COUNT ( * ) FROM Dehumidifier_Readings WHERE \"_Room_KF\" = ? roomID and WHERE Reading_Date = ? " ;

                      */

                       

                        matOKToday = ExecuteSQL ( matSQL ; "" ; "" ; roomID ; theDate ; setupDate ) ;

                        psyOKToday = ExecuteSQL ( psySQL ; "" ; "" ; roomID ; theDate ; setupDate )

                        /* dehuOKToday = ExecuteSQL ( dehuSQL1 ; "" ; "" ; roomID ; theDate ) ;

                         dehuToday = ExecuteSQL ( dehuSQL2 ; "" ; "" ; roomID ; theDate )*/

                        ] ;

                         matOKToday and psyOKToday /* and ( dehuOKToday or not dehuToday )*/

                      )

                       

                      See if the attached file gets you along.

                      • 8. Re: Stuck on getting data from a filtered portal
                        madmike6537

                        This is fantastic. THANK YOU!

                         

                        Taught me a TON about how SQL works and how I can use it. The sample file was a huge help. If you are at DevCon look me up and I will buy you a beer!

                         

                        Thanks!!

                        • 9. Re: Stuck on getting data from a filtered portal
                          erolst

                          You're welcome. Glad to see you got it working.

                          madmike6537 wrote:

                          If you are at DevCon look me up and I will buy you a beer!

                           

                          Alas not, but appreciate the thought! Maybe next year. Drink one for me … 

                           

                          PS: I forgot: best place for that calc is probably a CF; call it in the Hide calculations, passing the field names as arguments (“parameters” … ).

                           

                          Why clutter the schema – and putting it into each object is redundant and may be a bit fragile.

                          • 10. Re: Stuck on getting data from a filtered portal
                            madmike6537

                            Ah - I have never created a custom function before but never a better time to learn. I will do some googling

                             

                            Edit: done, that was easier than I thought!