6 Replies Latest reply on Apr 20, 2016 6:45 AM by erolst

    Conditonal Value List w/ Portal and Unrelated Table


      I am starting to believe I am attempting the impossible here, but here goes.  I have the following tables:

      Screen Shot 2016-04-19 at 3.09.00 PM.pngScreen Shot 2016-04-19 at 3.09.56 PM.pngScreen Shot 2016-04-19 at 3.10.15 PM.png


      Here is the scenario that I am trying to accomplish.   The Job table tracking projects in house.  I would like a "time-line" of activity for each job.  I have created the Job_Dates table to house all of the different dates associated with an given project (received, started, in progress, completed...)  I also need to assign an employee to the project.


      The database solution currently in house has an "Rapid Input" screen which facilitates the entering of many job/date combinations for a given date type (Verified, Completed, Reported, etc.).  I have a layout with a portal to allow the rapid input of the dates by category.


      My issue is portal of entering the Due Dates and assigning the employee.  I understand that the tables are not related and need to set up a script to "transfer" the employee input to the Job table.  What I am trying to accomplish is a conditional value list showing the active employees, and using that to trigger a script to update the Job table with the selected employee.  In order to make the "Rapid Input" layout to function correctly, I have the following TO's:

      Screen Shot 2016-04-19 at 3.21.20 PM.png

      to feed the layout:

      Screen Shot 2016-04-19 at 3.23.30 PM.png

      So where I stand now is the tabs activate a script to change the value of DateType (the date type in the header is there for debugging), I can enter the JobID, and the date and date type function properly but the drop down list for active employee does not.  I only receive one entry which is "?".  Taking the focus off the record all of the shown values for employee change to the first record on the employee table (since that field is a global).


      First, my difficulty to the condition value list.  Even reading most of the previous posts on the subject I am stuck.


      Second, am I better off creating with field within the Job_Dates table as a transfer field for each record to record the employee assignment?


      Any assistance would be appreciated.


      Thank you,



        • 1. Re: Conditonal Value List w/ Portal and Unrelated Table



          Playing around some more, I see the relationship between the EmployeeID and the Rapid Date Input.  That functionality is working, but I am still stuck with the dreaded Conditional Value List.



          • 2. Re: Conditonal Value List w/ Portal and Unrelated Table
            I don't understand your data model.
            If you select an employee for the creation of a JobEvent (I like that entity name better than yours… ), but instead of storing that selection with the event, write it into the Job itself …
            … then you have no employeeJob history, and/because the employee assigned to the job will change with every new event creation (or not, if you select the same employee – but that would be incidentally; it could change)
            Ask yourself what fact that employee selection is supposed to express: either …
            this employee is assigned to / responsible for that stage/event – then why store that fact in the Job proper?
            or …
            this employee is assigned to / responsible for the Job proper – then what does that have to do with an event creation (a sub-entity to the job)?
            As to your conditional value list: how is it defined?
            It should use related fields (1. primaryKey, 2. name, show second only) from the Employee_active TO, starting from the Employee TO.
            Whether these two TOs are related to your event creation context or not is irrelevant.
            • 3. Re: Conditonal Value List w/ Portal and Unrelated Table

              Erolst,  Thank You for the input!


              The employee is assigned to the Job proper, not the individual job events.   The reasoning for this portal:  The job flow is such that the employee is assigned along with the due date for which I am designing the layout so the user can quickly enter the Job ID, Due Date, and Employee without having to search for individual Job ID.


              I have recovered from my brain hiccup to make the portal function as designed, with the exception of the value list.  I have followed the suggestions in some other posts to create a global variable that is always true, and the self joining the table from the global "always true" field to the IsActive field.

              Screen Shot 2016-04-19 at 10.30.46 PM.png

              The value list is setup as follows:

              Screen Shot 2016-04-19 at 10.32.49 PM.png

              Which I believe is following the suggestions from you and the other posts.

              • 4. Re: Conditonal Value List w/ Portal and Unrelated Table

                Yes, that should work – if the InitialsWithName field is indexed.


                Even if it is indexable in principle, FM will not complain if for some reason it became unindexed at some point, and will no re-create the index automatically.


                If e.g. you have a previously stored calculation where a referenced field became unstored, that calculation will, too; if you make the referenced field stored again, the calculation will not become stored again automatically.


                As to your adding the project employee from this context: maybe employ a Hide condition to only show this popup if the selected stage/event is DateDue.


                And maybe consider to use (as you hinted at) a different UI altogether: e.g. a popover with a filtered portal into employees (I assume we're not talking thousands of employees here), which allows you to do some pre-processing, like only allowing selection of plausible/legal events, or checking that any date for events other than project start are > dateProjectStart, and whatever other business rules you may have.

                1 of 1 people found this helpful
                • 5. Re: Conditonal Value List w/ Portal and Unrelated Table

                  Thank you for the assistance Erolst...


                  After your confirmation that the value list "should" work, I decided to create a new value list, and step into the list by adding just the ID field.  That worked correctly. 


                  The error was in the calculation, the calculation was outputting the as a number instead of text.  That explains all of the questions marks (I would be shrugging too!).


                  Thanks again.

                  • 6. Re: Conditonal Value List w/ Portal and Unrelated Table

                    Good to hear it works.


                    I forgot to mention the “wrong result type” case, possibly because I habitually check that setting when creating a calc field.


                    Now that you've been bitten, you may want fall into that habit, too …