RavingLuhn

I've seen quite frequently how having a lot of fields with unstored calculations may result in sluggish database performance. Most articles and forum posts I've found suggest using a script to set values instead of using a calculation field. The idea makes sense to me, but I'm a bit at a loss when it comes to how to implement such a change. I humbly request assistance with one of my simplest applications: There are two tables: Product List, Issues In a portal for the product list, I want to count the number of open issues per product. As it stands now, I have a calculated field on the product list to count open issues on the Issues table. Because it's counting a related value, the calculation is unable to be stored. How would I convert this to a script or otherwise change the count to a stored value?

That makes a lot of sense. Thanks for helping me think through this!

I can think of quite a few reasons, but it could be that I'm not approaching the issue from the right mindset. Here's a standard scenario: I want to look at our product list and review their status for this year and next year, so I go to the layout above. Upon loading the layout, a script runs to ensure the records (and thereby, the portal) are sorted by product name. There's only one product or group of products I need to review, so I enter find mode to narrow down the records displayed. Potentially, I even perform a find for the year and the specific status. When I've reviewed the information, I need to get back to the full list of products, so I select 'show all' from the top navigation bar. Would I be better suited adding controls to my layout instead of relying on the 'show all' button on the default top navigation bar?

Hi, I'm sorry for not being more clear. The layout based on products is a form view, and the layout contains a portal to display those records. Since the portal draws from the same table as the layout, I don't have the option to sort or filter it. I have a script to sort the records on layout enter, and it works. However, if I enter find mode to constrain the record set, the list becomes unsorted again after I 'show all'. Screenshot below looks near the bottom of the list:

What are my options for sorting a portal which displays records from the same table that the layout is based on? There's a products table and a status table. The products table contains a list of unique items, and the status table indicates the status of the product for each year. My layout is based on the products table and contains a global field to select a year, which returns the status for that year in the product portal. The problem is that my product list won't stay sorted. I've written a script to sort the records on layout enter, but when I add scripts for other things on the layout the list reverts to its unsorted state. Do I need to add a step to every script to make sure the list stays in sorted order?

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?

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?

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.

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.

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.

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!

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

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.

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!

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!