3 Replies Latest reply on Dec 7, 2015 3:34 PM by Extensitech

    Conditional Value list related to many-to-many join table (FM13 pro)


      Hi Everyone,

      I will do my best to make this clear... (I have attached the a picture of the relationships window to assist)


      I have a "audit" DB that has a "user" table and a "discipline" table among others. The audit layout is attached to the audit table to capture all of the audit information.


      I have a user layout  with a portal to a many to many join table to join the disciplines to the users.


      Right now, I have a start-up script setting a global variable to the users name, and  each time a new audit record is created a script places that username in the name field on the audit table. So when the user starts the audit the username is already added.


      What I am looking for is a way to have the discipline drop-down filtered to only show me the disciplines that are assigned to the user. I was able to make it work for two other fields, but their relationship was direct (show 3 and audit 2 in the picture)


      I am getting stuck at creating the relationship and the value list that sees  the username as populated by the variable, and compares that to the disciplines linked in the join table. The join table is ID numbers. I even tried to add a username field in the join table, but it didn't work.




      Thank you in advance

        • 1. Re: Conditional Value list related to many-to-many join table (FM13 pro)

          A little hard to follow, even with the screenshot.


          1. What TO is your layout based on?
          2. Are you showing the value list in a portal? If so, what TO is the portal based on?
          3. What TO are the values in your value list coming from?
          4. What TO do you have in your value list for "show only related values starting from"?


          Now, what results are you seeing? All values? No values? Wrong values? There are many possible meanings for "it didn't work".


          Just trying to clarify. I appreciate how hard it is to describe something sometimes, when you're already in up to your neck.


          Chris Cain


          • 2. Re: Conditional Value list related to many-to-many join table (FM13 pro)

            On all attempts I get "No values defined".


            Your questions:

            1. The layout with the value list drop-down is attached to the Audit TO.
            2. I have not tried to use a portal, only a drop down on the audit layout with an attached value list.
            3. The list needs to be populated with the values from Disciplines table>DisciplineName. I have tried variations of TO like this but I cannot wrap my head around where the relationships are, so I am just guessing.
              1. A new TO of the Audit with lines drawn usernameFK in the JOIN (I have the text username populating in the usernameFK field on the join TO thinking it needed to see the actual text name)
              2. A new TO of Audit with lines drawn to usernameFK and discipline in the JOIN
              3. A new TO of Audit and JOIN with combinations of lines to either one.
                1. The list goes on..
            4. I have tried to "show only" from both the audit TO and also a new audit TOs,  and from the JOIN and a new JOIN TOs.


            It seems harder to explain what I have tried then just what I am trying to do. I will try to simply.

            As i have it right now:

            • Audit Table: Will store the entries made by users during the audit.
            • User table: Will store the user information
            • Discipline Table: Stores the list of disciplines
            • User_Discipline JOIN: creates relationship between USERID and DISCIPLINEID


            So I need the a drop down on the Audit layout (Audit TO) to see the usersname, which is set by "getaccountname", and then use that name to look at the relationship between user and discipline, only show those disciplines that are related.


            I was able to do this between the parks and shows table (bottom left of the picture). What is throwing me is the username to userID/DisciplineID relationship.


            I really hope this helps...

            • 3. Re: Conditional Value list related to many-to-many join table (FM13 pro)

              It appears that perhaps you have Audit::UserName linked to Users::UserID? If so, is that just field naming or is that a mismatch (or is the screenshot just poorly showing the relationship)?


              Other than that, I don't see any other obvious candidates for the problem. If that's not it, here are some things I'd try to troubleshoot, from the Audit layout. (I'd use the data viewer for this, but if you don't have FMPA, just drop these fields on the layout temporarily.)


              1. Check that Audit::UserName really does have a value
              2. Check if you can "see" Users::UserName - if so, get a count and make sure it's 1
              3. Check if you can "see" Join_USER_DISCIPLINE::disciplineFK - if so, get a count and make sure it's the right number of disciplines
              4. Check if you can "see" "DISCIPLINES::DisciplineID - if so, get a count


              Also check:

              • Are all the fields on the "right" side of the relationships (as currently oriented) indexed?
              • Do all the field types match (number to number, text to text, etc.)?


              This is, admittedly, tedious. Unless I happened to spot the problem in my opening paragraph, though, this is a good procedure to find out which relationship is breaking, and zero in on the problem.


              If you do have FMPA, and you're using data viewer, also check out the RelationInfo function as a troubleshooting tool. I like it because I can compare values in actual records without going in and out of the RG, and I'm more likely to spot where I've grabbed the wrong field, or put the wrong operator.


              If this doesn't get you there... this looks like a fairly small file. Is there any chance you could post the actual file, or a clone thereof? Or maybe send it to me backchannel?


              Chris Cain