NetSuite Saved Search Calculate Date Difference Between Two Related Records

Question

I'm trying to calculate the difference in days between an Invoice Date and the Date it was emailed out on a Saved Search.

I have a saved search created where I can see both the Invoice date (trandate) as well as the email date (messages.messagedate) but when I try to put this into a formula I keep getting a "field not found error".

I'm new to NetSuite and I also don't know a lot of SQL so I'm sure it's something basic I'm missing but here's what I have tried:

• Formula (Date): TRUNC({messages.messageDate}-{transaction.tranDate}) || ' days '

• Formula (Numeric): ROUND({message.messageddate}-{transaction.trandate})

I've tried "messages." and "message." and I've tried transaction. and without "transaction.".

I think I'm just missing the exact structure I need to use to reference the fields on two separate but connected records.

Please assist!

Answer 1

Please use Formula numeric and try with this formula

ABS(TO_DATE({messages.messagedate})-TO_DATE({trandate}))