2 Replies Latest reply on May 3, 2010 6:09 AM by paint

    Lists from several fields

    paint

      Title

      Lists from several fields

      Post

      I apologize for the Subject description. I am not sure how to explain it short which makes it difficult to search also.

       

      I am working on a project where i would like to register projects, project members and the time they spend on each project.

      The work is well on its way thanks to this forum to some extent. I have however a problem i cannot solve yet.

       

      I have choosen to make two files out of it (red and blue) since the red one is expected to grow really large in time but is not as important as the blue one.

      In retrospect perhaps this was unnecessary but now it is done....

       

      ProjectTable --< ProjectMemberTable >-- MemberTable

       

      TimeTable  --< TimeProject >-- ProjectTable

       

      This allows me to choose several project members to each Project.

      The project members can then register their time for each project every day through the TimeTable.

      There are limited access built in which hinders the members from seeing each others times registered on each project and day.

       

      All links between the tables are with ID-numbers but in some cases I use get(AccountName) to get the right IDnumber.  

       

      In “TimeTable” is a portal, allowing the member to choose several projects each day to be registered.

      There is a drop-down that shows the projects. Today only the active projects are shown in the drop-down, the list is drawn from a field in the "ProjectTable".

      I would however want to restrict this even further.

       

      Problem:

      The only projects that should be shown on each members list is active projects AND projects of which they are members of.

       

      In the value lists i have two fields to choose from. Today the first one is set to the ID number which is stored but not shown.

      The second is from a calculated value of the active projects number and name  like this 

       

      Case(

                       project=active;

                       name + number;

                       "")

       

      I am not sure how i can implement the member part in this?

      Perhaps I could use the "ProjectMemberTable" instead since it is the only table containing members as well as projects?

       

      case(

                       Project=active AND get(AccountName)=CalculatedAccountnameDerivedfromStoredID;

                       name+number;

                       "")

       

      I have not managed to get it to work yet since some fields can not be indexed and i am unsure how this will behave if there are two or more persons registering time at once.

      Anyone know of a much better way to solve this or I am on the right track and just have to give it a couple of more days?

       

      Regards

       

      Paint

       

        • 1. Re: Lists from several fields
          Mystick

          You should do it with a new TO

           

           

          create a calculated field containing the current accountname

          create a global field containing 1 and call it "lnk_Active"   

           

          I suppose you already have an active boolean field  and a field to link the account to a project member.. maybe even a list of members...

           

           

          Create a new TO of table Projet and call it "Available projects"

           

          the link between Projet and Available projects would be     lnk_Active = Active  AND  AccountName = Member

           

          then you do your value list based on table Available project with option only data from relation to table projects.

          • 2. Re: Lists from several fields
            paint

            Thank you Mystick for the answer (i am sorry i have not replied sooner) I did not quite get it to work. Today however, i gave it a new shot and succeded.

             

            1. Created a calculation field in the members table where i listed only the active projects
            2. Created a calculation field in the TimeTable with the same content as above
            3. Created a relation between the list in #2 (TimeTable) and the Project table relating ID-numbers of the projects
            4. Made a value list from the relationship in #4 from the ID-numbers and selected "include only related starting from..:" and then set it to "TimeTable"

            It worked. Problems before was that i often got stuck in a situation where i tried to make a value list from a calculation field. I guess it pays off to sit down, think before you try.

            Thanks anyway.