N8N Search with numbers in text field results in 400 error: type mismatch

[lcx_at]

I have this search in N8N

But the column InvoiceNumber is a text in seatable which results in this error message: 400 - {"error_message":"type mismatch: Invoices.InvoiceNumber = 2.024031203e+09"} - type mismatch: Invoices.InvoiceNumber = 2.024031203e+09

If I add a letter to the search, for example A2024031203 and change the value in seatable as well, the search works as expected.
I tried adding quotes (" ’ and `) but that didn’t help.
How can I force n8n search to treat it as a string? Using something like

{{ String($json["Invoice Number"]) }}

also didn’t help and the original data is actually a string anyway.

{
  "Category": "Invoice",
  "Invoice Number": "2024031203",
  "Invoice Date": "2024-04-30",
  "Due Date": "2024-05-03"
}

[bennhatton]

Hi @lcx_at, it looks like a bug to me, because as you said, forcing the type of the search term doesn’t help…

Depending on what you need to do after that step, you could use this workaround :

1. Create a ‘Get many’ SeaTable node to get all the rows of your table (or view) (don’t forget to uncheck the simplify output switch so you can access the rows’ ids in the next nodes)
2. Add an ‘If’ node that make the test you need (see below)
   
   
3. In the next steps, you can access the corresponding rows using their ids
   ({{ $('ListRows').item.json._id }})

Hope this helps…
Bests,
Benjamin

[lcx_at]

I used the find by sql from Seatable which doesn’t have native support in n8n but can be accomplished with a base call.
Getting the URL is still a bit of a pain, I use the web inspector to find it

and then used this to trigger the search:

{
  "sql": "select _id,InvoiceNumber from `Invoices` where InvoiceNumber = '{{ $json["Invoice Number"] }}' LIMIT 1",
  "convert_keys": true
}

but is a bit overkill just for searching an invoice number.