madman411

This is true, now you mention it. If you have a [generous] moment to demonstrate how you would use another table to split the records I'd be very grateful. The way the existing setup is created, filemaker is seeing it as multiple fields inside of one record. 194 records x 26 fields.

Thanks Comment. The overtime schedule isn't something I have control over. It's maintained by someone else who tracks the rules and contract terms and is provided to me as/when changes to the overtime schedule are made (i.e. labor law is changed or a contract is changed). I can put a request in to see if the person who maintains this is able to adjust the formatting. I believe it was originally exported from the system they were previously using. I was hoping to use it as-is. FWIW, "E" refers to elapsed. "W" refers to worked. This is likely used in determining monetary payout. Per my supervisor: "E" Elapsed - means "elapsed hours, pay out golden time before deducting meal period" and "W" Worked - means "worked hours, pay out golden time after deducting meal period." It is my understanding that "golden time" refers to a provision where after 16 hours employees are paid at the full day rate per hour beyond hour 16. "Sch 1 to 5 xx" would be day 1 to 5, "6Dxx" would be 6th day worked, "7Dxx" would be 7th day worked. This is my first attempt to integrate this into a FM solution to "automate" it. Your assumption is correct in that you would use Wrap - Call + M1Out - M1In + M2Out - M2In to determine hours worked minus breaks. Most (but not all) schedules break that down to 8 hours (straight) 4 hours (time and one half) and double after 12, but as you can ascertain from the spreadsheet it does indeed vary based on the contract and/or if the work week exceeds 5 days. Your second step indeed calculates correctly, but the overFrom, doubleFrom, tripleFrom, etc would need to extract the correct value from the appropriate schedule I appreciate your help however. I need to dig deeper into how the previous guy was using this data for calculations.

@comment thanks for replying! I have included an example below. Shown in list view you'll see a few example records. Under 1x, 1.5x and 2x you'll see two rows of fields. The first row uses a "threshold" which are designated in the header. I'd like to remove this manual process and utilize the imported overtime schedule to break the hours down. The second row of fields is my attempt at testing the calculations. And you're correct - if the rules change ideally older time entry records would remain unaffected. hours_testing demo.fmp12

Hi all I've inherited a project that I'm struggling to implement into a solution I've been asked to work on to see if FileMaker is an appropriate solution. Currently I'm working on a basic timecard system that uses a series of overtime schedules to break down hours worked into straight time, time and one half, double time, etc. Currently this is not calculating pay, but rather separating the hours worked during the day into their respective threshold categories. To begin with I've created a database and table called "hours testing" and have the following fields: schedule name - day number (1-7) - date - call - meal 1 out - meal 1 in - meal 2 out - meal 2 in - wrap - hours - 1x - 1.5x - 2x - just to create some test records and test some calculations. A second table (joined via "schedule name") has the 'overtime schedules' which was an excel file given to me as the base framework of how the company wants to calculate time thresholds. It's packed with quite a bit of data which I'm told was a timely process to assemble, so I would ideally like to leave it as-is. In the 'overtime schedules' table I have created fields based off of the column headers in the excel file and imported the excel file. Within the "hours testing" table I want to be able to create records, with time entered as tenths, and have the 1x, 1.5x and 2x fields calculate based on what respective schedule is chosen. The caveat here is that some values within the excel file use "e" and "w" (elapsed and worked) to determine when the threshold changes. According to my superior, "e" elapsed means "elapsed hours, pay out golden time before deducting meal period" and "w" worked means "worked ours, pay out golden time after deducting meal period." It is my understanding that "golden time" refers to a provision where after 16 hours employees are paid at the full day rate per hour beyond hour 16. I have successfully created the sample file and can add work hours and it calculate the hours worked, minus breaks, but I need assistance with creating the calculations for the 1x, 1.5x and 2x breakdowns. Further breakdowns like 3x and 3.75x are going to be a later addition. These schedules account for union contracts and different labor laws. I need this to work before presenting the example. As basic hour entry is, well, basic, I'm attaching just the excel spreadsheet that was given to me in hopes someone more versed could assist with the calculation breakdowns. In reference to the excel file, "Sch 1 to 5" refers to the first 5 working days ('day number'). "Sch 6D" refers to 6th day worked (different overtime provisions) and the same for "7D" (7th day), also with different overtime provisions. Any assistance or guidance is appreciated. Untitled.xlsx

