Question

date.equals should be defined, instead was `undefined`


Userlevel 1
Badge

This post has been edited by a moderator to remove personal information. Please remember that this is a public forum and to remove any sensitive information prior to posting.

I’m trying to sync Google Sheets to Notion but some fields get errors. I suppose the problem is in some dates not filled, but it actually could be in my GSheets.

 

This is the error: 

body.filter.and[2].date.equals should be defined, instead was `undefined`.body.filter.and[2].date.before should be defined, instead was `undefined`.body.filter.and[2].date.after should be defined, instead was `undefined`.

 

Data Run:

_zap_search_success_on_miss:

False

database:

e81484cd-1ced-4e24-b0ed-a2ac4754be2b

properties|||ID|||title:

QU46

properties|||Annual Taxes|||number:

$292.00

properties|||Sold Date|||filter:

6/13/2023

properties|||Note|||rich_text:

{{194781594__COL$AW}}

properties|||Sold Price|||number:

$44,000.00

properties|||For Sale Price|||number:

$49,900.00

properties|||Acq Date|||filter:

12/8/2022

properties|||State|||rich_text:

TN

properties|||acq month|||number:

12

properties|||County|||rich_text:

Grainger

properties|||Costs Manual|||number:

$5,975.29

properties|||Size (acres)|||number:

7.92

properties|||Acq Price (Manual)|||number:

$26,064.70

properties|||Tax Expiration|||filter:

2/28/2024

properties|||Owner|||rich_text:

Quantum

properties|||Parcel|||rich_text:

026-009.00

properties|||Address|||rich_text:

xxxxxxxxxxx

properties|||Buyer Name|||rich_text:

xxxxxx xxxxxx

properties|||Property appraiser link|||url:

https://tnmap.tn.gov/assessment/

use_zapier_datetime_fields:

false

properties|||Sold Date|||date__start:

6/13/2023

properties|||Acq Date|||date__start:

12/8/2022

properties|||Type|||select:

Vacant Lot

properties|||Status|||select:

Closing

properties|||Cost center|||select:

Quantum

Fields with no value:

properties|||HOA amount|||number

properties|||Selling Costs|||number

properties|||Seller Financing Details|||rich_text

properties|||HOA Expiration|||filter

properties|||Commission paid|||number

properties|||Financing Details|||rich_text

properties|||%commission|||number

properties|||HOA Expiration|||date__start

properties|||Financing|||select

properties|||HOA Y/N|||checkbox

properties|||Seller Financing|||select

properties|||Tax Expiration|||date__start

properties|||Realtor|||checkbox

 

I tried to not map those fields but it doesn’t work. Any help? 


29 replies

Userlevel 7
Badge +12

Hi @omarbortolato, welcome to the community!

It’s a little tricky to troubleshoot without more information but one thing did stand out in the data that you shared - this one:

If you see the name of a field in double brackets like that, it means that the Zap wasn’t able to find the field it was supposed to. To fix that, go to this step of the Zap, delete the existing information in that field and re-add it. 

 

The field above is related to a note though, so it doesn’t sound like what’s causing the issue here. So we can take a look at this with you, please could you share:

  1. An outline of the Zap (what trigger/actions are there). 
  2. Which Zap action is running into the error
  3. A screenshot of how that action is set up in the Zap editor (Don’t forget to remove or obscure any personal or private information in any screenshot you share in the community (eg full name, email address, etc).

​I hope that's clear, please let us know if you have any questions! Thanks :) 

Userlevel 1
Badge

Thanks a lot for the quick response!! 

Some details: the zap want to transfer data from Google Sheets to notion everytime there is some changes or new raw.

Trigger:

Event: Find Database Item → is it correct? 🤔

 

I mapped all the notion fields with the Gsheets columns, but it seems some dates doesn’t work, of course some dates could be empty (I suppose this is the problem)

 

Let me know if now its’s more clear :)

 

Thx for the support

Omar

Userlevel 7
Badge +6

Hi there @omarbortolato,

I’m jumping in to see if I can help!

