10 Replies Latest reply on Jan 18, 2012 2:17 PM by danknight

    How do I lookup data in another table


      I'm very new to Filemaker Pro, coming from years in the Access world.

      I'm attempting to build a Timesheet database.

      I'm needing to lookup and then use certain values based on the user who logged in.


      The table structure includes a tblUsers, which includes fields for FullName (which will be used to match against the Get(AccountName) function), FName, LName, Hours and HourlyRate.

      What I'm attempting to do is, based on the LogIn (Get(AccountName)) I want to lookup the FName, LName, Hours & HourlyRate, so I can set the default for the Timesheet layout. This means the user will not have to a) repeat the process of selecting their name to create their Timesheet, b) enter their default working hours for each day, b) enter their default Hourly Rate.

      I simply want them to have to:

      1) LogIn

      2) Select the Period Ending date

      3) Make any changes to default hours

      4) Add any Expense claims

      5) Print the Timesheet.


      I've managed to successfully use the Get(AccountName) function to return the logged in user, but how do I use that to find the appropriate record in tblUsers and then return the appropriate data from the requested field?


      Thanks in advance.


      Dan Knight

        • 1. Re: How do I lookup data in another table

          You can use the security area to set up the access for the User so that he only sees his record. Using a script parameter to pass the user record ID. Once you have the ID in Timesheet, you can lookup the information. All of this can be done without the user being aware of it.


          There are so many ways of doing this solution. Using a portal in the user record to show the timesheet information, the user could enter all the information on that record.


          Something to keep in mind is that the ID number should be used to link the 2 tables not the name.

          I am sure this is redundant to you but ID numbers in FM are the basic links between tables.

          • 2. Re: How do I lookup data in another table

            Dan -


            Welcome to FileMaker. Presumably, you have a table of timesheet entries (where each record represents ... a week?), on which the user's name appears. If so, you can use the Lookup function in FileMaker to copy the values from tblUsers into that entry. Here's how:


            1) Make a relationship between tblUsers and tblTimesheet (or whatever the table is called), where UserName = UserName.

            2) In both tables, set up an auto-enter calculation where UserName = Get ( AccountName ).

            3) In tblTimesheet, set up a Lookup on the fields where you need to copy values from tblUsers.


            See the attached graphics for some screen shots. Let me know if you need additional help.








            • 3. Re: How do I lookup data in another table


              I'm obviously doing something wrong.

              I've setup the calc fields in both tblUsers and tblTimesheets, with the calc set to Get(AccountName)

              I've done the relationship, which creates a second tblTimesheet table, since there is already a relationship path via a tblTimeAllocation between tblTimesheet and tblUsers.

              I've created/modified calc fields in tblTimesheet for: UserFName (tblUsers::FName), UserFullName (tblUsers::FullName), UserHours (tblUsers::Hours).

              So far, I think I'm on track with your advice.


              But when I create a new record in my Timesheet layout, I notice no auto-entry data in those fields, which are controls on the layout.

              What does it take for the Get(AccountName) calculation to fire?

              Why does it need to be in the tblUsers, since won't it populate every record in tblUser with whoever the logged in user is?


              Dan Knight

              • 4. Re: How do I lookup data in another table


                I have the UserID as my key field that relates to the records in tblTimesheet and other related tables. But, I'm unsure how to track the UserID and use it since it is not a value from FMs security login.


                You stated that I can use FM Security to limit a user to seeing only their records; would you please elaborate on how that is done, since I can't see anything within the Security setup dialog that enables that. Plus, wouldn't that require setting up a UserName (aka AccountName) as the related field?


                I've read up on Script parameters, but seem to be missing something because I can't get FM to pass the parameter value to a custom dialog the way it can pass the Get(accountName) value.


                Also, from how I'm understanding the Lookup function, there is no option for setting a filter value (ie: Lookup tblUsers::Hours where tblUsers::UserID = $userID).


                These questions obviously reveal my FM rookie status; Everything we're discussing is relatively simple in Access (esp. since I know what I'm doing), so it has to be equally simple in FM, I just have to get past the learning curve. So thanks for your patience and assistance.


                Dan Knight

                • 5. Re: How do I lookup data in another table

                  Dan -


                  The FileMaker relationships graph doesn't show tables. It shows table occurrences. (Huh?) It can be useful to think of them as queries, but ... not really (because they don't create separate entities). They're like a separate layer sitting on top of the tables. Think of it this way: The graph represents both your relational joins and your querying structure, except it's used for filtering only when you go from TO to TO.


                  Okay, now that I've totally muddied the waters on THAT concept ... let's tackle the issue of why your calculation isn't working. Take a look at this relationships graph:




                  This should be conceptually similar to the situation you have - two TOs for a single table, with one of them passing through another occurrence. (I most likely have the joins wrong; ignore that for now.) Based on what you're telling me, you've made two mistakes: First, you've defined calculation fields instead of text or number fields with lookups. Those are two different things and will result in two very different results. Second, you've most likely pointed your calculations at the wrong TO.


                  Okay, so let's talk about what happens in the first case. If you define a calculation field instead of a lookup, what will happen is that the system will resolve the value on the record at runtime rather than storing it in the target table. (This is called an "unstored calculation" in FileMaker-speak.) The most important part is that the calculation's value will change if the data in the source table change - so you don't preserve the original value that was there when you created the timesheet (for example). Probably not what you want. But not exactly a huge issue.


                  The larger issue is the second point: That you're evaluating the calculation through the wrong relationship chain. This is something called "context", and it's very important to understand. Let's say my calculation is based on tblTimesheet (which yours probably is), and I tell FileMaker to copy the value from tblUsers. What will happen is FileMaker will evaluate the value in tblUsers after filtering it through tblTimeAllocation - which may result in a null set, if there is no matching record in tblTimeAllocation (for example). Think of it as a filter: Every step through the graph results in an implied found set - what you might think of as a query in Access land - and if there is a null found set, then no records will be found on the other side of that TO.


                  So what's the fix? Change your calculation so that it's based on the tblTimesheet 2 TO (in my example), and has a direct link to the tblUsers TO. You do this using the "Evaluate this calculation from the context of" checkbox in the upper left-hand corner of the calculation dialog:




                  By doing this, you tell FileMaker what chain through the graph to follow when making the calculation. The equivalent in the Lookup dialog is the pull-down menus that tell FileMaker what TO to copy the values to and from:




                  In this case, what you want is the "Starting with table" pull down. Make it come from your second TO for Timesheet and it should work fine.


                  FileMaker Mantra #101: Context is king.        





                  • 6. Re: How do I lookup data in another table


                    Thanks, believe it or not, that was helpful and not confusing, in part because it is similar to the relationships in Access.

                    I did have my calc fields set to the wrong context and they are now fixed. However, they are still not populating.

                    I am noticing that the tblUsers:UserName field, which is a calc field set to Get(AccountName) isn't populating either, which leads me to think that is why the others aren't populating because there is no value to link between tblTimesheet and tblusers.


                    Am I missing something else?



                    • 7. Re: How do I lookup data in another table

                      Dan -


                      You're correct on two counts. First, the fields aren't populating because there's no link between the tables, and second, it's because you're missing something.   


                      UserName should not be set to a calc field. Reason #1: If you set it to an unstored calculation, you won't be able to use it as the target of a relationship, because it can't be indexed. Reason #2: If you set it to a stored calculation, it will store the value that existed at the time you exited the Manage Database dialog ... which means it will store the value of the current user - you.


                      tblUsers is a domain table, used for reference, so it shouldn't be user-sensitive. Set the field to be a plain text field and enter the account name corresponding to the user in question. That should fix it.



                      • 8. Re: How do I lookup data in another table


                        Thanks. That is helping. I'm not fully understanding this yet, but it is working. In making the adjustment you suggested I also made a change to the relationship properties for tblTimeSheet2 that:

                        Allow creation of records in this table via this relationship

                        Delete related records in this table when a record is deleted in the other table.


                        I'm assuming this was necessary.



                        • 9. Re: How do I lookup data in another table

                          Dan -


                          Those two checkboxes are not necessarily necessary.  


                          It depends on what you're trying to do. The first ("Allow creation of records") will allow you to create records in a portal (I believe Access refers to this as a Subform?). So the user can simply start typing into a blank row in the portal and the appropriate key field will be automatically entered on the new child record. This may or may not be necessary, depending on how your interface is designed.


                          The second ("Delete related records") is for enforcing referential integrity. This will prevent the presence of orphaned records if you should delete the parent. In this example, when you delete a User record, checking this box will cause all related Timesheet records to be deleted. That may or may not be what you want (it usually is, but may not be if you want to preserve the history).


                          Neither of these should be necessary just to perform lookups. You can create related records via other means than portals (ex: scripting, subordinate windows, etc.), and referential integrity isn't at all required to perform a lookup.





                          • 10. Re: How do I lookup data in another table


                            Thanks again for the insight.

                            Yes portals and subforms are the same, and your explanation of the "allow creation of records" helps.

                            I was fairly certain that "delete related records" was about "referential integrity", and while not conceptually needed in my application because the plan is to retain history and simply mark a User as InActive, I believe having it set as a "back-up" is useful.


                            Dan Knight.