Exception: Param: transactionAmount - Text filter requires a scalar

aleksis

Hello!

I am trying to run a loop to save multiple records from an external API to my DB. When doing an API call (without loop) it is workign fine and returning all data. (screen 1)

screen1.png

Once I add the loop it runs into an "Exception: Param transactionAmount - Text filter requires a scalar."
The custom function is the working API call to fetch the transactions.

screen2.png

Create Variable:

screen3.png

For each loop:

screen4.png

Add record:

screen5.png

Once I run it it returns I get:

screen6.png

and debugger shows:

{"input":{},"env":{"$datasource":"live","$http_headers":{"Cookie":"_gcl_au=1.1.933501340.1690353872; _lfa=LF1.1.08da592dc08a59ee.1690353873798; intercom-device-id-abk6h5qu=e8d6db56-759a-4f49-9e9c-59f2b0608e62; _ga=GA1.1.476442276.1690353872; ajs_user_id=44854; ajs_anonymous_id=ea07048a-5e3a-4c28-9072-8d5e90a91d82; _ga_EJWDZRK3CG=GS1.1.1693305311.33.1.1693310811.0.0.0; fs_uid=#YFS75#5702350951690240:5097472524431360:::#ae2eb5d6#/1721889871; intercom-session-abk6h5qu=OUdMNVdvTTUzQjdFSDY3T0dhVDk3VzJZVzR2aWJhQ3dYVUZ0bVNJdDFpZWNaV3Y5UnJWSE5UMUFENzNiY0VicS0teEJXdStCTXB2ZTMrdjY2UXpNejBaUT09--97916dce805fe9e8cbc1e8ddc42c156ba5360614","Accept-Language":"lv-LV,lv;q=0.9,en-US;q=0.8,en;q=0.7","Accept-Encoding":"gzip, deflate, br","Referer":"https://xwxg-wksf-azxc.f2.xano.io/admin/workspace/2-0/api/22/query/119","Sec-Fetch-Dest":"empty","Sec-Fetch-Mode":"cors","Sec-Fetch-Site":"same-origin","Origin":"https://xwxg-wksf-azxc.f2.xano.io","Sec-Ch-Ua-Platform":""Windows"","X-Branch":"v1","Accept":"application/json, text/plain, /","Content-Type":"application/json","Authorization":"Bearer eyJhbGciOiJBMjU2S1ciLCJlbmMiOiJBMjU2Q0JDLUhTNTEyIiwiemlwIjoiREVGIn0.5WLtYBMyY7gR89eCa3Z5CC8wyTcRllsb-02DZLIzD9z9NpOKGHSTS18PJz9Mg9Xc-5P6awrL2JdoIxGDZMVG8RwerP8EAI4y.XB9btBPsWpS0O5_hj92acw.XF7hXyrCy9OxLi5GgmHMKYWfVqsW5I6nJT007GPNQY9Q0RenhcLticlq6BfAy8s2IoMVjOLmYBvLggc598ziNHndrYfAOz3pn3eA7as9IIHLg8p8SUSvA-bn7_rNtIgdN8zaRJRX8ifTcGD7aCxGoYFRHUfbRs4BnWyQqDvivTPKYsPIs2r-btZloqczkPMbEpO9AlAWQw5k0y9rfrGXSfFYxToQIM6Hu-_I2KYYySxr4TDwGqRch5q-kI78G9GxresQwi0PW7RZlUH_elNswXb5eaUJJ6WHHfXIK-IJ3SMAppojRvyRBy-1JGAQsexUYBxKyxoyPHBaZ-5Q2Gh4wqh1BFaH8-Np9dAyjqUFLLAxag0FN8dktbsysvAwzOposONjcW4fV169PqsgkeuoDZbtDwLhXsKorLE6bSpgPX7MaZpw3SyRSYBbl3Meg67Qi1X4nbJjAzTXHJ46jd6ayR-p-VMbOzD9p5G09up1rUiofqEGwT5cKZoOpmtH6hQuY1oyaTGXGAqyuei16Myit5rR-sTMTru2_r0tNxM9RjEMXKQ8RMEel-nxfkrKCnus2WSaB2GgZowAVDKfdNvWrvydbHGcFdpitO2LDBrjbW-L5O7xFQJsvFGiGkfkL6q1J8LQV-NvDuHBypAPne1Gld64OkgnZOlHKsOxY_j2fBo8NNyGWx6XoWU-_Bh39D6Pdpvd265a62K6Cr8_yd27w-xs54e34TgeddfCXA9WMQgOSNf0pHMQ9AcSJ_4QJpAiAvPgieqAGEZFVSmKVwCFn30LroNyNbFzj0Ac066YO5nIW1FCXZCAJAzuhyBvel7YAi6AbaPODfr2_BPEFvllcCgop2_DHHWo4cLq0jv3rhdR1IWMGlRwABatuRz6-idXj4g0R6_JUcsa24R70dawuQ.SofuiBIEIGAfFC-tF7v11ZL5fHt8TeNMJ1_QJbftx04","User-Agent":"Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.0.0 Safari/537.36","Sec-Ch-Ua-Mobile":"?0","Sec-Ch-Ua":""Chromium";v="116", "Not)A;Brand";v="24", "Google Chrome";v="116"","Content-Length":"6053","X-Scheme":"https","X-Forwarded-Scheme":"https","X-Forwarded-Proto":"https","X-Forwarded-Port":"443","X-Forwarded-Host":"xwxg-wksf-azxc.f2.xano.io","X-Forwarded-For":"95.68.6.74","X-Real-Ip":"95.68.6.74","X-Request-Id":"6d16a8c9386741ef0be30344e95a3b52","Host":"xwxg-wksf-azxc.f2.xano.io"},"$remote_ip":"95.68.6.74","$request_uri":"/api:mvp-admin/query/119/debug","$request_querystring":""}}

