10 Replies Latest reply on May 31, 2013 8:56 AM by MarcoV

    Help with my value list

    MarcoV

      Hi I'm having problems with a value list. Here's the scenario: I've got 2 tables:

      Projects and Resources (Staff) which are related on Project No.

      A project can have numerous resources, these are added by using a User ID.

      (When the user logs onto the system their User ID is stored.)

      I have a Timesheet table which allows the current user to enter what projects they've been working on.

       

       

      Objective:

      I want a drop down list to show only Projects that have a Status = "Approved" and End Date < today and where the current user is a Resource. So the user can only select valid projects.

       

      Simple?

        • 1. Re: Help with my value list
          PeterWindle

          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?

          • 2. Re: Help with my value list
            mikebeargie

            There's two ways to get this value list, relationally, and a stored calculation.

             

            Relationally:

            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.

             

            -----------------

             

            Stored Calculation:

             

            Set a global field (might need two, one for the key, one for project name) to something like this:

            ExecuteSQL("

              SELECT key

              FROM Projects

              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.

            • 3. Re: Help with my value list
              MarcoV

              That's easy enough but the problem is also relating it to the Resources table where the User ID = Current User ID

              • 4. Re: Help with my value list
                PeterWindle

                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.

                • 5. Re: Help with my value list
                  MarcoV

                  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.

                  • 6. Re: Help with my value list
                    mikebeargie

                    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:

                    MarcoV

                    MikeBeargie

                    PeterWindle

                     

                    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.

                    • 7. Re: Help with my value list
                      MarcoV

                      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? 

                      • 8. Re: Help with my value list
                        MarcoV

                        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?

                        • 9. Re: Help with my value list
                          mikebeargie

                          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.

                          1 of 1 people found this helpful
                          • 10. Re: Help with my value list
                            MarcoV

                            Thanks Mike, really appreciate it.