Lookup fields - what am I doing wrong?

[Nicnacs]

Hello

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.

timesheets.zip

[-Queue-]

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.

[Nicnacs]

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!

Thanks

[Mike D.]

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

[-Queue-]

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.

[Nicnacs]

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.

[Nicnacs]

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!

[Nicnacs]

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:

WeekStaffID-1

and

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!

[Mike D.]

Nicnacs,

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.

[Søren Dyhr]

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.

--sd

Time2sheet.zip