How can I determine the elapsed time between an item and it's related items?

[jwnacnud]

I have a solution (or need one) that takes events with a start and end date and then displays the overlapping (concurrent) events.

The relationship to show the concurrent events works as expected, displaying only items where there is an overlap, no problem there.

My question is that I want to be able to show the elapsed time between the main (focus) event and the concurrent event.

I am uploading a sample database to clarify my question.

Within the example database, when viewing the focus item, "Went To Disneyland" in Item View, it correctly displays the concurrent items, "Undergoing Cancer Treatments" and "Age 4". The start dates for the concurrent items are correct. However, the field ElapsedTimeDays is not correctly calculating the number of days between the start date of the concurrent items and the start date of the focus item.

How do I fix that so that I can display them in a portal, as any focus item can have an number of concurrent items.

Thank you in advance.

if needed, the UID/PASS is admin/admin, but I have it set to autologin.

Concurrency_Elapsed_Time.zip

Edited June 2, 2010 by Guest
I removed the password on the file

[comment]

Your file requires a username and password.

[jwnacnud]

Thank you for the heads up. I have removed the password.

[comment]

I want to be able to show the elapsed time between the main (focus) event and the concurrent event.

That's not possible the way you have it set up now. The "focus" event must be identified as such (e.g. by placing its ID into a global field/variable*). Otherwise the concurrent events cannot know which one of the related events you are currently focusing on, and they will always select the first one in the order of creation.

---

(*) You could use a script trigger for this.

[jwnacnud]

Could you show me an example of what you mean?

[comment]

Roughly:

OverlapDiff.zip

[jwnacnud]

Thank you. Your example works beautifully. I have spent the lasat 45 minutes trying to integrate what you did into what I have - unsuccessfully. I will keep at it, but if you have a few moments, could you make it work in my sample? I can't seem to get it working. (I hate to ask because you've already done so much to help). I sell routerbits and saw blades if you happen to be a woodworker. I can certainly help you out that way (routerbitworld.com). Not a plug for the website, just to show that my offer is real. Not just working out of my garage.

Thank you.

Edited June 2, 2010 by Guest

[comment]

Sorry, I am not doing it twice. Basically, the added elements are:

• gFocusEventID global field

• FocusEvent TO (and the relationship to it)

• cDiff calc field

• Sync script

• OnRecordLoad script trigger

[jwnacnud]

No problem, that's plenty. I will just keep plugging away at it. Thank you!!

[jwnacnud]

I was able to figure out what you did and replicate it. Thank you very much for you help.