7 Replies Latest reply on Nov 9, 2016 10:32 PM by danielfarnan

    Conditionnal Formatting kills me

    gilo

          Hi

       

       

      I have 3 tables:

       

      Players

      Members

      Team

       

      I want to be able to assign players to teams, by adding memberships

       

      player_id ------< member_key_playerid

                                    member_key_teamid >-------------teamid

       

      On the layout based on player I have a portal teams_available (checks the birthdate of the player and shows the teams he could play in) - work great

       

      Now I want FM to mark in red, the teams in teams_available portal if there is a membership of the player with that team.

       

       

      I've been trying all afternoon, but I can't get it done.... why?!?!

       

      Can someone help me?

        • 1. Re: Conditionnal Formatting kills me
          philmodjunk

          I don't see a relationship here that will work for what you want.I'd guess that you have another occurrence of Team that you use for your portal to show what teams are available for a given player.

           

          Players-------<AvailableTeams

           

          If you add an occurrence of memberships like this:

           

          Players------<AvailableTeams>-------Members|available

           

          You can set up a conditional format expression that refers to a field in Members|available to get the results that you want.

          1 of 1 people found this helpful
          • 2. Re: Conditionnal Formatting kills me
            David Moyer

            Hi,

            what are you trying?  Will you post a screen shot of your Conditional Formatting dialog?

            1 of 1 people found this helpful
            • 3. Re: Conditionnal Formatting kills me
              mw777rcc

              I am assuming your structure looks something like the following for your teams_available portal...

               

              player_criteria(age etc) --------------< Teams Available

               

              You will need to add a TO for memberships off to the left (or use SQL to gather the information)

               

              player_criteria(age etc) --------------< Teams Available >---------- memberships (by team id)

               

              I prefer SQL rather than cluttering the relationship graph so I will put that here:

               

              ExecuteSQL ( "Select Count (*) from memberships where playerID = ? and teamID = ?" ; "" ; "" ; LayoutTable::PlayerID ; PortalTable::TeamID )

               

              If you have the experience it is also recommended not to hard code any field or table name in your calculations just in case they ever change names in the future. 

               

              You can do that with a calculation like:

              GetValue ( Substitute ( GetFieldName ( Membership::PlayerID ) ; ":" ; "¶" ) ; 1 )

              Returns "Membership"

               

              GetValue ( Substitute ( GetFieldName ( Membership::PlayerID ) ; ":" ; "¶" ) ; 3 )

              Returns "PlayerID"

               

              So putting that into a let statement to clean up the code: (I use ~ for let variables but it isn't required)

              Let([

              ~table = GetValue ( Substitute ( GetFieldName ( Membership::PlayerID ) ; ":" ; "¶" ) ; 1 ) ;

              ~id_field = GetValue ( Substitute ( GetFieldName ( Membership::PlayerID ) ; ":" ; "¶" ) ; 3 ) ;

              ~sql = "Select Count (*) from " & ~table & " where " & ~id_field & " = ? and teamID = ?"

              ];

              ExecuteSql ( ~sql ; "" ; "" ; LayoutTable::PlayerID ; PortalTable::TeamID )

              )

               

              With boolean logic, any matches found will be boolean true (highlight red) otherwise it will return zero which is boolean false.  So a modified version of this calc should work for your conditional formatting.

               

              Hope this helps.

              2 of 2 people found this helpful
              • 4. Re: Conditionnal Formatting kills me
                gilo

                Thank you all for your answers.

                 

                @Mw777rcc: Thank you, but your answer is exceeding my knowledge of FM and my non-existing knowledge of SQL.

                 

                How do I get FM to check

                 

                CM::playerID >------- Player::PlayerID

                                                   Player::birthday >-------< Teams available ::date_start & ::date_end

                 

                That Teams available::name is bold if there is a record in Membership with the playerID and the teamID.

                Bildschirmfoto 2016-11-02 um 00.38.37.png

                Bildschirmfoto 2016-11-02 um 00.37.21.png

                • 5. Re: Conditionnal Formatting kills me
                  philmodjunk

                  It appears that you are trying to use a relationship to only match to teams that meet your age qualification. If so, then all teams listed in a portal to that occurrence would be formatted as bold as ineligible teams would not appear in the portal.

                  1 of 1 people found this helpful
                  • 6. Re: Conditionnal Formatting kills me
                    gilo

                    Hey guys

                     

                     

                    Thank you for all your help.

                     

                     

                    I had to go another way, so now I have a portal with a List which only show available team, so I have the thing I was looking for.

                     

                     

                    Thank you guys

                    • 7. Re: Conditionnal Formatting kills me
                      danielfarnan

                      One thing to be aware of is the possibility of spaces in field or table names - you need to escape them so that you don't get an error:

                       

                      Let([

                      ~table = GetValue ( Substitute ( GetFieldName ( Membership::PlayerID ) ; ":" ; "¶" ) ; 1 ) ;

                      ~id_field = GetValue ( Substitute ( GetFieldName ( Membership::PlayerID ) ; ":" ; "¶" ) ; 3 ) ;

                      ~sql = "Select Count (*) from \"" & ~table & "\" where \"" & ~id_field & "\" = ? and teamID = ?"

                      ];

                      ExecuteSql ( ~sql ; "" ; "" ; LayoutTable::PlayerID ; PortalTable::TeamID )

                      )