0 Replies Latest reply: Aug 11, 2017 5:42 AM by Simon Touyet RSS

    How to check if another table has a record that meet a certain criteria?

    Simon Touyet

      Hi all,

       

      I have 2 tables, one with events and one with tasks.

       

      Tasks:

      Load

      [Task ID],

      [Event ID],

          Date

      Inline

      [

          Task ID, Event ID, Date

          1, c, 24/03/2016

          2, a, 06/06/2016

          3, e, 01/12/2016

          4, b, 01/01/2015

          5, f, 02/08/2016

          6, a, 02/02/2017

      ];

       

      Events:

      Load

      [Event ID],

          Date

      Inline

      [

        Event ID, Date

        a, 01/01/2017

        b, 01/01/2017

        c, 01/01/2017

        d, 01/01/2017

      ];

       

      What I would like is: while I am loading the Events table, can I have a calculated field where it checks if the Tasks table contains an item that has the same Event ID and a date older than the data in the event table?

       

      So basically, when the events table is loading its first row, it should ask itself "Is there a task with an [Event ID] = 'a' with a Date < 01/01/2017 ?" and should return "Yes".

      When the Events table loads its 4th row (event d) it should say "No" as there is no task with an event ID of 'd'