Jump to content

Lookup fields - what am I doing wrong?

This topic is 6798 days old. Please don't post here. Open a new topic instead.

Recommended Posts


I'm developing a timesheet system and have it pretty much sorted (some teething troubles but help from the forums has mostly sorted out weird and wonderful problems!). However, I'm bemused by my lookup fields.

I have one database with 3 tables: staff data, time data and weekly summaries.

Staff data holds people's names, how many hours they're supposed to work each week (as we have some part time staff), how many days annual leave they get, etc.

Time data holds the actual data for each day - it's actually viewed through Staff Data which is where people enter information through a portal.

Weekly summaries - this exports the information at the end of each week so that managers and trustees can quickly look at who's overworking or not taking enough holiday etc. Weekly summaries has a field that calculates a running total for overtime worked (TOIL) and a calculation field that works out how much annual leave you have left.

For example:

Week number Hours Worked TOIL Annual Leave taken this week

23 37 2 0

24 40 5 0

25 35 0 2

Running TOIL 7 Annual leave left 23

This all works fine for Weekly Summaries. The calculations work fine and as weeks get added, the running totals update.

What I need to do is then take the Running totals back to the Staff Data so that staff are aware how much TOIL or Annual Leave they have without having to check their weekly summaries.

So I thought I'd use a lookup. Each member of staff has a unique ID and each week is uniquely numbered. So I created a field that joins the two using a calculation to create a unique reference number for each week for each person.

I then created the same field in Staff Records and defined the relationship StaffIDWeekNumber in Staff Records = StaffIDWeekNumber in Weekly Summaries.

Then I defined two fields in Staff Records to look up the TOIL and Annual Leave from Weekly Summaries based on StaffIDWeekNumber.

But it isn't working. It's looking up a value, but not the right one and I can't work out where it's getting its value from.


Link to comment
Share on other sites

Lookups only update when their key fields are updated or a Relookup is performed. So you either would have to run a Relookup Field Contents on StaffData::WeekStaffIDLessOne or, if you want the data to be instantly updated, make it a calculation instead of a lookup.

Link to comment
Share on other sites

Yes but when I do do a relookup (choosing Records/Relookup field contents) it looksup but 'copies' the wrong information. It's very strange. So I go to the field upon which the lookup is based, click on Records and choose relookup field contents, off it goes and.....wrong stuff!

I've checked my relationship and the fields it's supposed to be copying from again and again and I can't see what's wrong.

S'bugging me!


Link to comment
Share on other sites

I downloaded your file and from what I see, you have a couple problems.

1. Your relationship between Staff Data and Weekly Summaries uses a calculation field in Staff Data that cannot be indexed. Change the StaffIDWeekNumber and StaffIDWeekNumberLessOne from calculation fields to Auto-Entry using the same calculations. Now the fields can be indexed. (FM7 doesn't warn you about this like the earlier versions do.)

2. The Annual Leave Taken field in Weekly Summaries should be populated by the Count Annual Leave data from the TimeData table. One problem to watch out for in your current design is that as you go from this year to the next, your totals will carry over.

HTH, Mike

Link to comment
Share on other sites

Regarding Mike's point 1: the relationship between StaffData and Weekly Summaries 2 should be fine, since information is being pulled into StaffData from Weekly Summaries 2, and not the other way around. The lookups that are being done in Weekly Summaries are based on that TO itself (which uses a simple StaffID relationship), not Weekly Summaries 2, as is the Weekly Summaries layout.

You can set your relookups in StaffData to use the blank option if there is no exact match. Then perform a Relookup in your WeekStaffIDLessOne field.

Link to comment
Share on other sites

Thanks guys. So queue are you saying that my lookups should work? I think you are so I'll give your suggestion of the blank option and relookup thing and see if that works. It's always a bit confusing when you think you've set up something correctly and can't understand why it just doesn't work. I guess you get to learn these little tricks with experience.

Link to comment
Share on other sites

Hmmm....I think I've figured out what's going on. The question is can I explain it! I now have two weekly summaries - one for me and one for a colleague. I have 3.25 hours of TOIL, my colleague has 1.5 hours.

If I do a lookup using WeekStaffIDLessOne field, it brings across the TOIL for both of us. So it brings across 5.75.

Is this something to do with how Running TOIL is defined in Weekly Summaries? Is it because it's a summary field?

I'll try the auto-entry option as Mike suggested and see what that throws up!

Link to comment
Share on other sites

Now I can't get that to work either! The WeekStaffIDLessOne field doesn't work. It's just blank. I've tried defining it two ways:



Staff ID&WeekNumber-1

Neither work. I've also tried unticking the do not replace value...and the do not evaluate if empty.

So that's not an option. I've got around it by having a seperate script that find the person's weekly summaries, copies the TOIL and then pastes it, but it's not very satisfying!

Link to comment
Share on other sites


When you change the WeekStaffID and WeekStaffIDLessOne fields from Calculate to Auto-Entry fields, all previous data in those fields will go away. To resolve that, click in the WeekStaffID field and do a "Replace Field Contents" with your calculation. Then do the same in the WeekStaffIDLessOne field. After that, all new entries in those fields will come up as expected.

Link to comment
Share on other sites

I toyed with your Timesheet, because Lookups as such makes storage of redundant data, and the data very well instead could use the tunneling features Comment stumbled over some month ago.

What I do here, is suggesting a structure exploiting the tools in the relations the way they're implemented in Filemaker ...not that your solution is wrong in any way - But looking at different approaches to the same problem is very educating!!!!

This means that only one Summary field is needed in the related and various multicriteria relations takes care of the required filtering.

But also because the need for fields required to perform the same task are cut to the half, however is it a little bit more requirering of the developer, to make all the substantial relations criterias ...which might be because I havn't seen the light how to overlay an extra criteria to a enherited structure??

What I dislike with my solution is the use of flags, but thats more because earlier tuition in c++ have discouraged globals as well as flags ...what doesn't have to be so with filemaker.



Link to comment
Share on other sites

This topic is 6798 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.