2

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!

saved search function picture

dimay
  • 2,768
  • 1
  • 13
  • 22
ShannonQ
  • 21
  • 1
  • 2

1 Answers1

4

Please use Formula numeric and try with this formula

ABS(TO_DATE({messages.messagedate})-TO_DATE({trandate}))
David Buck
  • 3,752
  • 35
  • 31
  • 35
Addy
  • 41
  • 1