Jump to content

RavingLuhn

Members
  • Content Count

    16
  • Joined

  • Last visited

Community Reputation

0 Neutral

About RavingLuhn

  • Rank
    member

Profile Information

  • Location
    North Carolina

FileMaker Experience

  • Skill Level
    Novice
  • FM Application
    18

Platform Environment

  • OS Platform
    Windows
  • OS Version
    Win 10
  1. 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
  2. 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.
  3. 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!
  4. 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!
  5. Wow, that's incredible! Thanks for sharing! I appreciate your willingness to help people learn. 🙂
  6. Either way, I still need to have two fields? One for the month number and one for the text value?
  7. 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?
  8. 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.
  9. 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!
  10. The reason I have a separate table is that the two data sets come from wholly different processes. Tracking Data contains granular labor hour detail; hours per task per department per person per day kind of thing. The Statistics table holds a summary of output per week. There's no direct correlation between the labor hour records and output; that's why I need to keep the output records separate and then calculate everything based on weekly totals. I did recreate the report based on the Tracking Data table, the most atomic, and am getting most of the numbers I need. However, I'm running into issues when trying to run calculations off the subtotals. What I'm trying to do here is divide s_TrackedSeconds by c_PlantCount. I should wind up with 33.97, but get 9839.5: Here's the calculation configuration for c_Seconds Per Plant Per Week: I'm guessing part of the issue is that the s_ShippedPlants is coming from a different table... but that field displays the correct value when placed on the layout so I'm a bit confused. Why does the field show the correct value on the layout, but something goes AWOL when trying to use those fields in a calculation?
  11. Thanks. My brain struggles to grasp the atomic-level mode of thinking, but it's starting to sink in. I've got a find set and summary fields working as expected. I have the records for output per week residing in the parent table. Since the child table has detail records to the tune of multiple per day, I need the output stored separately. It's possible I may need to create more summary fields, right?
  12. I'm in the process of designing a report and am running into issues thinking through how to narrow down a record set for the math of the report. My parent report table has records for each week, as well as output per week. These values are entered manually since there's no viable option for a data connection. The child table has detailed records of hours spent on task per department per day. I've already managed to build the report to only show totals for one department by using the relationship as linked to a global value in the parent table. My question is: how do I further narrow the calculations to only count hours for specific records in the child table? In the screenshot below, I'd like to limit the math in table R_T01 to include only records in LT_T03 to ones where the TaskTrackNum match one of six values (in addition to SubDeptNum of my choice). My criteria for TaskTrackNum are 001, 110, 164, 551, 560, 569, 578 Here's the relationship diagram: Here's a glimpse at the source data table: And here's a section of the report layout:
  13. Got it! Thanks! I'm still learning how to apply database mentality to my design approach and tend to forget about capabilities like this.
  14. If I do this, the space that would have been occupied by the fields in the body is still there. That's behaving as expected, right? It sounds like it may be better to have some global fields to specify find criteria, then jump to my grid layout to perform the find, then jump back to the report. How persistent are those found records? Do I need to add an 'exit layout' script to show all records? Thanks!
×
×
  • Create New...

Important Information

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