I assume the problem is with TransactionAmount having two sub-values

problem.png

In the database I have enabled "list" for these parameters, but still get same problem

db.png

Can you help me with this? I

arturosanz

transactionAmount is an object not a text or text[].

You need to define the field type according to the type of data you want so store.

item.transactionAmount is an object, but you defined transactionAmount as text[], so the assignment fails. Define transactionAmount as object in your table and replicate the same schema {amount: integer, currency: text} and you should have fixed the problem.

Change the amount field type too, because it should be of type integer not text[].

Maybe it works with text instead of integer, because that type of casting is often allowed. But it's impossible to cast an object into a text, list of text, or any other primitive type.

aleksis

Still not able to fix it on my own. Any chance you can help with tips / ideas @Michael Udinski ?

aleksis

Thanks @arturosanz ! This helped and now I am able to store transactions from external api request in my DB.

Everything worked well, until I hit an exception, where for some particular transactions there is one var missing. Receiving: message: Unable to locate var: item.creditorAccount
After investigation I found that some transactions do not contain a creditor account and for most this is empty.

1) Any idea how can I "allow" such exceptions where a specific var is missing in the structure?
2) Or perhaps, I can completely exclude this var, not to be stored in DB for all transactions? How could I do that?
Sorry for dumb questions, I am still new to this but really want to get better at it.

aleksis

Ok, for all the newbies, sharing that you can actually just hide this field

and it will be stored as "0" in DB

aleksis

Ok, completely excluding is really not a viable option. Perhaps, anyone can help with:
1) Any idea how can I "allow" such exceptions where a specific var is missing in the structure for some entries/repsonses and is included in other?

arturosanz

Again there is a mismatch between the input type (object) and the table field (list of integers). You have to make sure the field types match the type of data you want to store.

creditorAccount is an object with a specific schema, probably the same as debtorAccount. It's important to know the schema because you must configure it in Xano, not only in the table field but also in the input.

If you do this right, then you shouldn't have any problem whether you receive the creditorAccount or not. Xano will store an empty object in the table field if it is not provided in the inputs.

This is how the creditorAccount field should be defined in the table, assuming it has the same schema as the debtorAccount field.

This is how the creditorAccount input should be configured according to the creditorAccount field.

This is how the function is mapped.

This is the result if creditorAccount data is received.

This is the result if creditorAccount data is {}.

This is the result if creditorAccount data is not received at all.

arturosanz

Make sure your table fields types match your inputs types and they both match the data received. creditorAccount is an object {} but your field type is a list of integers []. This is what is causing the error.

If you define the input and the table field as object {} you won't have any problem whether the data is included or not. Xano will leave the field empty if creditorAccount is not present without throwing any errors.