Comparing dates in separate entities

Ask your questions here.
Post Reply
dhickey76
Posts: 42
Joined: 27 Mar 2020, 20:45
Name: Damien
Location: Utah, USA
Company Name: Techtop Canada

Comparing dates in separate entities

Post by 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
Screenshot_9.png (11 KiB) Viewed 243 times
Is there another way of doing this?

Thanks!

User avatar
support
Site Admin
Posts: 3587
Joined: 19 Oct 2014, 18:22
Name: Sergey Kharchishin
Location: Russia, Evpatoriya

Re: Comparing dates in separate entities

Post by 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.

Post Reply