Hi all Firstly, apologies if this is in the wrong sub-forum. I wasn't able to determine which part of the forum this belonged. I have a small company that has a couple different departments within it. I'm wanting to create a system that allows employees to enter their worked hours each day prior to payroll processing their timecards separately. This is a request from management. No taxes and such are calculated within this, this is more of a daily/weekly review to track overtime and hours worked. They call them "production reports". They use these when approving or flagging payroll timecards. Ideally: - each user login has their own "profile" layout where they can see their department and create their timecard entries for review. - each user can only access/create their own respective timecard records. - department managers can see their own timecards and the timecards of people within their department. The manager can also edit them if it's necessary. - the administrator can select/view/modify all records and grant users managerial access rights, add new staff, generate reports for payroll, etc. I have never worked with user-specific records before, but have limited experience with access rights. Typically my databases that have been deployed have about two users on average, so this scale of a solution is new to me. Basically my question here is what is the best way to structure such a database? 1) a table that contains users 2) a table that contains departments 3) a table that contains work weeks 4) a table that contains daily timecard entries What is a recommended way to dynamically adjust what records are available to what user? For example, a day-to-day employee is promoted to manager and needs their privilege set changing. Is a hidden field within the record that contains the employee and manager's unique ID an option so FileMaker can permit or deny access accordingly? Some employees are affiliated with a union, so their respective overtime schedule needs to be applicable, but I believe I can achieve this through calculations. Initially, buttoned up security isn't a huge concern, but obviously I would like to build this database with it in mind once it's approved and deployed. I'm also told this may be moved to a web-based service rather than their internal FileMaker system. Thank you!

I'm confused. There is a related record as it's getting the date block from it that's displaying on the repeating field. Perhaps a relationship issue?

Hi @comment I believe you actually helped me way back figure the initial structure of a similar project. Your calculation suggestion just returns a ? so I'm assuming the record number and repetition number isn't matching properly. In the context of the linked thread, the asset (in present case) is then displayed in a list view and the summary field is next to it. Any note in the asset booking record should be shown in a tooltip when hovering over the summary field's respective repetitions.

Hi all I have created an employee vacation scheduler that is basically gantt charts using a combination of repeating fields. On the employee record I can add related records (timeOff), set the dates, approval status, approved by and a note. This repeating field (cChart) is then recalculated into a summary field (sChart, also repeating) that shows the given date range (31 repetitions) for the employee. Numerical values complete the repeating fields. 0 is no data, 1 is approved, 2 is rejected and 3 is pending. On another layout I have the Employee table in List view and each employee has the sChart repeating field shown in a matrix so the user can see the specified date range and when and where vacation time might overlap or coincide with other employees that might have vacation scheduled. This works fine but I would like to add some additional functionality for an "at a glance" option. I would like to allow the user to hover their cursor over the vacation blocks displayed and in the tool tip show the status, who approved, and the short note the approver entered, if any. I have it semi-working, however if the employee has multiple vacation day(s) on the matrix, the tooltip only seems to show the note from the first related record. Conditional formatting or calculations based off of the numerical data (i.e., approved, rejected) all displays properly. Any tips or advice?

Days table is used to generate statistics about the work day - who worked most, how many crew worked, filming location for that day, if mileage was applicable, etc. EmployeeID field was used to generate a list of IDs for another feature I worked on. Your suggestion did work though. I was overcomplicating the way I was trying to do it before. Thank you!

