I need some pointers to solve the following:
In the case of the example, there are 3 timeline overlaps where the space used adds to 1500 sqm, 3500 sqm and 2500 sqm respectively.
Any thoughts will be appreciated.
Have you considered a report view? I think it can do exactly what you need.
Not yet, my actual requirement is in a table view where the user will interactively reduce the space used and/or overlaps to reduce the space used during overlapping periods. Using a report will provide a report of the overloads but the interactivity of the table view to remove the overload will be awkward and require a back-and-forth process between generating reports and changing values in the table view.
How did you imagine that the numbers might be represented?
In the image above, the Spaces Used - Customs entity contributes to all three of the overlaps you have listed. Are you expecting that all 3 numbers will show up on that entity? (and 2 numbers each on the other two)
I have available space of say 3000 sqm, I could manually look at the timeline view and find the overlaps, sum the spaces used and check if it is more than the space available. If it is more, manually reduce the space in one or more of the overlaps so that the space usage is less than what is available.
There are different ways to solve it e.g. split a space use period in two with less space used during an overlap with the space used increased after the period of overlap in order to keep the space used in line with what is available at all times.
This method becomes tedious as the number of spaces and overlaps increase, automating it with a formula could create entries in another database which could be displayed as milestones on the timeline view where overloads occur flagging problem areas.
I hope this provide more insight of what I am trying to achieve.
One solution could be to use a button to trigger a script that find the earliest start and latest end, and loop through the dates one day at a time while adding the spaces used and create and add an entity in the overload database for every day the usage summation exceeds the available space.
Not very elegant and may require some data cleanup.
I think it might be possible to find ways to identify/highlight periods of overlap where the required space exceeds the available space, but I think automating the process of ‘levelling’ such that entities are adjusted/split in order to stay within the limits is a much more serious challenge. There is no simple algorithm for determining the ‘correct’ fix, since the problem is underconstrained.
Looking at the example above, a simple fix for the overuse issue would be to change the start date of the Glock entity (to be after 29/05/2024) which would keep everything under 3000sqm. But changing the end date if Quehen (to be before 18/04/2024) would be just as valid.
And there are multiple possible solutions if the required sqm per entity can be changed. If the required sqm can actually vary over the duration of the entity, then the options are even more numerous.
I agree, I am not attempting to automate the levelling, merely to automate a visual indication on the timeline view where overuse occurs. The user will then manually solve the problem in one of many ways.
I am starting to think a looping script triggered by a button may be the only way.
I might suggest creating a document, and embedding the (editable) timeline view and then embedding a report view underneath.
In that way, a user can make changes, click the refresh icon on the report view and see the effect of the changes.
Yes, that could work. This solution might be a different way to achieve the same as a script using the enhanced calculations available in a report.
I will brood and sleep on it to see if other ideas come forth. Thanks
@Chr1sG, I tried both and found the report option too complicated.
I used the AI Script Assistant to generate the script and fixed a few minor errors in the script.
I’d love to see what you ended up with, if you don’t mind sharing
Yes, I will definitely share my experience and findings.
I am still experimenting how to find the best way to formulate the interaction with the AI assistant to understand the small syntax mistakes in the generated scripts and how to coach the AI to generate the most elegant solution.
