Comparing dates in separate entities

[dhickey76]

Sorry Sergey this will be the last one for a while Just really stuck on this and cant figure it out.

2 entities, 1 sub-entity

Entity A (parent)
- sub entity B (child)

Entity C (parent)

Sub-entity B and Entity C are related using a common field

"Order date" is in Entity A
"Arrival date" is in Entity C

I want to check the difference in weeks between the Order Date and the Arrival date

Code: Select all

({function}-[ID])/86400/7

But when I try to pull the date from C into a function using "SELECT" it only gives the value in the latest record. See testing field "arrival function value" in screenshot.

Screenshot_9.png (11 KiB) Viewed 415 times

Is there another way of doing this?

Thanks!

[support]

Function SELECT will work on entity B to get parent A
You can't use it in entity C because A and C not related directly.
Looks like you have touse Myslq query field to get value from entity A
So query will be like this

Code: Select all

Select date from entity_A where id = (select parent_item_id from entity_B where id =[field_id_in_entity_C])

NOTE: this is just example. You have to use correct field and entity ID.