Hi all I've written a small database that calculates crew hours in the production and entertainment industry. I've been asked to "flag" employees automatically that have turnaround violations (i.e. they weren't given enough time off between finishing the previous day and starting on the current day as stipulated by their particular union contract.) The goal being to list the names each day so the department heads have an idea who are accruing these rest period violations. I currently have a "day" table where the work date is entered and each employee working is added to a portal and their hours are entered ("DailyHours"). These are further broken down into weeks, where a production report can be generated. I'm wanting to use a hidden field to display their wrap time in DailyHours ("tenth_out") from the previous day, if they worked. Using this I can then calculate if enough time was given between wrapping on the previous calendar day and starting again on the current day. Hours are entered as tenths of an hour and can (and do) go over hour 24, so 1am would be 25, etc. if they worked that late. Unfortunately, it's not possible to guarantee the users are entering data in sequential order, meaning they could go back and adjust times later, or add days that were missed after the fact. I'm wondering what the best method is to calculate this, whether it be specifically referencing the employee ID number and work date - perhaps using SQL? The important fields in this scenario would be: 1) Days::Date and Days::EmployeeIDs 2) DailyHours::Date, DailyHours::EmployeeID, DailyHours::Tenth_In and DailyHours::Tenth_Out Thank you

Thank you, comment. That worked.

Okay, another question. I am filtering my portal via a relationship using a popup menu to filter by departments (no direct filter on portal). This works fantastic so far, however I would like to be able to include an option to "show all" for select users. I've tried modifying the department table to include a "show all" record that features a "*" in the primary key field, but an asterisk doesn't appear to work like it does in Find Mode to show all records. Is this just not possible using a Popup Menu? I may have to resort to filtering directly in the portal but I wanted to ask first. Thanks

@Fitch - I managed to find a solution that is working by using the following calculation IsEmpty ( FilterValues ( List ( Days::sCrewOccurence ) ; PR_CREW::PrimaryKey ) ) I do understand that filtering within the portal isn't the best method, but for now at least I have a working model. I will go back and try to recreate your method in another dummy file and amend as needed.

Hi all. I am attempting to filter a portal in a few different ways. I have a daily time sheet displayed in a portal that is department specific. Table: Day Portal Table: DailyTimes (Day::ID = DailyTimes::DayID) Each work day the user opens the current day record and can open a card window and be shown another portal with applicable crew who are in the system that could be working that day. Table: Day Portal Table: PR_Crew (another instance of the "Crew" table). Not every crew member works every day. Currently I have it scripted where the user selects a checkbox next to the names and the script loops through and adds the crew to the daily time sheet for that day. This works correctly. The PR_Crew portal is currently filtered by department "sDepartment" pop-up menu. Most users generally cannot change this as it's dependent on which department they work in, but for the sake of this post the department can be changed using the popup menu. Case ( IsEmpty ( Days::sDepartment ) ; PR_CREW::Department <> Days::sDepartment ; PR_CREW::Department = Days::sDepartment ) I need to retain this popup filter and filter out any crew that's already been added to the DailyTimes table for that day so a duplicate entry cannot be made for the same person. I have created an additional field in the Crew table to list which days they already have records in, but I'm going in circles when it comes to calculating the filter. I'm either getting all crew member names listed in PR_Crew or none.

I appreciate your encouragement. Meal Penalty 1 was easy to decipher, but I'm struggling with MP2 because it is more complex and covers the entire work day rather than just the first half. If I had a question for your area of expertise it would be how to eliminate the need for assigning each penalty individually within the calculation - rather than a long Case () function checking if equations return >6, >6.4, >6.9, etc, how would it be possible to calculate the results through a formula that determines that result criteria automatically for anything over 6 hours, and the respective violation times thereafter? Each penalty begins at the 6 hour mark, another at minute 15, another at minute 30, another at minute 45 and another at the top of the hour, and continues. However, we enter times as tenths of an hour. In the event of a day without meal breaks, I'd have to write a Case function that acknowledges 24+ different equations (assuming we only work 12 hours... sometimes it can be much longer). There must be a more simple way for the computer to determine these.

@comment - sorry, sometimes I still get confused with how they calculate all of this stuff. On union contracts there are penalties that are in place for the employer if they don't provide a break every six hours. Despite "clicks" (tenths of an hour) being standard for time reporting, these are usually calculated in 15 minute increments and have different fees tied to them, i.e. after six hours worked with no break, you'll accrue one penalty. Once hour 6.4 hits another penalty will be added if still not broken for lunch (so two meal penalties at this point), and the cycle continues until a break. This would be Meal Penalty 1. The same is applicable on the back end. So after your first meal break, penalties will occur once the 6 hour mark has surpassed again and you've not been dismissed for the day or a second meal has been provided. This would be Meal Penalty 2, and so on. Sometimes you aren't broken for a break at all, so a meal violation begins from hour six until you are dismissed for the day. If you work a 12 hour day without breaks then 24 meal penalties will be paid to the employee for the day.

