Issues with date operations - test doc provided

1

Hi all.

i am responsible for the duty scheduling of a team of about 60 people and am trying to create a vacation administration doc.
Employees can already submit their vacation requests by form, and that works well.
In my original doc there are some additional features implemented like a chart of a weekly absence level to see at which time too many employees have requested vacations.
At the moment I have some trouble implementing the recording of vacation periods for individual staff members…
I have created a test doc that describes the issues.

Would someone be kind enough to take a look at it and give me a hint?

I would also appreciate any suggestions in general on how you would create or optimize such an administration doc.

Thanks for your help!

Test Doc

2

hi Otto,

When you create a different set up, it will work
in the button you have a well working function (I modified a bit it to make it easier to read):

RunActions(
  Staff.Filter(CurrentValue.Contains(thisRow.Name))
    .ModifyRows(
      Staff.[Vacation Dates],
      ListCombine([Vacation Dates], thisRow.Dates)),
  ModifyRows(thisRow, thisRow.Set, True())
)

In the table Staff you put the dates into a column that is a relation. Due to this choice, you have blanks, when you put it on text, it is solved.

to filter out on the weekends I suggest:

thisRow.[Vacation Dates].Filter(CurrentValue.Weekday().Contains(1,7).Not()).Count()

Your syntax looks a bit like a spreadsheet way of writing, that makes it harder to spot irregularities.

I hope it helps, Cheers, Christiaan

3

Thanks, Christiaan, for reaching out.

I know I’ve not found a smart and consequent way of writing formulas yet, therefore I’m always very interested in any examples and thankful for that.

Regarding the column type switch to text, yes that solves the “Blank” problem, but I lose the conditional formatting as well which is a pity. And there’s still the sorting issue.

Well, at least I’ve managed to let the employees see their individual vacation dates in an alternative table using filtering, so I can live with those restrictions and little problems.

Thanks anyway!

4

hi @Otto_Kiefer ,

the sorting of the dates is possible by adding Sort(), see below

RunActions(
  Staff.Filter(CurrentValue.Contains(thisRow.Name))
    .ModifyRows(
      Staff.[Vacation Dates],
      ListCombine([Vacation Dates], thisRow.Dates).Sort()),
  ModifyRows(thisRow, thisRow.Set, True())
)

writing clear en understandable with Coda takes time to learn. But I believe it is essential to share work with others and to review (and understand) your own work over time.

May it help you, you can add comments in the code via //

Cheers, Christiaan