10 Replies Latest reply on May 27, 2013 2:59 PM by rfaintich2013

    Is it possible to disable a relationship based on calculation?

    rfaintich2013

      Title

      Is it possible to disable a relationship based on calculation?

      Post

           Hi!  Is it possible to disable a relationship between two tables based on a calculation?  

           For example, I have a multi-relationship set up between a couple of fields between tables....  (based on Athlete's name, Level, Season)  

           This works terrific when I want to see only the stats compiled by that athlete during a particular season, at a particular level (Varsity, JV, etc...)

           My question is, there is a drop down menu, where the user can select the frame of reference, for example, Level....  I want the user to be able to select "ALL LEVELS", but this would mean that I would need to turn off the relationship, since it is impossible for me to have "ALL LEVELS" be an option in the table I'm referencing....  

            

           Thanks for your help!

            

            

        • 1. Re: Is it possible to disable a relationship based on calculation?
          RickWhitelaw

               Create a new Table Occurence of the second table and use a different relationship to return the data you need.

          • 2. Re: Is it possible to disable a relationship based on calculation?
            GuyStevens

                 I'm not sure I understand exactly what you are trying to do but as far as I'm understanding it you do have a point and this is indeed an annoying situation.

                 You have a dropdown list that contains levels. Somewhere you have a relationship that is showing only the selected level. But the problem is you can see only one of the levels at the same time and can't see all of them.

                 This requires a calculation field and a second Value list.

                 I made you a little example file:

            https://dl.dropboxusercontent.com/u/18099008/Demo_Files_FMP12/Ryan%20Faintich/Relationships.fmp12

                 Here's the technique.

                 Let's say you have a value list that goes:
                 Level 1
                 Level 2
                 Level 3

                 These are the three levels you select in your Stats table.
                 Now from your player table you also have a Stats field and there you select the stat you want to see. There is a relationship between these two Level fields.

                 Now let's create a new value list and make it look like this:

                 All Levels
                 -
                 Level 1
                 Level 2
                 Level 3

                 Now in the Stats table you need to create a field where the level is showing, but every stat also needs to contain the value "All Levels".

                 We can do this with a simple calculation field in the Stats table:
                 "All Levels"  & ¶ &Level

                 This will take the word "All Levels" and add the actual level to the next line. For a stat with Level 1 it will say:

                 All Levels
                 Level 1

                 So now every stat record has the value "All Levels" in that calculation field Plus it's own level.

                 Now you use that calculation field in your relationship and use the second value list in the field where you select the Level you want to see.

                 I hope this makes sense, because I feel my explanation is a bit unclear :(

            • 3. Re: Is it possible to disable a relationship based on calculation?
              rfaintich2013

                   Wow!

                   Thank you both so much for taking the time to really help me with this, I can't say enough, in regards to how much this is appreciated!  I hope that I'm able to keep picking up the program so that I can help out others in similar fashion!

                   Especially to Rick!  Thanks so much for your incredibly detailed and inventive solution.  I had a chance to read over it yesterday morning and was thinking about it all day (I was not able to be at my computer, so I had to run it through my head) and I was trying to figure out the specifics...  (I'm going to look at your example file now, so that may answer the question)

                   Rick: I guess I'm just stuck at the notion of creating that field in the stats table; so I'm creating a new field, which is basically going to say "All Levels" and then display the level name from the other field in the table, so that it will end up displaying, "All Levels / Level (X)" But how does that directly solve the relationship problem, since the other field will still not match, (unless relationships matching is altered by carriage returns?  sorry, I'm new, and I don't really understand the full implications of carriage returns)

                   Is it possible to create a calculation in the Viewer Table, whereas if a value is set, for example, Field 1 = "All Levels" than the table sets the field that has the relationship (We'll call it Field 2, and we'll call the related field in Stats Field2a) to be Viewer::Field 2 = Stats::Field2a.  Or does that open a hole in the fabric of the universe?  That would essentially turn off the relationship, correct, if I made, via calculation, the contents of the related field = to the field it's relating?  

                    

                   Thanks so much!

                    

                   Ryan

              • 4. Re: Is it possible to disable a relationship based on calculation?
                rfaintich2013

                     Rick: I've looked at the example file, so I have the solution! —though I'm not sure I understand it!  This is either because I'm not well educated on the functionality of Carriage Returns—which I'm going to start reading a lot about—or because I'm not using a portal for the stat fields, when displayed in the viewer....  I need to see if the reason that your solution is working is based on a filter function built into the portal, or if it's a matter of carriage returns....

                      

                     Thanks again,

                     Ryan

                • 5. Re: Is it possible to disable a relationship based on calculation?
                  GuyStevens

                       Hey Ryan, I don't know if Rick is still with us or not but I thought I'd jump in for a second.

                       What happens in the example file is that you have two related tables. The relationships are on:

                       - The ID field.
                       - The Level field.

                       That means that both the ID and the Level need to be the same in both tables; Otherwise the relationship isn't "true".

                       The problem ofcourse is that this way you can never show "All Levels".

                       Because the levels in the Stats table contain the one or the other level. But they never have the same value in all of the records.

                       So I created a calculated field that sets the words "All Levels" followed by a carriage return and then the actual level.

                       If you base the relationship on this calculated field all of your records will contain the word "All Levels" and then every record also has it's level added.

                       The carriage return makes it so that any of the two values is sufficient to resolve the relationship.

                       I don't know how you are trying to do this in your file. Tell us a little more. You are not using a portal?

                       Are you using two related tables?

                  • 6. Re: Is it possible to disable a relationship based on calculation?
                    rfaintich2013

                         Thanks DaSaint,

                         So, I am using a Portal to display the Match History List, and I am trying to use basic fields to show sum stats from all matches....  My portal displaying the Match History works fine under this new equation, but my summed stats are not working.....

                    • 7. Re: Is it possible to disable a relationship based on calculation?
                      rfaintich2013

                           Lol, I had the calculation set to return as number, once I switched it to text, it started working!

                           So you guys have solved my problem!  I assume I can do this with muliple fields, (meaning, so that both the level, and say, "Season") could match, meaning I could chain a bunch of these together....  I don't see why not....

                            

                           Thanks for the explanation of Carriage Returns as well!

                            

                           Ryan

                      • 8. Re: Is it possible to disable a relationship based on calculation?
                        RickWhitelaw

                             Ryan, I think you're thanking me for help I never provided. What I mean to say is if you want to view a player and see all levels, create another TO and don't have Level as one of the match fields in that new relationship. That way Level need not match to return what you need. In any case that's how I try to solve problems like these. Relationships are clear and fast.

                        • 9. Re: Is it possible to disable a relationship based on calculation?
                          rfaintich2013

                               Lol, either way, you guys have steered me in the right direction, and the problem and issue is now understood!  This is a bit unrelated, but one can not Sum a PatternCount, huh?  For example, in the stats, there is a field called "Win Type", and I wanted to sum the count of times that a win was "Type 1", so I guess I'll have to create a field on the stats page, for each win type, where the result is either a 1 or 0, and then sum those....  Wish I could sum a Pattern Count!

                                

                               Thanks

                          • 10. Re: Is it possible to disable a relationship based on calculation?
                            rfaintich2013

                                 DeSaint!  I'm so sorry, I misread the author name on your original post, waaaay back, by the way, that's why I said, "Rick...."  My apologies!  Thanks so much for your continued help.