11 Replies Latest reply on Mar 9, 2017 7:21 AM by dtcgnet

    Let + Case statement don't work

    user28397

      Hi

       

      i'm tryin to filter a portal. I made this script:

       

      Let ( Account = Get ( AccountName );

       

       

      Case ( Account = "Administration" ; Perios Dashboard Links::id_service_fk = "1";

             Account = "Animation" ; Perios Dashboard Links::id_service_fk = "2";

             Account = "Cafeteria" ; Perios Dashboard Links::id_service_fk = "3";

             Account = "Cuisine" ; Perios Dashboard Links::id_service_fk = "4";

             Account = "Direction" ; Perios Dashboard Links::id_service_fk = "5";

             Account = "Ergomotricite" ; Perios Dashboard Links::id_service_fk = "6";

             Account = "Gouvernante" ; Perios Dashboard Links::id_service_fk = "7";

             Account = "Infirmier Chef" ; Perios Dashboard Links::id_service_fk = "8";

             Account = "Infirmier Chef Adjoint" ; Perios Dashboard Links::id_service_fk = "23";

             Account = "Infirmier Zone 1" ; Perios Dashboard Links::id_service_fk = "9";

             Account = "Infirmier Zone 2" ; Perios Dashboard Links::id_service_fk = "10";

             Account = "Lingerie" ; Perios Dashboard Links::id_service_fk = "11";

             Account = "Soins 1" ; Perios Dashboard Links::id_service_fk = "12";

             Account = "Soins 2" ; Perios Dashboard Links::id_service_fk = "13";

             Account = "Soins 3" ; Perios Dashboard Links::id_service_fk = "14";

             Account = "Soins 4" ; Perios Dashboard Links::id_service_fk = "15";

             Account = "Soins 5" ; Perios Dashboard Links::id_service_fk = "16";

             Account = "Soins 6" ; Perios Dashboard Links::id_service_fk = "17";

             Account = "Soins 7" ; Perios Dashboard Links::id_service_fk = "18";

             Account = "Salle a Manger" ; Perios Dashboard Links::id_service_fk = "19";

             Account = "Secretaire" ; Perios Dashboard Links::id_service_fk = "20";

             Account = "Technique" ; Perios Dashboard Links::id_service_fk = "21";

             Account = "Economat" ; Perios Dashboard Links::id_service_fk = "22";

             Account = "Admin" ; Perios Dashboard Links::id_service_fk = "24";

          

      ) )

       

      But it don't work.

      Can someone tell me where is the error?

       

      Thanks

        • 1. Re: Let + Case statement don't work
          Johan Hedman

          You are answer to Account = "Admin" will be Perios Dashboards Links::ID_Service_fk = "24".

           

          I think you do not understand how you handle two parameters in a Case ( ) function.

           

          Case(

           

          FieldA = "Admin" and FieldB = 24; "Then your result is";

          FieldA = "Economat" and FieldB = 22; "Then another result"


          )

          • 2. Re: Let + Case statement don't work
            Louis A. Voellmy

            A script to filter a portal? I suppose a formula ...

             

            Louis

            • 3. Re: Let + Case statement don't work
              Johan Hedman

              For all kind of filter in a portal the result should be either 1 or 0. 1 for if it is true and 0 if it is NOT.

               

              So you this case,

               

              Case(

               

              FieldA = "Admin" and FieldB = 24; 1;

              FieldA = "Economat" and FieldB = 22; 1


              )

              • 4. Re: Let + Case statement don't work
                keywords

                Each of these expressions: Perios Dashboard Links::id_service_fk = "1" — will result in either a 1 (true) or 0 (false), whereas you have used them with the (apparent) intention of them being result of another test. You need to rethink your logic.

                • 5. Re: Let + Case statement don't work
                  Markus Schneider

                  seems all is not in FileMaker terminology..

                   

                  -> check the manual for 'let' and 'case'

                   

                  (-:

                  • 6. Re: Let + Case statement don't work
                    user28397

                    What i need to do is to filter the perios dashboard links based on user

                    account

                     

                    Il 09 Mar 2017 1:09 PM, "Markus Schneider" <noreply@filemaker.com> ha

                    scritto:

                     

                    Let + Case statement don't work

                     

                    réponse de Markus Schneider

                    <https://community.filemaker.com/people/mschneider?et=watches.email.thread>

                    dans Discussions - Afficher la discussion complète

                    <https://community.filemaker.com/message/647315?et=watches.email.thread#647315>

                     

                    • 7. Re: Let + Case statement don't work
                      beverly

                      I will start this for you and you can finish. Hopefully it will make more sense. You want to return a value and place it so that the portal is filtered based on the related field "Perios Dashboard Links::id_service_fk ".

                      Perios Dashboard Links::id_service_fk  = <<your calc here>>  // your portal filter

                      The "<<your calc here>>" should do something like this:

                      Let (

                      [ Account = Get ( AccountName )

                      ; myValue = Case

                           ( Account = "Administration" ; 1

                           ; Account = "Animation" ; 2

                           ; Account = "Cafeteria" ; 3

                           ; Account = "Cuisine" ; 4

                           ; Account = ....

                           // finish your case statement here with each 'Account'

                           ; "" // default will be empty

                           ) // end case

                      ]; myValue // what gets returned should only be one value

                      ) // end let

                      This should return one value (or none) to be placed in the "filter" and only show zero or one related record in the portal.

                      beverly

                      • 8. Re: Let + Case statement don't work
                        philmodjunk

                        calculating values of 1, 2, 3, 4.....

                         

                        doesn't make sense for a portal filter expression.

                         

                        A portal filter expression normally compares a value from the portal table to some other value or expression. If that comparison evaluates as True for a given related record, it is allowed to show in the portal.

                         

                        If your portal shows shows records from a table occurrence named "perios dashboard links", the original expression could be perfectly correct and the reason that no records appear in the portal could be due to other causes that have nothing to do with the calculation. The id_service_fk field might have the wrong value or there could be a problem with the portal's relationship.

                        • 9. Re: Let + Case statement don't work
                          beverly

                          Ah, the design of this is likely needing changing, true, Phil!

                           

                          I think I'd have the data as related, but use a global field for the "key" to match and set (by the same let-case calculation) so that the fields (in portal or not) are unique to that Account.

                           

                          However, given that we are talking about

                          Get ( AccountName )

                          I think we are trying to side-step SECURITY in some way and that's just not advisable in any form.

                           

                          RLA (record level access) should be used, instead.

                           

                          Search this forum for other suggestions.

                          beverly

                          • 10. Re: Let + Case statement don't work
                            philmodjunk

                            All good points Beverly, but sometimes we do this kind of thing to improve the user experience by hiding those pesky "no access" screens that cover stuff the user is not permitted to see.

                             

                            I haven't tested this recently, but if you have a portal to records where view is not permitted, does the portal record appear covered in a "no access" screen or is it omitted from the portal?

                             

                            And this might just be a case of customizing the data set to fit the needs of the user without any security concerns.

                             

                            But like you, I'd look at setting up an unstored calculation field that works off of either account names, privilege sets or extended privileges to set a Match value that controls what appears in the portal. Should be a slightly simpler expression (unless you retrofit the data to match on account or privilege set name instead of a number--then it becomes a LOT simpler). And it can also produce better system performance than such a complex portal filter.

                             

                            Or original poster should reply back with more info on what hey have and what they are trying to do if they still cannot get this to work to their satisfaction.

                            • 11. Re: Let + Case statement don't work
                              dtcgnet

                              Keeping Beverly's comments about security in mind...you might do something like the following instead of the route you're going.

                               

                              Upon login, your startup script does the following:

                               

                              Set Variable $$UserGroup //then use your Let statement, but modify it like:

                              Let ( Account = Get ( AccountName );

                              Case ( Account = "Administration" ; $$UserGroup = 1 ;

                                     Account = "Animation" ; $$UserGroup = 2 ;

                                    etc.)

                              )

                               

                              $$UserGroup would then have a value of 1, 2, 3, or whatever. Note: you don't need quotation marks around numeric values.

                               

                              In your portal, you filter the records this way:

                               

                              $$UserGroup = Perios Dashboard Links::id_service_fk

                               

                              (Note: I edited this because I'd not used the same $$UserGroup name all the way through).