Jump to content


  • Posts

  • Joined

  • Last visited

Profile Information

  • Location
    North Carolina

FileMaker Experience

  • Skill Level
  • FM Application

Platform Environment

  • OS Platform
  • OS Version
    Win 10

RavingLuhn's Achievements


Explorer (4/14)

  • First Post
  • Collaborator
  • Conversation Starter
  • Week One Done
  • One Month Later

Recent Badges



  1. I have a report based on a join table. This report only contains sub-summary parts. The first summary part is grouped by 'employee name', and the second part is grouped by 'hour type'. This report displays on PC and prints just fine, but when I export the report to an Excel file it omits any subsequent appearances of the 'employee name' field. All the other fields are fine. Here's the layout view. Imp_EmpID, HourType, and s_Hours all come from the parent table. c_Name_Reversed and SubDeptartmentName come from child tables: Here's the report view from within FM (names redacted): And this is what the export looks like: The employee ID number does repeat, but the name doesn't. How can I modify this so that the name field shows up on all repetitions?
  2. At what point do you usually see performance degradation? I know it depends heavily on the specific design of a solution, but maybe there are some general guidelines you've observed in your experience?
  3. Is this because I intend to calculate or use summary fields on this table? For reference, we tend to average 60-70 employees a year which would result in about 25,000 records on this table for each year. I've been working on a sample file as a test and it seems to be pretty responsive.
  4. That probably wouldn't work, as I have layouts in this solution that require calculating daily discrepancies for all employees within a 14-day period. The basic layout is that a user clicks on an employee name in a portal from the employee table. This triggers a filter in a portal that displays records from the join table. The values shown here are the sum of hours from the time clock table as well as a sum of hours from the jobs table. A date is selected on this join portal to filter the jobs portal for that specific day. The user enters jobs and hours for that day until the discrepancy field in the join table shows a zero. My goal is to be able to find and display users with any discrepancies.
  5. The shortest answer I can give is: it's quite complicated. This join table exists to merge data between: A table with worked hours; this table is populated from an import and updated at least once daily A table with manually entered job (or assignment) hours. I need the join table to more easily calculate discrepancies for a given date and employee between the two tables. There are other fields on this table that aren't relevant to the script. These are primarily summary fields used for calculations and reporting. Given some other limitations, it's probably the simplest means to accomplish my end goal.
  6. In my database I've got a table that functions as a join table. Due to the nature of the data, I've got to create the records here manually - or at least with a script. There needs to be a set of records for each employee that contains their ID number and all possible dates of a given year. The workflow happens like this: An administrator navigates to the layout They enter an employee id number and the desired year into respective global fields: GLOBAL_AddYear and GLOBAL_AddEmployee A button is pressed and then the script runs to create a year's worth of records for the selected employee This is my script, which - as far as I can tell - does what I need it to. I'd appreciate a second set of eyes to take a look at it and see if there's anything I missed or could do better: #This script is meant to be run on LAYOUT #Will create a year's worth of records, inserting one employee ID number along with the specified year #Both values come from global fields filled out by the user #Check to see that both dependent fields are filled, then set variables. If [ IsEmpty ( GLOBAL_AddEmployee )or IsEmpty ( GLOBAL_AddYear )] Show Custom Dialog [ Message: "Need to specify a year and an employee ID number!" ] Exit Script [ ] End If Set Variable [ $SetYear; GLOBAL_AddYear] Set Variable [ $SetEmpID; GLOBAL_AddEmployee] #Perform a find to verify that a particular employee has no records for the specified year Set Error Capture [On] Show All Records Enter Find Mode [ ] Set Field [ TABLE::Date; "*/*/" & $SetYear ] Set Field [ TABLE::EmpIDNum; $SetEmpID ] Perform Find [ ] If [ Get ( FoundCount ) > 0 ] Show Custom Dialog [ Message: "This user already has a serires of records for the speified year." ] Exit Script [ ] End If #Get length of selected year in days, then set first date of year Set Variable [ $YearLength; Value:Date ( 12 ; 31 ; $SetYear ) - Date ( 1 ; 1 ; $SetYear ) + 1] Set Variable [ $YearDate; Value:Date ( 1 ; 1 ; $SetYear )] #Create starting point; couter begins at 1 here New Record/Request Set Field [ TABLE::EmpIDNum; $SetEmpID ] Set Field [ TABLE::Date; $YearDate ] Set Variable [ $Counter; Value:1 ] #Add new records; one for each day of the year. Loop will continue until counter equals number of days in each year Loop #Variables are increased here and are checked against year length Set Variable [ $YearDate; Value:$YearDate + 1] Set Variable [ $Counter; Value:$Counter + 1 ] Exit Loop If [ $Counter - 1 = $YearLength ] New Record/Request Set Field [ TABLE::EmpIDNum; $SetEmpID ] Set Field [ TABLE::Date; $YearDate ] End Loop Show All Records Sort Records [ Restore; No dialog ] Clear [ GLOBAL_AddYear] Clear [ GLOBAL_AddEmployee ] Are there any potential landmines here that I need to be aware of? I'll probably make a variation of this for year-end maintenance that creates a list of active employee ID numbers in December and then loops through for each ID, but I've got time to figure that out. Thanks!
  7. Point taken on the minimal complete example. I'll reference that for future instances. I think this is where the biggest gap in communication is coming from: What I'm trying to display is the cumulative total up to the current week of that year. Here is the pertinent data from my parent table: 'PlantCount' is the weekly output. I have a summary field in this table to calculate the annual cumulative output each week. So week 2 is the sum of weeks 1 + 2. Week three is the sum of weeks 1 through 3, etc. My child table has detail records for hours per day: I can use a calculation field to convert hours to seconds, and then a summary field to give me a running total of seconds per week on the report. What I'm trying to do is get the cumulative total of seconds divided by the cumulative total of plants for each week of the report. It might wind up looking something like this: Year Week PlantCount Seconds C_plants c_seconds Seconds per plant 2020 1 35,584 345,600 35,584 345,600 9.7 2020 2 103,508 418,212 139,092 763,812 5.5 2020 3 20,048 498,276 159,140 1,262,088 7.9
  8. Sorry. My experience in asking help from a community gravitates between two extremes. People either want all the details or none of them; trying to work on communicating more clearly. Trying again: How do I compare cumulative output from one table to cumulative output in a different table? The background: I have a database to track employee time spent on tasks per department, as well as metrics for different departments. This data resides in two different tables. My goal is to create a report that shows a weekly summary of total hours per department, whilst comparing those hours to the related outputs in a different table. I can do 95% of what I intend. The problem is that I want to compare cumulative hours to cumulative output; this will give me a running work pace for each week throughout the year. Since the summary fields that would generate those cumulative calculations reside in different tables I'm having difficulty figuring out how to lay everything out. In the sample file from my post above, the parent table is 'Shipping Volume'. There's one record for each week that contains week number, year, output, and so on. It is related to the child table, 'TrackingData' by Year, week number, and department. I have a report layout based on the information from Tracking Data. It displays information in sub summary parts only; no body parts. I've tried many combinations, but I can't figure out how to display a cumulative output from the shipping volume table on the corresponding weeks of the tracking data layout. The end goal here is to be able to divide cumulative seconds on the 'tracking data' table by cumulative output from the 'shipping volume' table to give me a running pace of seconds per unit.
  9. To help illustrate what I'm trying to do, I've uploaded a demo file: https://drive.google.com/open?id=1-knf0y7rMgBFI8loBAX0RQW1EGvJ9TpM My report layout has no body parts, so that might be what makes this more difficult to accomplish. Here's a view of the layout: And here's what the report itself looks like: The field labeled 'c_Cumulative Plants from Trackingdata' is a calculation field that's pulling a value from a summary field in a related table. Placed on this layout, the calculation field only shows the weekly total instead of the running cumulative total. My aim is to be able to divide s_Cumulative Seconds by that running total of plants. At the moment I don't have any idea how to get the running total of plants displayed on this layout. Any help would be appreciated!
  10. It's me again. This time my question is centered around how to reference a summary value from table A in a calculation in table B. The end goal is to calculate a running average of seconds per plant in the 'TrackingData' table by dividing time in seconds 's_Cumultaive Seconds' by 's_Cumulative PlantCount' from the 'Shipping Volume' table. After doing a bit of research I know that it's not possible to use the values of a summary field from table A in table B. So I made a field in 'TrackingData' to try and pull over the values from the 'Shipping Volume' table. That field does return the correct number of plants for each line in my subsummary, but it's not a running total. When I tried to make a summary field based off c_Cumulative Plants in 'Tracking Data'; it returns huge values and slows down the layout significantly. Here's the layout view: How can I get a cumulative plant count in my TrackingData table? Thanks!
  11. Wow, that's incredible! Thanks for sharing! I appreciate your willingness to help people learn. 🙂
  12. Either way, I still need to have two fields? One for the month number and one for the text value?
  13. Wait a minute; I misspoke. Originally I had a calculation field with the function 'MonthName' set to generate a numerical result. The report didn't function that way. Changing the calculation result to text solved the issue. Do you recommend I make another calculated field to return the first day of the month?
  14. It had been set as text, since the field returns the month name. However, changing it to a number makes the report sort by month; albeit in alphabetical order. Do you recommend I make another calculated field to return the first day of the month? I just thought it was odd that there was an option; I hadn't noticed the capability to sort by fields on a layout before.
  15. I've got another question about subsummary parts and how to ensure that they present data for reports as expected. My layout is based on the most atomic table for the data to be displayed, but I'm having some trouble getting sub-summaries to be grouped by month. What I expect to see is this: Year January Department List - numerical data February Department List - numerical data ..etc. for each month of the year What ends up happening is that my data is grouped by each year, then one seemingly random month is shown, but the departmental breakdown shows as intended. This is what I actually see: Here's a sample of my table data: My sub summary parts do coincide with the fields contained. The first part is set for year, the second for month, the third for sub department. This is the layout for the report: And here's how my sort criteria are set: So I guess I have two questions: How do I know when I have to sort by fields on a layout versus fields in the table? In this instance, records will sort by year regardless of the source, but in order for the sub department sort to work I have to choose the field from the layout and not the source table. Why is the report not creating sections for each month? Thanks in advance!
  • Create New...

Important Information

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