Simple enough if you are storing the User ID in a global, then you can create a relationship using multiple values.
in one table use a global containing the current user, a field with the text value "Approved" (global or calc) and a field storing the current date (global is preferrable). Creating a relatioship using these fields to the matching fields in the Projects table, you can then create a value list using related values from the source table, showing the related values from Projects.
Does this make sense?
There's two ways to get this value list, relationally, and a stored calculation.
Create global fields on the timesheet table:
global_status - on open of file, set this field to "Approved"
global_date - on open of file, set this field to Get(CurrentDate)
global_resource - on open of file, set this field to Get(AccountName)
Then relate those fields to an instance of the Projects table, and make your "related" value list based on that table.
Set a global field (might need two, one for the key, one for project name) to something like this:
WHERE status = 'approved'
AND theDate < ?
AND resource = ?
" ; "" ; "" ; Get(CurrentDate) ; Get(AccountName)
Note I said "theDate" since "date" is a reserved word.
Once you set those global fields, you create the drop down menu based on those fields. Of course you will also need to refresh the values occasionally with new set field triggers.
That's easy enough but the problem is also relating it to the Resources table where the User ID = Current User ID
If the Current User ID is a global, then you can create the other globals in the Resources table, related these to the Projects table and then use this relationship for the value list.
Thanks for your reply Mike. I'd like to go the relationship way if possible. In your relationship approach, I can't see how your solution can work when the resource is not stored in the Projects table (there could be many). I'm looking at 2 tables for the criteria. I have tried adding the 'End Date' and 'Status' fields to Resources table from Projects, but can't use a calculation field for relationships. Also, if I have these 2 fields as fixed text fields then this causes problems as the Status can change.
You have to store the resources somehow in the projects table. Assuming you have a many-to-many relationship table between projects and resources, you would simply calculate a field in projects that has a list of resource names.
IE: Projects::calc_resources = List(Resources::accountName)
This would calculate something like:
assuming those three people had records in the intermediary table to tie them from Projects to Resources.
Then when you relate to the projects table based on global_resource, if the value from global_resource exists in the list of values in Projects::calc_resources, it will relate. You can relate to a stored list of values, it will compare to see if the value exists within the list of values.
So I've added a new text field in Projects called Resources that is a calculation = List(Resources::User Name). I've then updated all records with this calculation. I've then made a relationship to Projects from a table I've called Session which simply stores the gUserName, gCurrentDate, gStatus = 'Approved' (all Global variables). The relationship is as follows: Status = gStatus, End Date < gCurrentDate and Resources = gUserName. In the Timesheets field I use a drop down list which looks up the Project Number and title from Projects table, but Includes only related vaslues starting from: my new relationship.
This produces no records in the drop down.
If this was to work, what happens when new resources get added, how/when will I need to update the Projects::Resources field?
Mike it does work! Thanks, the List() field really helped. What happens now when the resources change when/how do I update the Projects::Resources field?
1 of 1 people found this helpful
well, you could set that List() field as an unstored calc with "index as needed" checked off. Else, I believe if you set it as stored, with an auto-enter calculation, and uncheck the "do not replace existing value, if any" setting, that should also get you what you need.
PS - make sure to mark helpful answers when you close questions.
Thanks Mike, really appreciate it.