I looked into the issue you mentioned, and it appears that the error is connected to the field labeled with "…filter". To better understand the situation, could you please provide another screenshot that shows the fields with "…filter" in them? It's possible that these fields require specific values, and identifying them will help us determine the cause of the error.

Please keep us posted! 😊

Userlevel 1
Badge

Thank all for the interest. I’m having the same issue on another ZAP I’m trying to do (same integration GSheets 2 Notion)

The problem is alwas on the filter item although I don’t understand what it means, in my spreadsheet it’s a simple date :)

I have 4 dates and some are not filled in.

Thx again

Userlevel 7
Badge +6

Hi @omarbortolato,

Thank you for providing the additional information and the screenshot.

I would really appreciate it if you could help me out by clicking on the "Sold Date filter" field and checking its contents. It seems like the "...filter" fields might require a certain data from that particular field, and it's possible that it's a dropdown field expecting a specific value based on the error you encountered.

Thanks! 😊

Userlevel 1
Badge

Here we go, I have some new details. Actually the field is a “filter” field indeed there is an icon on the left and the menu has 2 different tab:

  • filter
  • custom

What I think is Zapier is thinking the field as a filter, that is “if this date is before, after or whatever, than run the zap” so it’s not a simple field to pass to notion.

I attach 3 screenshots, the first 2 show the 2 field tabs as mentioned above, the other is the google sheets where I take the information.

Thank you again for the support, this zap could help me setting my busienss cockpit, having all the info on notion but keeping G sheets as source of truth, other than using it as source for my charts on Google Data Studio (now Looker Studio).

Really appreciated

Omar

Userlevel 7
Badge +6

Hi there @omarbortolato,

Thanks for the additional information!

I did some digging into our notes, and it seems like you have to select from the “...filter” drop down fields (based on the error you will need to select the “equals” option) and a new field will appear. In this new field, you can map the data from the "Sold Date" column in your Google Sheet step.

34ec3565dc5e015b188cb957b6c1444e.png
(view larger)

Here’s an outline from our notes:

In "search" actions, a selection must first be made in these "filter" fields:

176cda268934506ff779ee126bb255e0.png

The corresponding date field will then be displayed:

d1d9014dd4001e5e7d26ad699be626a2.png

In "write" actions, a selection must first be made in the "Use Datetime Instead Of Date?" field:

35b3f4b559d03eb5894d229d58d69a0d.png

Again, this will cause the corresponding date field to be displayed:

dbeb8b80567b37f1894ed948043b3531.png

Hopefully, this points you to the right direction! 😊

Userlevel 1
Badge

Thanks a lot, this is really useful I’ll try it asap and share the results.

Userlevel 7
Badge +6

Hi @omarbortolato,

You're welcome! I'm glad you found the information useful.

Take your time to try it out, and please feel free to share the results with us whenever you're ready. 😊

Userlevel 1
Badge

Here again :) unfortunately something is still wrong 😫 obviously some field is empty at the origin and some other field is not mapped

Error:

 

date filter Fields:

 

Thanks again for the support

Omar

Userlevel 7
Badge +11

Hi @omarbortolato! 👋

Ah, yes it looks like a lot of those start date fields have fields mapped that don’t have a date as we’re seeing “No data” as the fields value from the selected Google Sheets sample:
2aa0a167bcc5b3ca542fe12ab6a1f53d.png
It might be worth changing the selected test sample from Google Sheets to see if that will give you some sample data that has a date value that you can use for testing purposes here.

Going forward, are those fields always going to be empty when the row is first added then the date is added to the row later on?

If so, it might be worth adding a column to the spreadsheet with a checkbox that you tick once it’s had the dates added. That way you can set that column as the trigger column which will get the Zap to only look at changes to information in that column. Now, that won’t stop it from triggering for brand new rows though so you’d likely need to add a filter to use a (Boolean) Is true rule that checks whether that column contains the value true (checkboxes like that will be true when ticked and false when they’re not). The downside with this is that adding a filter would make the Zap a Multi-Step Zap so if you’re not on a paid plan you’d need to upgrade to use a filter.

Can you try changing the test sample and let us know how you get on? 

Userlevel 1
Badge

Thank you all for your feedback.

