9 Replies Latest reply on Mar 14, 2014 10:53 AM by philmodjunk

    Conditional Lookup with global field

    rt314@icloud.com

      Title

      Conditional Lookup with global field

      Post

           I’m currently creating a database in FMP13 for our staff to input time records that essentially store the employee, date, client, project, and the amount of hours worked. For each Time Record entry, I want the employee to select the client first, then the list of projects available under that client. So my tables are currently that Client is one-to-many to Projects, which is one-to-many to Time Records. The only way I have been able to allow selection of Client first is to create global field in the Time Record table, then relate a new instance of Time Record to the field “ClientIDforeignkey” in the Project table. This allows me to select from all available clients listed in the Project table (in the global field from Time Records 2). Then when I select the appropriate project, the client information is filled into the rest of the fields. It seems that there should be a better way of doing this, but I cannot find one. All the forum suggestions I’ve seen either don’t seem to apply or have an example file that I cannot download. Please suggest better ways!

           If there is not a better way, I am running into layout trouble in a different layout. I have a layout listing all the time records associated with an employee over a specified time period. I want this to be the main interface for the employees to add time records. But as you can imagine, putting the global field in here has the possibility to goof up the look and functionability of entering data because all of the project fields are based on one global field. Any suggestions here? Thanks and let me know if you need clarification!

        • 1. Re: Conditional Lookup with global field
          philmodjunk

               Can you explain why you need to use a global field for this? Why can't you use a non global field?

               Can you upload a screen shot of Manage | Database | Relationship (with the match fields visible) to show the relationships that you have set up?

          • 2. Re: Conditional Lookup with global field
            rt314@icloud.com

                 No, I can't really explain why I need a global field other than that it seems to be redundant if I save client information into the Time Record table that is already in the Client table. I am learning databasing as I'm learning Filemaker Pro and am trying to make sure the database is most efficient. the jpeg should be attached.

            • 3. Re: Conditional Lookup with global field
              philmodjunk

                   If you are setting up a conditional value list of Projects for a selected Client on your TImeRecords layout, I'd define the global field for ClientID in the TimeRecords table so that I can match an occurrence of Projects directly to the table occurrence selected for the TimeRecords layout.

                   But there shouldn't be the layout problem you imagine with your second layout. Note that you have a chain of "many to one" relationships:

                   TimeRecords>-----Projects>----Clients.

                   Thus, if you create a layout based on TimeRecords to list all the TimeRecords for a given employee, you can include fields from both Projects and Clients in this layout.

                   If you set up a portal to TimeRecords on an Employee layout for this purpose, the same holds true. Fields from Projects and Clients can be placed in the portal row and they should show the correct data for that Time Record.

                   Employees-----Portal----<TimeRecords>------Projects>-----Clients

              • 4. Re: Conditional Lookup with global field
                rt314@icloud.com

                     PhilModJunk, Thank you so much for your time on this! Just a couple more clarifications:

                     If I am understanding you correctly, I believe I am already doing what you said in your first paragraph. 

                     As for the formatting in the TimeRecords-based entry layout (see attachment below) there should theoretically be no issues, but there are a few that I run into, particularly because they are confusing to the user:

                     1. If I put the global field in the header, it's out of place and still messes with the 'project pop-up' field (see #2). If I put it in each line, the global field will give the same client on all lines rather than matching with each actual client association. 

                     2. The 'project pop-up' field that has the conditional choice set up formats differently depending on whether the global client field is associated with it or not. This is not a problem until I try to cover this field with the 'project name' field for uniformity sake. I have not found a way that will either may 'project name' opaque and cover everything but the arrow option in 'project pop-up' field, or write a script to refresh and use the hidden option where the 'project pop-up' field can both be hidden/shown as well as still work as a pop-up. It seems to not let me interact with it when it is shown if it has a conditional hidden tied to it.

                     If you have any suggestions, they would be most appreciated. Otherwise, I have spent more than enough time on this and it would be good to know that what I'm trying to do is not possible and just figure out the next best thing! This is something else I've searched for on the forum and never found a definitive answer. I am attaching the screenshot that will hopefully give you a better idea. Thanks again for your time!

                      

                • 5. Re: Conditional Lookup with global field
                  philmodjunk
                       

                            If I am understanding you correctly, I believe I am already doing what you said in your first paragraph.

                       Not by what I see in your screen shot of your relationships. There's no global field in TimeRecords to match to records in other tables such as Projects. You have a global field in a globals table--a good idea, but can't be used if you need to use the global field as a match field in relationships.

                  • 6. Re: Conditional Lookup with global field
                    rt314@icloud.com

                         The gClient field in TimeRecords2 (a second instance of TimeRecords table) is a global field (coming down and to the right of the Projects table). This field is related to all the clientIDforeignkeys that are associated with projects in the Projects table. So essentially, it chooses from the client IDs that are matched with a project. Then once the gClient (from TimeRecords2) is selected in the TimeRecords layout, only the associated projects are available in the projects field.

                    • 7. Re: Conditional Lookup with global field
                      philmodjunk

                           Yep, sorry for missing that detail.

                           

                                1. If I put the global field in the header, it's out of place and still messes with the 'project pop-up' field (see #2). If I put it in each line, the global field will give the same client on all lines rather than matching with each actual client association. 

                           But you can add the ClientName field from Clients on each line if your layout is based on TimeRecords and not TimeRecords 2. If you want to use TimeRecords 2, you can also do this if you add in more occurrences of Projects and Clients linked to it and then select ClientName from this new occurrence. I don't see any need to use the global field here.

                           ExecuteSQL could also be used to access the Client Name on each row

                           

                                2. The 'project pop-up' field that has the conditional choice set up formats differently depending on whether the global client field is associated with it or not. This is not a problem until I try to cover this field with the 'project name' field for uniformity sake. I have not found a way that will either may 'project name' opaque and cover everything but the arrow option in 'project pop-up' field, or write a script to refresh and use the hidden option where the 'project pop-up' field can both be hidden/shown as well as still work as a pop-up. It seems to not let me interact with it when it is shown if it has a conditional hidden tied to it.

                           "Conditional Choice to setup formats" means what exactly?

                           I don't get why the gClientID field--a field for selecting a client would be covered by a field for the project name. I can see a rationale for covering the field with a field for the client name in order to hide the the ID number, but not by covering it with the project name.

                           And does your layout intended to show only time records for a given project--in which case there is no need to show the client in every row nor should there be an issue with adding the ClientName from Clients to the header a Time Records 2 table occurrence, or is it used to list time records for multiple projects/clients? (such as listing all the time records for a specific employee.)

                           If you format the

                      • 8. Re: Conditional Lookup with global field
                        rt314@icloud.com
                             

                        But you can add the ClientName field from Clients on each line

                             I just realized, I don’t think the screenshot is wide enough. I moved fields around to clarify on the screenshot and I do have the ClientName field on the line, my apologies.

                             The use of the global field is the only way I could first select a client (in the global field), and then be able to choose from the projects associated with this client. I could not think of any other way to create a field in TimeRecord table that did not duplicate information stored in either Projects or Client tables. And if I understand the relationship aspect of databases correctly, I cannot have a field in TimeRecord that first references Client (bypassing Projects) that then comes back to Projects (in an attempt to first choose the client, then the project based on the client I chose).

                        "Conditional Choice to setup formats" means what exactly?

                             This was poor wording…why I studied math. I was trying to describe that if I include the global field in the line, it replicates the same thing for all lines (since the global field only shows the locally stored info). I think what I’m going to do is cover the global field with the ClientName field (but make the ClientName uneditable) and write a script that forces the user to Project selection upon choosing the Client from the global field (the pop-up will be visible for selecting). This would update the ClientName field and hopefully not be confusing to the user. I am covering the Project Pop-up field with the ProjectName field to hide the unique key from appearing so that it is not confusing either. I also found that if I make the text of the pop-up white, I can still see the options when I select the pop-up, but the pop-up text will not be seen when the field on top of it is showing.

                             

                        And does your layout intended to show only time records for a given project

                             The common theme for the layout is the employee. There may be multiple clients and projects, but this page is for one employee. I believe that is why I need to list the project and client for each line. I have a script that uses the login account to populate the employee name and filter

                              

                             I didn’t fully understand what I was doing before and I thought there was a better way of doing it, but you have helped clarify a lot and unless you have other suggestions, I don’t know that there is a significantly better way. Please let me know if you see anything else, and thank you again!

                             

                                   

                              

                        • 9. Re: Conditional Lookup with global field
                          philmodjunk

                               I still don't understand why there is a problem nor why you want to "hide" that global field--which should not be placed in the body of your layout at all. It should be left in the Header and then solely for the purpose of selecting a client so that you can limit the time records shown to a specific client or project.

                               Base your layout on TimeRecords, not TimeRecords 2 and you can perform a find (either manually or from a script) for TimeRecords for a specified employee. This can be for all time records for that employee or additional criteria can filter things down to a specified date range and/or specific projects or clients.

                               As long as the layout is based on TimeRecords, you can include any fields that you need from Projects and Clients and they will display the correct information. FileMaker will "tunnel" through Project to access data in Clients given the relationships shown in your relationships graph.

                               For examples of scripted finds, see: Scripted Find Examples