Hi All. Attempting to implement an Excel spreadsheet I have been using into a newly designed FileMaker database, but the Excel sheet isn't without limitations. The attached Excel spreadsheet file was provided to me to help start the process. The attached file is a timecard, however what I'm doing doesn't require calculation of wages - just tracking departmental daily film industry hours worked in "clicks" (tenth of an hour, military time) so we can submit the report for our department to our manager at the end of each day. I need help repurposing the calculation for meal penalties, particularly Union meal penalties. Please see Excel formula below exampling the calculation for Meal Penalty 2 on the 1st Day: =IF(AC11="W",0,IF(IF(ISNUMBER(AY11),BE11-AY11,IF(ISNUMBER(AS11),AS11-AM11,IF(ISNUMBER(AM11),BE11-AM11,BE11-AC11)))>6.9,3,IF(IF(ISNUMBER(AY11),BE11-AY11,IF(ISNUMBER(AS11),AS11-AM11,IF(ISNUMBER(AM11),BE11-AM11,BE11-AC11)))>6.4,2,IF(IF(ISNUMBER(AY11),BE11-AY11,IF(ISNUMBER(AS11),AS11-AM11,IF(ISNUMBER(AM11),BE11-AM11,BE11-AC11)))>6,1,0)))) In the Excel spreadsheet, under the UnionTC tab, on the "MEAL PNLTY" column, you'll see each day has two cells calculating meal penalty violations. I'm struggling converting Meal Penalty 2, or the lower cell of the two cells. These occur twice in a 30 minute period where a break hasn't been given within a period of 6 hours or less. I also see in this example that the calculation only factors up to 6.9 hours (or three penalties) where it would be great to also calculate penalties accrued > 7.0 hours past call time or the previous meal penalty ending. Although it's shown on the spreadsheet, below you'll find what each cell number in the formula is referencing: AY11 - Meal 2 In BE11 - Wrap (end time) AS11 - Meal 2 Out AM11 - Meal 1 In AC11 - Call (start time) I managed to convert Meal Penalty 1 using Case () and it seems to be working correctly, but Meal Penalty 2 is causing me some major headaches. In a nutshell, there are union regulations that stipulate a meal break must be given within six hours of Call and/or within 6 hours of the previous meal break. Sometimes jobs do "french hours" in which case no official break is given, and Meal Penalty 2 needs to calculate how many violations have accrued from Call time to Wrap time in the absence of meal breaks being entered. Any help appreciated EP basic forms.xlsx

Hi all. Attempting to sum hours worked using SQL where a separate field matches a particular value in another field. The calculation is determined from the "Tax Year" table. The calculation references a child table where the job entries are created - Relationship is: Tax Year::YEAR = Job Lines::YEARid ExecuteSQL ( "SELECT Sum (HRS TOTAL) FROM \"Job Lines\" WHERE IA = 'Y' YEARid = ? " ; "" ; "" ; "" ; "" ; TAX YEAR::PrimaryKey ) I keep getting a ? as a result. My suspicions are somewhere in the last three lines of the calculation. Any ideas?

Worked a treat. I had the relationship backwards. Thanks again, comment.

Hi comment. Sorry about that. I've tried to strip down and simplify an existing database that contains personal information. I've uploaded a new version of the file with more accurate naming. In the original file, which is aimed at independent contractors, the Tester table is a "tax year" table. "Payment entries" tracks payments made by payroll companies which include a check date. There are multiple payroll companies and some repeat. So if 2022 is the record in the "tax year" table, all payment entries for the 2022 calendar year appear in portal "1". The purpose of portal "3" is to assist in unemployment applications. The unemployment agency requires some specified date ranges where the amount made by each payroll company needs to be entered. In this example, EDD and CAPS may have paid multiple times over the course of 12 months, however the user might be asked to provide the total payments received within a specified date range, whether it's 2, 3 or 6 months. Instead of having a portal filled with EDD and CAPS payments, my goal is to have one EDD record show and one CAPS record show with a sum of the total payments within the range. I thought summing via the payroll company table behind the relationship would achieve this, and it does in the sense that it shows one instance of the payroll company that falls within that date range, but the sum calculation field doesn't seem to consider the date filter. testerV2.fmp12

