Using Global Constants to store holidays

I have quite a few formulae using WORKDAY & NETWORKDAYS which necessitates a long list of holidays in the required format. To save updating all these formulae with new future holiday dates I thought a constant would be ideal, so I created c_HOLS.

Although the formulae work perfectly when the holidays are included directly, they fail to work when I try to bring them in using c_HOLS. I’ve tried different formats including the [ ] in C_HOLS and excluding them and putting directly in the formula but nothing seems to work.

Any ideas on formula syntax to bring the constant in? Thanks, Ian

Hi Ian,

I’ve reproduced the issue successfully and will report to our developers.
We’ll keep you posted once the issued solved. :slight_smile:

Hi Ian,

Our developers have fixed this issue. :slight_smile:
However, to activate it, our support team will need to re-install the system sheet to your database account. May I know if you’ve made any design modification on system sheet such as “User”, “External User”, or “Group”?

Hi Angie, yes I’ve made quite a few changes to User & X-User, notably the section on default vehicle, but also some other fields such as ‘default entity’ and ‘related contact’ which pulls in info from my contacts sheet.

Is this all going to be lost? :open_mouth:

Cheers, Ian

Hi Ian,

Updating system sheets currently would only update from the “From Page”. So that I would suggest you to have these additional fields created by you previously to be listed on the listing page of the system sheets “User” and “X-User”. Once your system sheets are updated, you may revert to the last design version of the from page to have these fields back to the sheet form page, then you won’t lost the data.
And it’s also suggested to do a manual backup before updating your system sheets.

Please let us know once you are ready for us to update the system sheets for you by emailing to "support@ragic.com".
Thanks!

Hi Amy, I know it’s been a while since we had this conversation!

I’ve followed your instructions re my changes to system sheets and creating a backup so please can you update now so that the fix re global constants is applied and I can use c_HOLS in my formulae?

Many thanks, Ian

Hi Ian,

As mentioned in previous reply, you will need to send email to "support@ragic.com" to request an update to your system sheets, which the issue you encountered when using global constants will be fixed.

Best,

Thanks for confirming you have made the update Amy, but the global constant c_HOLS still doesn’t work, whereas the list of dates does. Most frustrating after you assured me your developers had fixed things and I’ve gone to all that trouble re system sheets!

Hi Ian,

I’ve reported this issue to our developers and will keep you posted from the mail that you requested for system sheet update.

Hi Ian,

We’ve applied a fix on this issue. Please navigate to the global constant and change the “Constant Type” to “Array” for your “HOLS” constant and test to see if it works as expected.

Hi Angie, I’ve done that. It still doesn’t work, either using c_HOLS or [c_HOLS], yet it works perfectly when I paste the cumbersome list of dates straight into the formula, which negates the whole value of the concept of global constants, of course.

Hi Ian,

Please set the global constant as below:

Constant Name: HOLS

Value: [“2019/01/01”,“2019/01/02”,“2019/04/19”,“2019/04/22”,“2019/05/06”,“2019/04/19”,“2019/05/27”,“2019/08/05”,“2019/08/26”,“2019/12/25”,“2019/12/26”,“2020/01/01”,“2020/01/02”,“2020/04/10”,“2020/04/13”,“2020/05/04”,“2020/05/25”,“2020/08/31”,“2020/12/25”]

Formulas: WORKDAY(start_date,days, c_HOLS)

Thanks. Finally got it working, I was pasting in the array from your message but it was obviously pasting hidden html or such like, when I manually typed all the dates in your prescribed format it worked. However, coming to the formula, WORKDAY doesn’t work for me; it’s fine if you want the day before or after (-1 or 1) but it doesn’t work with 0 as ‘days’. Eg payroll tax is due 22nd of month following unless that is a weekend or holiday. So if I use -1 and 22nd is a weekday non-holiday, the 21st is stipulated which is incorrect; but if I use 0 this seems to wreck the formula, which then specifies 22nd irrespective of whether it’s a weekend or holiday. I’ve had to construct 5 formulae using NETWORKDAYS in order to get this to work in all circumstances eg back to back holidays, holidays immediately preceding or following a weekend. You need to fix WORKDAY so it works with zero as a parameter.