# How to set auto create multi entities (with exactly date range) based on date range of a parent entity **URL:** \ **Category:** Get Help\ **Created:** [August 6, 2023, 12:42am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911 "2023-08-06T00:42:20Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 6, 2023, 12:42am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/1 "2023-08-06T00:42:20Z")
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](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/8/81cccc3c69f3337b50b9964562c0122637c4bfd6.png) 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](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/0/052b9431ad2fe919fa590bf19e775b25089c460a.png) 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](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/9/9681987e838b6f9824b2ef95f7de0b2a76cf9051.png) ) with the exactly datetime range for each day. based on the Date range ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/0/077a45a3189e0ef0c6bc980f9b2de7bdfb9e56d7.png) ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/d/d75f5985d6eff0c178bb49a672d4ca5e25fab664.png) Here’s the output I wanna. I dont wanna create it manually. It’s too many. Any suggestion for this @Chr1sG ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/3/3da885c40c14261f7fb93949fe1dca460b0c0f21.png) ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 6, 2023, 2:11pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/2 "2023-08-06T14:11:52Z")
Hi @Minh_Tri_Do_Hang I’m a little bit confused. You wrote that > [@Minh\_Tri\_Do\_Hang](#): > > June 2023, I have to work on Mon-Wed on Shift 1 Does this Mon, Tues Wed on Shift 1 or just Mon, Wed on Shift 1? > [@Minh\_Tri\_Do\_Hang](#): > > I have to work on Mon-Wed on Shift 1 + Tues-Thurs-Sart on Shift 2 I presume that Sart means Saturday? I can’t see that you mention Friday or Sunday anywhere 🤔 > [@Minh\_Tri\_Do\_Hang](#): > > Each month, I have rotate the shifts with others. 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 - ? ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 7, 2023, 11:45am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/3 "2023-08-07T11:45:13Z")
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 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 7, 2023, 12:10pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/4 "2023-08-07T12:10:11Z")
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? ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 7, 2023, 1:27pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/5 "2023-08-07T13:27:19Z")
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 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 7, 2023, 2:02pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/6 "2023-08-07T14:02:25Z")
The reason I asked this > [@Chr1sG](#): > > Is your ultimate objective to have a Calendar view of your shifts? 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. > [@Minh\_Tri\_Do\_Hang](#): > > The way i do these to see the different between Schedule vs reality to have a right adjust or assign tasks 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? ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 7, 2023, 2:16pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/7 "2023-08-07T14:16:45Z")
> 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 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 7, 2023, 2:27pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/8 "2023-08-07T14:27:42Z")
> [@Minh\_Tri\_Do\_Hang](#): > > So you mean ‘Calendar’ ⇄ Periodic database ? to set auto-relation by Day field above ? Yes. So the ‘Day of the week’ field is probably already present assuming you’re using the beta app (which I think you are). > [@Minh\_Tri\_Do\_Hang](#): > > Can it be more specific in here with an example, i’m not fully understand this from here 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: ```auto 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. ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 7, 2023, 2:44pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/9 "2023-08-07T14:44:18Z")
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 ? ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 7, 2023, 2:53pm UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/10 "2023-08-07T14:53:21Z")
> [@Minh\_Tri\_Do\_Hang](#): > > 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 ? 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. ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 8, 2023, 8:28am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/11 "2023-08-08T08:28:04Z")
About auto-relation or set rule, which condition should i set to autolink day type and date range to periodic database. Ex ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/e/e3344021b99e98ad397657cb7d4ef9d879e7521b.png) I cant figure out the condition for date range ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 8, 2023, 8:39am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/12 "2023-08-08T08:39:30Z")
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. ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 8, 2023, 8:45am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/13 "2023-08-08T08:45:05Z")
nah, I already do this, the things’s I wanna narrow results Fleetings field by Dates fields in Expected Shift db ![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/d/dff928f2c27bec91f3b4a8f372e7cab24ce7becf.png) Currently it display all Monday days ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 8, 2023, 8:48am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/14 "2023-08-08T08:48:35Z")
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](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/3/3318b0bb23870799c2762b1c3c007e7e3c73a5c2.png) ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 8, 2023, 9:06am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/15 "2023-08-08T09:06:58Z")
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) ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 8, 2023, 9:10am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/16 "2023-08-08T09:10:28Z")
![image](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/2/2dc1a83c916e40d898322a299665ac5124d52efc.png) ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 8, 2023, 9:16am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/17 "2023-08-08T09:16:42Z")
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](https://us1.discourse-cdn.com/flex020/uploads/fibery/original/2X/9/93af325bd377fcb69654ad1b54c21b2a083b99ea.png) 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 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 8, 2023, 9:16am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/18 "2023-08-08T09:16:49Z")
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: > [@Chr1sG](#): > > 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: and here: > [@Chr1sG](#): > > 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. which are just fields/formulas in the Period db ---
**Author:** ![Minh\_Tri\_Do\_Hang](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/minh_tri_do_hang/32/6791_2.png) [@Minh\_Tri\_Do\_Hang](https://community.fibery.io/u/Minh_Tri_Do_Hang)\ **Post date:** [August 8, 2023, 9:22am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/19 "2023-08-08T09:22:12Z")
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 ---
**Author:** ![Chr1sG](https://sea2.discourse-cdn.com/flex020/user_avatar/community.fibery.io/chr1sg/32/3941_2.png) [@Chr1sG](https://community.fibery.io/u/Chr1sG)\ **Post date:** [August 8, 2023, 11:46am UTC](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911/20 "2023-08-08T11:46:59Z")
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? [Next page](https://community.fibery.io/t/how-to-set-auto-create-multi-entities-with-exactly-date-range-based-on-date-range-of-a-parent-entity/4911.md?page=2)