Lists from several fields
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.
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
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?
Project=active AND get(AccountName)=CalculatedAccountnameDerivedfromStoredID;
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?