For tables that summarize data, is it common to "outsource" the column calculation?

1

I have a Table A which gets a new entry for each user every day. I have Table B which gets 30 entries every day.

I have a button “Add Table B Entry” that creates an entry and opens the modal for the user to add a note. This modal takes so long to open. I went into Settings > Doc Map > Dotted Menu Button > Debug Calculations and pressed my button to see what was up. It said that Table A has a Column that summarizes entries from Table B and that it was recalculated every time I added or changed something in Table B.

image
image896×1034 61.9 KB

So: Table A has a column called Table B List, which summarizes everything from Table B that matches the date and user of Table A. This makes adding each new column to Table B slow, since it triggers a recalculation of ALL rows in column Table B List.

Is there a way to make it not trigger? If not, I’m assuming the best solution is to create a button called “Recalc Table B List” that recalcs ALL rows once a day (Automate it to 5am or something). Or create a button in its own column in Table A called “Recalc Table B List for Row” and have an automation that checks when a new row is added to Table B, and to trigger the Recalc button for the date and user that matches in Table A.

Thoughts?

I feel like its going to get slower and slower without a solution, as we are only 2 months into using it and its already slow enough for users to complain. There must be something in terms of a “Design Pattern” for these types of situations.

2

This doesn’t answer your core question, but do you want to share your full formula for column B list here in case we can spot an optimization in how it’s written?

I think the daily automation is a good idea, as long as you don’t mind the A table being that stale.

3

Usually, filter formulas are getting slow quite rapidly when the dataset gets bigger, I had a similar problem (different setting). Possible solution:
“write” the information from table b to table a -
Add a “submit” button to table b that, when pressed, modifies the corresponding row in table a, to add the desired data.

1 Like
4

@Nick_HE , its a long formula, but I’ll post it up here just in case:

WithName(
    [Time Logs].Filter(Date = thisRow.Day and User = thisRow.User), 
    FilteredTimes,

    ListCombine(
        WithName(
            FilteredTimes.Filter(_isManualDuration = false).Sort(true, [Time Logs].[Start Time]), 
            TodaysTimes,

            TodaysTimes
                .FormulaMap( 
                    Concatenate(
                         _Color(IF(CurrentValue.[Start Time].ToTime() > thisRow.[End Time] OR CurrentValue.[End Time].ToTime() < thisRow.[Start Time], "#ff0000", "#000000"), 
                         Concatenate("(", Duration(CurrentValue.Duration.DateTimeTruncate("minutes")).ToText(), ") ", CurrentValue.Period)  ) , 
                         IF(
                                      CurrentValue.[End Time] != [Next Log Start] 
                                           AND (
                                                   NOT(TodaysTimes.[End Time].Max() = CurrentValue.[End Time]) 
                                                   OR thisRow.Day = today()
                                             )
                                       , 
                                      Concatenate(" (" , Duration(Duration(IfBlank(CurrentValue.[Next Log Start], Now()) - CurrentValue.[End Time]).DateTimeTruncate("Minutes")).ToText() , ")"), 
                                       ""
                          ) 
))),

WIthName(FilteredTimes.Filter(_isManualDuration = true).Duration, ManualEntries, IF(IsNotBlank(ManualEntries), List("Manual Entry(s): ", ManualEntries), "-"))
).BulletedList()

)

Basically, I have an
(1) OverallTimes Table, which has one row per user per day for them to clock in and out of the office (for payroll),
(2) and then I have a TimeLogs table where they can log time they spend on projects (payroll doesn’t go off of this number as the duration is not always the same as the duration between a clock in/out).

In my OverallTimes table, I am wanting to show a list of times that were logged to projects.

  • (ie: (9 min) 01/12 2:40 PM - 3:10 PM (15 min) ).
    The first duration lists the length of the logged time, while the second duration lists how much time was not logged between this log and the next log. I want the bulleted item to show black if it was within the payroll start/end and red if it was done after clocking out. i would also like the duration at the end to not show for any date but today, as I don’t need to know the duration until the person clocked in on the next morning.
5

Yes, I believe this is what I will need to do @Asaf_Dafna

6

Haven’t had a chance to look through in detail yet but can any portions of this be pulled out and cached in a column in your TimeLogs table? E.g. I wonder if even some of the concatenation of the first 3/4 could help if you did it in advance in a hidden column.