How to set auto create multi entities (with exactly date range) based on date range of a parent entity

Get Help
1

I have 3 database: Weekly Shift Define, Monthly Schedules, Fleeting
1/ Weekly Shift Define: which mean each Day has 2 Shifts

  • Shift 1: 7:30 am → 5:30 pm
  • Shift 2: 10:00 am → 8:00 pm
    image
    image784×626 32.2 KB

    2/ Each month, I have rotate the shifts with others.
    Ex:
  • June 2023, I have to work on Mon-Wed on Shift 1 + Tues-Thurs-Sart on Shift 2
  • July 2023, I have to swap shifts with others employee: Mon- Wed on Shift 2 + Tues-Thurs-Sart on Shift 1
    image
    image1352×288 16.3 KB

3/ The things I wanna help 's right here: I wanna auto create multi fleeting (all the days in that month or even the date range I set up in Monthly Schedule
image
) with the exactly datetime range for each day. based on the Date range

image
image1381×288 22.4 KB

image
image1484×656 35.9 KB

Here’s the output I wanna. I dont wanna create it manually. It’s too many. Any suggestion for this @Chr1sG
image

2

Hi @Minh_Tri_Do_Hang
I’m a little bit confused. You wrote that

Does this Mon, Tues Wed on Shift 1 or just Mon, Wed on Shift 1?

I presume that Sart means Saturday?
I can’t see that you mention Friday or Sunday anywhere :thinking:

As I understand it, you alternate working ‘early’ and ‘late’ shifts (at least for Monday through Friday). But the pattern is inverted every month.
Did I get that right?

If you examine a specific example week: 28/08 - 03/09 is it correct that you will work as follows:

28/08 Monday - early
29/08 Tuesday - late
30/08 Wednesday - early
31/08 Thursday - late
01/09 Friday - late (because it is a new month)
02/09 Saturday - ?
03/09 Sunday - ?

1 Like
3

Ah, sorry for missing information. I will express it clearer
Yes, I have alternate working shifts: early and late shifts rotate everyday + 2 days-off. and It 's interted every month. For more details, it would be nice to have a real life example:
Week 1:
31/7 Monday - early shift
1/8 Tuesday - late shift
2/8 Wednesday - early shift
3/8 Thursday - late shift
4/8 Friday - day off
5/8 Saturday - day off
6/8 Sunday - special shift (9:00 - 14:00)
Week 2-4: the same as Week 1

On Week 5 become inverted
28/8 Monday - day off
29/8 Tuesday - early shift
30/8 Wednesday - late shift
31/8 Thursday - early shift
1/9 Friday - late shift
2/9 Saturday - special shift (9:00 - 14:00)
3/9 Sunday - day off
Week 6-8: the same as Week 5

Week 9 - 12: the same as Week 1

4

Why is 4/8 a day off?
What is the rule for when the pattern should be inverted? Is it starting the monday when the week contains the 1st of the following month?

Is your ultimate objective to have a Calendar view of your shifts?
How far in advance do you want it to be planned?

5

Ah those’re the policies and scheduled by my manager
each week we have 2 day off. If this month, Monday 's early shift, I have 2 day off Friday and Sarturday. If this month, Tuesday 's early shift, I have 2 day off Sunday and Monday.
And i have to swap those shift with other employee each month
the Objective of this, each day I have to work with different clients and partner, also have some several different template task for each day, so I have to create daily schedule not just daily fleeting database
In real case, I wanna scheduled each days of each month vs reality:
Ex: Schedule 2023-Aug-07 Mon shift 1: I work with employee A, with some several template processes for Client B
vs Reality 2023-Aug-07 Mon shift 1: I work with employee X + Y (because employee A got sick), with some several template processes for client B
The way i do these to see the different between Schedule vs reality to have a right adjust or assign tasks suitable @Chr1sG

6

The reason I asked this

was because I was trying to determine whether you need each shift to be an entity, or whether you just need to see a text string somewhere telling you the hours you are expected to work on a given day.

How are you expecting to compare expectation vs reality (in Fibery terms)?

The way I see it, on any given day, the expected shift is either 1 or 2.
For Tuesday, 1 is late, and 2 is early.
For Friday, 1 is day-off, and 2 is late.

So I think you could create a db of shift options:
Early, Late, Special
with each one having a start time and end time (number) fields.

Name StartHr StartMin EndHr EndMin
Early 7 30 17 30
Late 10 0 20 0
Special 7 0 17 0

and then you can create an ‘Expected Shift’ db which has the following fields:

  • Shift pattern (A or B)
  • Day (Monday or Tuesday or Wednesday … Sunday)
  • Shift Option (to-one relation to first db)
