Service Duration field name for formula?

Hi Ada Napierala ,

The field name for Service Duration Unit is service_duration_unit. However, when trying to build a formula in the Revenue Line Items (RLI) module with that field, the formula will not save since it does not recognize the field name. 

The 'subtotal' field in the RLI module has a stock formula using both service_duration_value and service_duration_unit in its calculation. However, if you go into that formula in Studio and attempt to save it without making any changes, you cannot proceed their either since neither field validates. 

I consider this to be a bug; I recommend raising the issue with Sugar Support and highlighting how stock fields in Sugar are able to use these fields in calculations (though they cannot be updated to still use those fields). 

Chris

Chris Raffle  

Thank you for a detailed explanation - I will definitely raise it with Sugar. 

And for now, I guess I will create a new custom field that will calculate the same number as Service Duration, from Service End Date and Start Date. Is it possible to get a number as a result? 

Hi Ada Napierala ,

It's definitely possible! When comparing two date fields and calculating the difference in Sugar, you need to account for 3 scenarios:

1. start date is today or in the future and end date is in the future
2. start date is in the past and end date is in the past or today
3. start date is in the past and end date is in the future

The following formula accounts for those 3 scenarios:

ifElse(
    or(
        greaterThan(
            daysUntil($service_start_date),
            -1
        ),
        not(
            greaterThan(
                daysUntil($service_end_date),
                0
            )
        )
    ),
    abs(
        subtract(
            abs(
                daysUntil($service_end_date)
            ),
            abs(
                daysUntil($service_start_date)
            )
        )
    ),
    subtract(
        daysUntil($service_end_date),
        daysUntil($service_start_date)
    )
)

The first part of the ifElse does the same formula as long as the dates fall under scenario 1 or 2 above. Scenario 3 is executed in the last calculation.

One note is that the value returned by this formula is 1 less than the actual number of days. For instance, with 2024 being a leap year, the days between November 9, 2023 and November 9, 2024 should be 366, but the formula returns 365. Similarly, when calculating days of a monthly service duration like September 15th to October 15th, the result is 29 instead of 30. You can use the add() formula around two calculations to increment the value by 1 if that is important for your use case.

Chris

Hi Ada Napierala ,

It's definitely possible! When comparing two date fields and calculating the difference in Sugar, you need to account for 3 scenarios:

1. start date is today or in the future and end date is in the future
2. start date is in the past and end date is in the past or today
3. start date is in the past and end date is in the future

The following formula accounts for those 3 scenarios:

ifElse(
    or(
        greaterThan(
            daysUntil($service_start_date),
            -1
        ),
        not(
            greaterThan(
                daysUntil($service_end_date),
                0
            )
        )
    ),
    abs(
        subtract(
            abs(
                daysUntil($service_end_date)
            ),
            abs(
                daysUntil($service_start_date)
            )
        )
    ),
    subtract(
        daysUntil($service_end_date),
        daysUntil($service_start_date)
    )
)

The first part of the ifElse does the same formula as long as the dates fall under scenario 1 or 2 above. Scenario 3 is executed in the last calculation.

One note is that the value returned by this formula is 1 less than the actual number of days. For instance, with 2024 being a leap year, the days between November 9, 2023 and November 9, 2024 should be 366, but the formula returns 365. Similarly, when calculating days of a monthly service duration like September 15th to October 15th, the result is 29 instead of 30. You can use the add() formula around two calculations to increment the value by 1 if that is important for your use case.

Chris