Finally I opted for another trick :) I duplicated the sheet with the importrange function and keept only the useful column, not considering the problematic ones (e.g. filter colums). 

Now it works properly… I hope :)

I’ll keep it monitored.

Thx

Omar

Userlevel 7
Badge +9

Yay! 🎉 So glad to hear you were able to get this up and running. We appreciate you sharing your trick with the community too!
Keep us posted. 🤗

Userlevel 1
Badge

Hi folks,

although it seems working I noticed that it doesn’t update the fields but it creates new raw instead :(

The runs seems to be ok, you can see it detected the status changed to Sold

 

but on Notion I see  duplicated raws :(

 

 

Does anyone have some idea? :(

 

Thank you again for this neverending story :)

 

Omar

Userlevel 7
Badge +11

Hi @omarbortolato!

The action you’re using has this note in it:

 

 

Are ALL of the fields that you’ve added matching when the step runs? If not, then a new item will be created. Wanted to check on this first. Maybe you’ve got a field (or fields) that aren’t matching?

Userlevel 1
Badge

Hi @omarbortolato!

The action you’re using has this note in it:

 

 

Are ALL of the fields that you’ve added matching when the step runs? If not, then a new item will be created. Wanted to check on this first. Maybe you’ve got a field (or fields) that aren’t matching?

Yes I have a filed that is the key (ID) I supposed it was already set. 

I used “Find Database Item” as trigger, I supposed it was the best choice, or maybe “Update Database Item” is the correct one?

 

 

In this case I don’t understand what to chose as “Item” since I need to find if it exists before updating :(, moreover I would like to create a new item if the one searched is not present

 

Userlevel 7
Badge +11

Ok, that helps me understand what you’re trying to accomplish!

Here is the general flow when you’re looking to search for an item, create one if it’s not found and update it if it is found:

Trigger: Whatever your trigger is
Action: Find the Thing (and create if not found...assuming your action has this option)
Filter: Only continue if the thing is found
Action: Update the Thing

A more detailed explanation

Does this help to clarify?

Userlevel 1
Badge

Thank you Nick

it clarifies and indeed it’s the behavior I expected :) but something doesn’t work.

On Notion Zap you can choose between other these 2 actions:

1)  Update Database --> I tried to add only ID that is my key but it shows an error telling me that it expected a page uuid instead

2) Find Database and create if not existing --> I think it continues to be my first and the right choice, but although it runs without error, and the log is shown that the record is found, it creates a new raw on Notion DB and also I cannot set all the fields but only a few of them

 

LOGS:

 

Second raw on DB with fields not set

 

Only a few fields to be set on Zap

 

Thank again but I’m giving up :)

Userlevel 7
Badge +11

Oh man, that’s frustrating @omarbortolato!

Does Notion have a forum/community you could ask in? I wonder if there’s someone there who has run into this. 

If you choose to “un-give up” do let us know :) 

Userlevel 1
Badge

Hi folks, to be honest, I gave back :)