Name Shift pattern Day Shift option
- A Monday Early
- A Tuesday Late
- A Wednesday Early
- A Thursday Late
- A Sunday Special
- B Tuesday Early
- B Wednesday Late
- B Thursday Early
- B Friday Late
- B Saturday Special

(name can optionally be made meaningful with a formula).

Then, for every day in your calendar, use an auto-relation (many-to-one) to link to an ‘Expected shift’ based on matching the parameters (Shift pattern and Day).

Note: you will need to create formulas in your calendar db to define the Shift pattern (based on the week/month it is in) and the Day (use Fibery’s WeekDayName function) if they don’t already exist.

Then you can create a formula field in the calendar db that calculates a date range, based on the start/end time number fields of the auto-linked Expected shift.

How does that sound?

7

How are you expecting to compare expectation vs reality (in Fibery terms)?

that’s what I dont know how to display or which view setup to see the different on overall. Can report view do this ?

Define ‘Expected shift’ database with Day field to set condition for auto-relation, seem make sense for me

Then, for every day in your calendar, use an auto-relation (many-to-one) to link to an ‘Expected shift’ based on matching the parameters (Shift pattern and Day).

So you mean ‘Calendar’ ⇄ Periodic database ? to set auto-relation by Day field above ?

Note: you will need to create formulas in your calendar db to define the Shift pattern (based on the week/month it is in) and the Day (use Fibery’s WeekDayName function) if they don’t already exist.
Then you can create a formula field in the calendar db that calculates a date range, based on the start/end time number fields of the auto-linked Expected shift.

Can it be more specific in here with an example, i’m not fully understand this from here

8

Yes. So the ‘Day of the week’ field is probably already present assuming you’re using the beta app (which I think you are).

As far as I understand your rules, if the last day of the week (Sunday) is in an odd month (Jan, Mar, May, Jul, Sep, Nov) then it is shift pattern A, otherwise shift pattern B.
So in the Period database, you could create a formula field as follows:

If(
  [Part of].Type.Name = "Week",
  If(
    RoundUp(Month([Part of].[End date]) / 2, 0) -
      RoundDown(Month([Part of].[End date]) / 2, 0) =
      1,
    "A",
    "B"
  ),
  ""
)

This will give each day in the Period db a value of A or B depending on which month the week ends in.

1 Like
9

wow, clever observation
but what if I have non structure inverted type ? Sometimes in some year, I got 3 months Shift pattern A, then 1 month Shift pattern B, then 2 months Shift pattern A. It sometimes can by non-fixed, which formula should we set to auto-relation for this case ?

10

Well, if you can explain the logic for whether a given week should follow pattern A or pattern B, then you can probably write a formula for it. Without knowing the clearly defined rules, I can’t say what it should be.

If it’s not very logical, then just manually set the shift pattern for each week, and then use a lookup for each of the day entities to get the value from the week it is part of.

1 Like
11

About auto-relation or set rule, which condition should i set to autolink day type and date range to periodic database. Ex

image
image798×86 3.32 KB

I cant figure out the condition for date range

12

Well, you probably need to create formula fields in both databases to get the day of the week as text, in order to compare them:

[Date in Week].Name (for the Expected shift db)

and

[Day of the week].Name (for the Period db)

and then you can match on these.

13

nah, I already do this, the things’s I wanna narrow results Fleetings field by Dates fields in Expected Shift db
image
Currently it display all Monday days

14

which means, I wanna set auto-relation for Periodic (Fleetings field) with Monday type with Date range from 31/7-20/8 only, not query all monday type like this @Chr1sG

image
image810×98 3.32 KB

15

Have you created the databases as I suggested?
If so, you will have a Shift option database and an Expected shift database.
You will want to auto-link the Expected shift database to your Period database (assuming you haven’t renamed it)

16

image
image345×501 9.89 KB

17

I tried it, and i feel it still rigid in some case of mine
Define fixed-Shift pattern in Periodic database make me cant modify in some real scenerio
So I consider set define direct into Shift Pattern + date range

image
image1425×436 32 KB

You see that I can auto-link Fleetings field by Date in week (Monday = Monday)
But the date range make me confused to set condition, any suggestion for this
To be clear Fleeting meaning Periodic Database @Chr1sG

18

FWIW I think the db you have called Weekly Shift Define is probably functionally very close (maybe the same) to being the Shift option database.
I have no idea what Fleeting database is.

The Monthly schedules db seems to be unnecessary if you’re doing what I suggested you need to do here:

and here:

which are just fields/formulas in the Period db

19

What if I dont use Set-relation auto for this case ?
Can I have a formula to Set Rule for match Date in Week + Dates Ranges from Shift Pattern to Periodic Database @Chr1sG

20

The rules for matching can only utilise ‘equals’.
There is no way to auto-link based on a formula for matching.

Is that what you meant?