How to make a relation between Date Range

Ask for Help
1

It is possible to check the relation based on date range?

For example Sheet 1 i have:

Name | Start Date | End Date |
User 1 | 2 Feb 2023 | 5 Feb 2023

Sheet 2

Name | Date
User 1 | 1 Feb 2023 | No relation
User 2 | 2 Feb 2023 | No relation
User 1 | 3 Feb 2023 | Got relation
User 1 | 4 Feb 2023 | Got relation

It is possible ?

2

Absolutely possible :wink:

3

Can i know how

4

Very complicated but possible… First, let me know what the workflow is… maybe there is an easier way to do it…
How do you enter dates in sheet 2? is sheet 1 fixed or variables? also, will multiple relations be allowed or only single ones?
Also… should it be filtered by user name, or will all data be considered?

5

dates in sheet 2 based on Form.
relation will be one-to-one.
Filtered by Username.

6
  1. will the user enter the date in the add item form?
  2. is the data in sheet 1 fixed, or will it be updated continuously?
  3. relation should find the first match and ignore the rest?
  4. only match data from User 1 to User 1?

the most important question is #2… if the data in sheet 1 is static… if yes… then is not hard to do it

7

Question 1 : user will not add the date . the date is set by SET COLUMN from the other sheet. Example : SHEET 3.

Question 2 : Updated continuously is can edit or not right ? Basically can update before the status Approved. Sheet 1 is something like leave form. Start date for leave and end date for leave . There’s additional column that we need to consider which is status. the data from Sheet 1 is static once the Status approved. cannot be changed anymore.

Question 3 : We make it unable if “Relation” is not empty . So it must one-to-one relation

Question 4 : Basically also based on the status but I believe this name-status can make it.

8

ok… I might be wrong because I don’t know your App, but this is my solution:

  1. set the date in row 1 of sheet 1 User Specific Column (use relation for that)
  2. copy that date using a single value to all rows
  3. add column if-else: a) user is not user1, set 0, b) date equal or greater start date… set 1
  4. add column if-else equal or less end date … set 1. (if the user is not user1, set 0)
  5. add a math column to sum these if-else columns
  6. add an if-else column if the sum is 2, then row ID
  7. add join step 6 column… and this is your relation!
    copy that to the new entry row
9

No 1 is sheet 1 or sheet 2? because in sheet 1 we have 2 dates required . Start date and end date.

10

yes, that is why you need to set that date there… so you can compare it to these two dates… later copy the result to a new row…
or you can use a single value column to copy date there from sheet 2

11

To set the date at first is referring to the date in sheet2 right ?

12

no… all action is done in sheet 1… result will be copied to sheet 2…
You need to have temporary USC columns for User Name and Date… so you can run if-else columns.
Use custom action…

  • step 1 sets the user name and date to the first row in sheet 1…
  • step 2 add a new row with the result from sheet 1 (row id, date, and user name) in sheet 2
13

But the date you mention in Step 1 is any date or must be the date that i need to copy in Sheet 2? I mean what date to put?
Because basically Sheet 1 and Sheet 2 is different form.

14

the date that you want to check if is related

15

oooooo. one by one… I see

16

may I ask what you are doing? I think I did a similar system for one of my customers :wink:
are you doing leaves to calculate salaries? and checking if leaves are in their working schedule?

17

I am making the Timesheet for technician/servicer. let say they have apply the leave and approved (In sheet 1), they cannot add the timesheet (in sheet 2) or we added for them but unable for them to enter details. thats why i need them to relate each other.

18

ohh… then if you find any sum equal to 2 then do not add a record to the timesheet…
you should start from this explanation LOL

19

But @Uzo , they do add form separately , as i understand your Step, it will add row to the sheet 2 based on the result we create in sheet 1 right?

20

yes… when they choose the date for the timesheet… set that date in sheet 1, so you will know if the date is valid… then you will create a record… so you don’t need to create relations…
unless you want to keep invalid timesheet records?