Relationship Query

[emncwundy]

I am trying to set up a time book database in FMP. It will replace the manual time book entries for payroll purposes. It will be fairly basic - ie start time, finish time, time for lunch. But I want to also group and be able to view records according to their Week Number (WeekOfYear).

So I created three tables: Employees, TimeCardWeek and TimeCardDays

I created a unique ID for the TimeCardWeek Table called __pkEmpIDYearWeek (which pulls the employee ID, the Year and Week Number: eg EmployeeID_2012_46).

I am having trouble getting my head around the relationship set up because I only want records from TimeCardDays to be related to the appropriate TimeCardWeek record.

I want to be able to enter records via a portal in the Employee records table but also from a dedicated TimeBookDay layout. On the TimeBookDay layout I also want to show a portal listing all the entries for that particular week. This i why i thought i needed the TimeBookWeek table - but now i am not so sure and i'm totally in a muddle.

Have i over complicated matters by creating the TimeBookWeek table which joins Employees and TimeBookDay records?

Please help.

[comment]

I don't think you need the Weeks table. To view (and/or create) the week's records, you can enter the year and the week into global fields, and use a relationship matching on EmployeeID and the globals.

[emncwundy]

thanks for your suggestions. I have been reworking the relationships and have deleted the weeks table.

still having some trouble getting the portal filters to work so that either just today or just this weeks records show.

 

Not sure why, but the year number calculation in the time card (child) records doesn't show up. the calcuation for week number in the parent record works but also not in the time card (child). Not sure what i'm doing wrong. Do you have any suggestions? I'm feeling a bit stumped as ive tried reworking the fields in various ways with no luck.  Thanks again for your help.

[comment]

Would something like this work for you?

TimecardsByWeek.fp7.zip

[emncwundy]

thank you so much. it seems so simple. sometimes i have trouble wrapping my head around the concepts!

 

the other thing i want to do is provide a screen on an ipad where an employee will enter a record for today only.

 

ie with buttons to automatically enter times for - start day, end day with or without .5hr lunch break in the middle. do i fliter the record with global fields also - ie so just a record for today shows - and somehow create a script to create a new record for each employee for each new workday? does this make any sense?

[comment]

Either one should work. It's really a matter of what's convenient for you and for your users.

[emncwundy]

I think my query is now a calculation based query... but not sure how to move it?

 

Thanks to your advice I have managed to create a time card entry layout for the ipad which shows an employees time for the week, but also has a portal with just one record for todays entry.

 

I want the global field g.WeekCommencing to automatically enter the week commencing as this current week.

 

I have tried a few things, but cant seem to make it work.

 

On my startup script - for when an ipad is detected -  the script navigates straight to the timecard layout.

 

i tried inserting a calculation line so that the global field would automatically set itself to this current week using the following:

 

Insert Cacluated result : Get(CurrentDate)

 

This didn't work, so I tried Set Field.  This doesn't seem to work either.

 

Can i change the calculation for the field g.WeekCommencing from

 

Self - Day of Week (Self) + 1

 

to somehow automatically go to this current week?

 

Many thanks in advance for any help!

[comment]

This didn't work, so I tried Set Field.  This doesn't seem to work either.

 

Why not?

TimecardsByWeek.fp7.zip

[emncwundy]

Hi there

 

I think i mustn't have explained my predicament clearly. When i re-created tables and fields exactly as per the sample database above - it works beautifully.

 

However this is relying on the employees seting the global field g.Week Commencing as this week. This seems to be way too much for them to comprehend. So I want to take the guess work out of it and have the field automatically setting itself to the current week. Is this possible? That way, the records that show in the filter will only be records entered for this week.

[comment]

So I want to take the guess work out of it and have the field automatically setting itself to the current week. Is this possible?

 

That is exactly what my last file does on opening.

[emncwundy]

I have tried this (see attached screen grab). But for some reason its still not automatically setting the date on entry.

 

Any ideas?

 

 

Startup Script.tiff

[emncwundy]

FYI the set field script does work in a script created when navigating from another layout (see attached screen grab) to the iPad layout (when on a mac). Just doesn't seem to work for the ipad. Could it be that FMGo operates differently?

Nav to iPad layout.tiff

[comment]

Could it be that FMGo operates differently?

 

I don't know. Why don't you do some error trapping to see where it breaks. Does the startup script run at all?

[emncwundy]

Thanks for your suggestion. Error trapping is a new concept for me. So I shall investigate!