Rollup Formula with Date Filtering - Data Type Issues

[HeleneRousseau]

Hi SeaTable community,

I’m struggling with a rollup formula that’s giving me data type inconsistency errors. The formula is designed to calculate current monthly working hours for staff members, accounting for changing work patterns throughout the year.

What I’m trying to achieve:

• Pull each staff member’s current working arrangement (as patterns can change during the year)

• Calculate total monthly hours based on current weekly hours

• Subtract various deductions (rollups, leave, illness)

Current Formula:

IF({Start Date (Month)} <= 8,
  (rollup("Staff Working Patterns", "Total Hours Per Week", "sum", 
    {Staff Working Patterns.Effective Start Date} <= "2025-08-01"
  ) / 5 * 21)
  - {August Rollup} 
  - rollup("Events","Total Leave in hours", "sum","{Month}=8") 
  - rollup("Events","Total days of illness in hours", "sum","{Month}=8"),
  0
)

Original Formula that works before trying to add the date filtering from Staff Working Patterns table:

IF({Start Date (Month)} <= 8,
({Staff Working Patterns.Total Hours Per Week}/5*21) - {August Rollup} - rollup(“Events”,“Total Leave in hours”, “sum”,“{Month}=1”) - rollup(“Events”,“Total days of illness in hours”, “sum”,“{Month}=8”),
0)

The Problem: The formula fails with what appears to be data type inconsistencies. I’ve tried various approaches but I am a bit stumped in how I should approach it. Before adding the date the fomula worked fine.

Question: What’s SeaTable’s best practice for “current record” scenarios in linked tables?

[bha]

Hi @HeleneRousseau,

Unfortunately I wasn’t able to test the whole formula as it implies created a lot of columns, tables, links and so on, but here is problematic part:

• the condition for your rollup isn’t encapsulated by double quotes as it should be
• in the condition, you refer again to your actual table’s link column (Staff Working Patterns) whereas you should only refer to link table’s columns =>you should use {Effective Start Date} instead of {Staff Working Patterns.Effective Start Date} in your condition
• you’re trying to compare an actual date (the content of your Effective Start Date column to a string, which is the representation of a date in the format YYYY-MM-DD of course, but still only a string! That’s probably where the “Data inconsistencies” come from. You’ll have then to create a date object for 1st August 2025.

Here is a formula that worked, as far as I tried:
rollup("Staff Working Patterns", "Total Hours Per Week", "sum", "{Effective Start Date}<=date(2025,8,1)")

Bests,
Benjamin

[HeleneRousseau]

@HeleneRousseau

IF({Start Date (Month)} <= 8,
  (rollup("Staff Working Patterns", "Total Hours Per Week", "sum", 
    {Staff Working Patterns.Effective Start Date} <= "2025-08-01"
  ) / 5 * 21)
  - {August Rollup} 
  - rollup("Events","Total Leave in hours", "sum","{Month}=8") 
  - rollup("Events","Total days of illness in hours", "sum","{Month}=8"),
  0
)

Ah thank you so much ! This was really helpful I just tested it and it worked however I realised that it wasn’t calculating correctly because I needed to rather choose the end date otherwise it was including previous work patterns of that team member. This is what I ended up doing:

IF({Start Date (Month)} <= 8,
rollup(“Staff Working Patterns”, “Total Hours Per Week”, “sum”, “{Effective End Date}>date(2025,8,1)”) / 5 * 21) - {August Rollup} - rollup(“Events”,“Total Leave in hours”, “sum”,“{Month}=8”)rollup(“Events”,“Total days of illness in hours”, “sum”,“{Month}=8”)