Hi all. I believe this has a simple solution, but I've been unsuccessful. I have a table of records (table "b" - "Entries") shown in a portal on table "a" ("Tester"). Table "C" shows the occurrences without duplicates. These records have two values that need to be "summarized" within the portal. Furthermore, in table "a" I have two global fields, "start date" and "end date." To further breakdown the related records I have a relationship filter: "start date <= check date" and "end date >= check date". I am able to get the portal to show each instance of the related record within the date range, however I want one instance of the related record to show with a summed total from within the date range. In the attached file, portal 2 should show all records that fall within the date range. This is accurate. In the attached file, portal 3 should show all records that fall within the date range, without duplicates, however, while the occurrence names are correct, the sum doesn't account for the date range. Instead it is summing all records regardless of the specified date range. EDD should be 150. CAPS should be 25. Can someone tell me where I'm going wrong? tester.fmp12

Hi all I am trying to utilize ExecuteSQL to calculate earnings per quarter last year, however it doesn't appear to be working and I'm sitting here scratching my head. Table JOB LINES - where each check is entered and the check issue date. Table JOB TRACKING - contains portal for JOB LINES where data for each tax year, including checks and the quarterly earnings fields. Other calculation fields are able to reference each table accordingly and return accurate results for each tax year. Let ( [ sqlQuery = "SELECT Sum (Chk_GROSS) FROM \"JOB LINES\" WHERE paymentFlag IS NULL AND Chk_DATE BETWEEN ? AND ? AND Form IN («FORMS») " ; FORMS = Substitute ( JOB TRACKING::Quarter_Tax_Form ; ¶ ; "," ) ] ; Case ( ExecuteSQL ( Substitute ( sqlQuery ; "«FORMS»" ; JOB TRACKING::Quarter_Tax_Form ) ; "" ; "" ; JOB TRACKING::Quarter1Start ; JOB TRACKING::Quarter1End ) = "?" ; "" ; ExecuteSQL ( Substitute ( sqlQuery ; "«FORMS»" ; JOB TRACKING::Quarter_Tax_Form ) ; "" ; "" ; JOB TRACKING::Quarter1Start ; JOB TRACKING::Quarter1End ) ) ) Quarter1Start and Quarter1End contain the quarter dates, i.e. Jan 1 to March 31. JOBTRACKING::Quarter_Tax_Form contains checkboxes from a value list to "filter" the particular tax form, i.e. W2, 1099, etc. I realize I'm missing the link between the two tables (JOB LINES::YEARid = JOBTRACKING::PrimaryKey). I've tried including this and I'm still getting the same error (see below) so I'm trying to eliminate calculation steps to see where the error is coming from. There is an error in the syntax of the query. If I select a tax form (in this case "W2") in the checkbox set I get the following: The column named "W2" does not exist in any table in the column reference's scope. If the tax form I select includes a numerical form, such as 1099, I get the following: An expression contains data types that cannot be compared. Perhaps a more experienced user can see where I'm going wrong here. Thanks so much.

Good to know I suppose. Another SQL trick up my sleeve. Also, I didn't know « » existed as operators(?) inside FMP.

Is that using the ? clause to determine the IN operator? AND ProppID IN (?) ; ... " ... ; gValueListField or AND ProppID IN ( gValueListField ) ; ... I think I'm missing something simple here.

@comment - is it possible to have SQL reference a field containing a value list? Using a checkbox set to allow me to choose which properties are factored into the calculation - ProppID would be changed to the field containing the keys. A new field in the Properties table, say 'gPropertySelect' would contain the checkboxes, show values from field 2. I tested this, but as soon as more than one value is "selected" the calculation breaks. I know it may not be the best solution, but curious if it's possible. ExecuteSQL ( "SELECT Sum (Remittance) FROM Payment WHERE Cat = 'Rent' AND Type = 'Payment' AND Datefield BETWEEN ? AND ? AND PropID = ?" ; "" ; "" ; DateStart ; DateEnd ; ProppID )