I just tried it again hoping for a better result but instead, it remained the same :(
Same error with date filters although it seems everything is set up.
Any update on that? Nobody faced a similar issue? 
Error: body.filter.and[3].date.equals should be defined, instead was `undefined

Is there a way to exclude some fields from the triggers to not incur this error?

Thank you for your support :)

Userlevel 7
Badge +11

Thanks for letting us know @omarbortolato.

Hmm, I did some digging into this just now and from what I can see in the Notion API documentation here it looks like they might be expecting the dates to be supplied in a different format, specifically in ISO 8601 format

From previous screenshots shared here it looks like the spreadsheet has dates formatted like this:
2f3c88378201f7e1a16d703fe72d8dfd.png
So I’m thinking that if you reformat all the dates into the expected format (e.g. 2023-04-29 instead of 4/29/2023) then that might get rid of that error. 

Can you try reformatting the dates to see if that allows it to run without error and let us know how it goes? 

Userlevel 1
Badge

Thank you @SamB just tried but it didn’t work. There should be some other mysterious problem :) 

Userlevel 7
Badge +11

Sorry to hear that @omarbortolato.

I decided to dig a bit deeper into this to see if I could recreate the error in order to better understand what’s potentially causing this. Here’s what I found:

The Find Database Item action is able to successfully locate items with date and times added like so:

53fab08ee695f3aefd63445cb1ac5819.png
Example showing it was able to successfully find it:
1cc0d317c8b5f50ba2ca0ad995d5070b.png
But when I then changed the date and enabled the create if not found feature:
0c98198d389fa5d732471bfd81b2af92.png
I then got this error: 
6885c35f5e6f5583838789dc44482d18.png
So, then I tried using a separate Create Database Item action and set the date fields using date/times the same format and that successfully created the item:
cf9c7fadcb6179a7bbd629223075e79f.png

So it appeared to be a bug with the Find Database Item action that only occurs with the create if not found feature is enabled. I did some further digging on this end and discovered that there is a open bug for this behaviour. Apparently it’s occurring for cases specifically a date filter is used. That being the case I’ve added you to the list of folks being affected by this issue. I can’t make any promises around when you can expect a fix to be implemented by but we’ll be sure to let you know as soon as it is!

As a workaround, you’d need to use a separate Create Database Item action to create the database item. As you’re wanting to update the item if it’s found as well then you’ll need two similar Zaps that use a filter to check whether an item was found. One that has a Create Database Item action after the filter, and another that has an Update Database Item action after the filter to handle both scenarios. 

Hope that helps. If you run into any issues in setting that up please let us know - happy to assist further on that! 🙂

Userlevel 1
Badge

Hey @SamB , you're amazing! Thank you so much.

So, I took your advice and removed all the filter fields from the find database trigger. My goal is straightforward: check if an item with a matching ID exists in Notion. If it does, I'll update the necessary fields; if not, I'll create a new item.

However, removing the filter fields indeed makes the zap work, but there's a catch. If the item isn't found, the creation process only includes a partial set of fields, meaning I can't create a fully detailed item as intended. My plan now is to adjust the setup so the find item action in Notion doesn't automatically lead to creating an item in the database, and instead, to handle updates separately. I'm thinking of setting up a second ZAP specifically for creating items, triggered by the absence of an item with the same ID in Notion.

I'm a bit unsure about splitting the process into two separate ZAPs, given my understanding that each zap action usually results in a single outcome. I'm envisioning two possible pathways after the "find Notion Item" action:

If found, proceed with updating the item.
If not found, trigger a new item creation.
I'm curious about your thoughts on this approach. Could it work?

Thanks again for your incredible support, Sam. You truly are a rockstar.

Best, Omar

Userlevel 7
Badge +11

 You are most welcome @omarbortolato! 🤗

Yes, that’s the same approach I was thinking of. We refer to is as branching logic and there’s two ways implement it. The first, is by using two paths in a single Zap (requires a Professional or higher plan). The second, is to set up two Zaps will opposing filter conditions (requires Starter plan or higher). 

To go with the filter approach you’d need two Zaps as follows:

Zap 1 - handles new items

  • Trigger: New or Updated Spreadsheet Row (Google Sheets) - triggers when a row is added/updated.
  • Action: Find Database Item (Notion) - searches for an existing item.
  • Action: Only continue if (Filter by Zapier) - only allows the Zap to continue if an existing item was not found (e.g. the _zap_search_was_found_status field is false). See: Search for existing data in Zaps.
  • Action: Create Database Item (Notion) - creates a new database item.

Zap 2 - updates existing items

  • Trigger: New or Updated Spreadsheet Row (Google Sheets) - triggers when a row is added/updated.
  • Action: Find Database Item (Notion) - searches for an existing item.
  • Action: Only continue if (Filter by Zapier) - only allows the Zap to continue if an existing item is found (e.g. the _zap_search_was_found_status field is true). See: Search for existing data in Zaps.
  • Action: Update Database Item (Notion) - updates the existing item.


That way even though both Zaps would trigger for the same new/updated Google Sheets row only one of them would be able to successfully meet the filter’s rule conditions, so only one would be continued. It’s a similar approach for paths only as only one of the paths rule conditions would match, so only one would be run.

You can find out more about using this kind of branching logic in Zaps here:


Let me know if you have any further questions on this or if you get stuck at all, I’m here to help!

Reply