Daily Notification if entry doesn't exist for today.

[devblock]

I'm trying to create a daily notification if an entry doesn't exist in a certain table for the current day.  I have the following condition, but it doesn't seem to be working.

The Event is based on the Technician table and checks if each technician has an entry in the Mileage Data table for the current day.  I was testing it today and using the Test functionality in Appsheet it looked like it was returning Y for all technicians even though one of them did have an entry.

COUNT(SELECT(Mileage Data[starting_mileage],AND(([date] = TODAY()),([technician] = [_THISROW].[Email])))) = 0

[Kirk_Masden]

Have you tried testing to see if the parts of your AND() expression ([date] = TODAY() and [technician] = [_THISROW].[Email]) are functioning properly?

[devblock]

I know the date portion works properly as I'm using this exact same expression without the technician part to hide the new action after an entry already exists for that day (there should only be one mileage entry per technician per day).

I'm not sure how to test the technician part of it. Is there any way to "step through" the automation, like a debug mode, and be able to see what values are getting filled in for those variables?

[Marc_Dillon]

I'd suggest this expression instead.

ISBLANK( FILTER(
  Mileage Data ,
  AND(
    [date] = TODAY() ,
    [technician] = [_THISROW].[email]
  )
) )

If it still does not work, are you sure [technician] is actually holding email values?

[devblock]

That didn't seem to work either using the Test link.  But I'll keep it in there and see if it works when the bot actually runs Monday morning. [technician] is definitely holding an email, it gets filled in using the USEREMAIL() function and isn't editable.

[Kirk_Masden]

---

@devblock wrote:

Is there any way to "step through" the automation, like a debug mode, and be able to see what values are getting filled in for those variables?

---

I don't think there's a debug mode.  What I would do is make a separate virtual column to test it.

[Steve]

Your expression, reformatted::

(
  COUNT(
    SELECT(
      Mileage Data[starting_mileage],
      AND(
        ([date] = TODAY()),
        ([technician] = [_THISROW].[Email])
      )
    )
  )
  = 0
)

Alternatives:

(
  COUNT(
    SELECT(
      Mileage Data[starting_mileage],
      AND(
        ([date] = TODAY()),
        ([technician] = [_THISROW].[Email])
      )
    )
    - LIST("")
  )
  = 0
)

Or:

(
  COUNT(
    SELECT(
      Mileage Data[starting_mileage],
      AND(
        ([date] = TODAY()),
        ([technician] = [_THISROW].[Email])
      )
    )
    - LIST("")
  )
  = 0
)

Or:

ISBLANK(
  SELECT(
    Mileage Data[starting_mileage],
    AND(
      ([date] = TODAY()),
      ([technician] = [_THISROW].[Email])
    )
  )
  - LIST("")
)

Or:

ISBLANK(
  FILTER(
    "Mileage Data",
    AND(
      ([date] = TODAY()),
      ([technician] = [_THISROW].[Email]),
      ISNOTBLANK([starting_mileage])
    )
  )
)