Counting overlaped days

[Enigma20xx]

After searching the forum and looking into some others, finally I have to ask for some help...

I’m trying to make work a calc field in a portal and I can’t figure out what am I doing wrong because not always shows the correct count of days (lack of knowledge must be key, for sure, lol).

Everything seems to work fine, except the field that counts the overlapped days.

I upload a demo file, I apologise it’s in Spanish but I’m sure it’s easy understandable.

Is it the relationship, is it the calc? I’m lost.

FechasSuperpuestas.fmp12

Edited November 29, 2023 by Enigma20xx

[comment]

6 minutes ago, Enigma20xx said:

I’m sure it’s easy understandable.

I don't think it is, and it is not because it's in Spanish. Please provide some specific examples of input dates where the result you get is different from what you expect.

 

[Enigma20xx]

If I'm not mistaking...

Layout T02. Record 5. Employee 5 can't have an overlap of 4 days. The real overlap should be 2 days.

Layout T02. Record 9. Employee 5 can't have an overlap of 4 days. The real overlap should be 8 days.

Layout T02. Record 10. Employee 1 (second portal row) can't have an overlap of 1 days. The real overlap should be 2 days.

Layout T02. Record 11. Employee 6 can't have an overlap of 3 days because the range of employee 1 is only 2 days.

Layout T02. Record 11. Employee 4 can't have an overlap of 1 day. The real overlap should be 2 days.

Layout T02. Record 12. Employee 3 (second portal row) can't have an overlap of 3 days. The real overlap should be 2 days.

Layout T02. Record 12. Employee 3 (third portal row) can't have an overlap of 2 days. The real overlap should be 8 days.

Layout T02. Record 13. Employee 4 can't have an overlap of 1 days. The real overlap should be 3 days.

Layout T02. Record 14. Employee 4 can't have an overlap of 1 days. The real overlap should be 2 days.

Layout T02. Record 14. Employee 6 can't have an overlap of 3 days because the range of employee 1 is only 2 days.

Edited November 29, 2023 by Enigma20xx

[comment]

I cannot find my way in your file.

I am attaching my test file, where I have replicated the data from your record #11. The results I am getting are indeed different from yours. You will have to find the difference between the two files yourself.

 

RangeOverlap.fmp12

[Enigma20xx]

comment, as always thanks for your effort and great help.

I think the structure of your test file is not the same or similar to mine. In your example you are comparing ranges from the same employee, and must be compared with the others’.

Having employees, we establish a range of days (their holidays) to each one.

This range just created, must be compared with the holiday ranges of the other employees to see if there’s any overlap.

In my understanding the logic schema would be:
- From table EMPLOYEES create records in the table RANGE OF DATES (pretty simple).
- In the table RANGE OF DATES every record should be compared with the rest of ranges, excluding those from the same employee. A self relationship (EmployeeID not equal to EmployeeID; range greater than or equal to startDate; range less than or equal to endDate)????

[Enigma20xx]

Could it be a bug or something? Because the data viewer shows the right calculation...

Most of the times the first row of the portal works fine and the issue happens in some other rows, but not always. Some other times happens in the first row of the portal too.

[comment]

25 minutes ago, Enigma20xx said:

Having employees, we establish a range of days (their holidays) to each one.

This range just created, must be compared with the holiday ranges of the other employees to see if there’s any overlap.

You are right, that is not what my file does.

And I don't know how this could be done using a single calculation that compares two ranges. Suppose you have 3 employees with 3 ranges:

• A and B overlap by 2 days; 
• B and C overlap by 3 days;
• A and C overlap by 1 day;

What should be the result of the calculation, when performed from the context of A??? 

You seem to want to have the results displayed inside the portal - so that when A is the currently viewed record, the portal would show record B with an overlap of 2 days and record C with an overlap of 1 day. But records B and C do not "know" that you are currently viewing record A. The only way this could be accomplished would be to load the currently viewed record's dates into global fields (this could be done using a script triggered OnRecordLoad) and then have each record compare itself to this range.

Alternatively you could define the relationship so that only overlapping records are related. Then you will know that an overlap exists because it will show in the portal - even without calculating the overlap's length.

Note also that in your proposed schema, any holiday of an employee would be related to every holiday of all other employees. Assuming that employees take a holiday more than once, this would grow and grow and become slower every year.

 

 

 

[comment]

6 hours ago, comment said:

Alternatively you could define the relationship so that only overlapping records are related. Then you will know that an overlap exists because it will show in the portal - even without calculating the overlap's length.

Here's an example of how this could work:

OverlappingVacations.fmp12

Note that you could just perform a find instead (also shown in the attached file); then you wouldn't need to add any relationships or calculation fields.

[Enigma20xx]

After some work and trying to implement comment's suggestions, I think I have finally accomplish what I wanted to.

Setting a holiday date range for an employee, find out if this period overlaps with other/s, and if so, how many days overlaps.

I used a global field to make the calc work, but there had to be something wrong with the custom functions in my first demo file, because they didn't work well. I imported them again, and now they seem to work as expected.

I post de working file in case it could be useful for some other member of the forum.

As always comment, I really appreciate your help sharing your time and effort with others. 🥇

FechasSuperpuestas v.2.fmp12

[comment]

13 hours ago, Enigma20xx said:

there had to be something wrong with the custom functions in my first demo file,

I doubt that. The difference is that when you use global fields, each record compares itself to the global values. While in your first file, each record was comparing itself to the first related record (in the sort order of the relationship) - which sometimes happened to be the currently viewed record, but more often not.

 

[Enigma20xx]

👌