Re: API Update records in a table problem

Giuseppe88 · 06-15-2023 02:04 AM

Hello lads

I come back to you for help, I connected 2 apps to add, edit and delete data from each other.

the connection api works for "add" and "delete", but for "edit" it always returns

Failed: Webhook HTTP request failed with exception The remote server returned an error: (404) Not Found.

I tried and tried but nothing.

Can you help me?

This is the code for Edit

{
"Action": "Edit",
"Properties": {
"Locale": "en-GB",
"Timezone": "W. Europe Standard Time",
},
"Rows": [
{
"ID PRENOTAZIONE": "<<Any(Select(ESCURSIONI[ID PRENOTAZIONE],[_THISROW].[ID PRENOTAZIONE]=[ID PRENOTAZIONE]))>>",
"ID PRENOTAZIONE": "<<[ID PRENOTAZIONE]>>",
"DATA ESCURSIONE": "<<[DATA ESCURSIONE]>>",
"ESCURSIONE": "<<[ESCURSIONE]>>",
"ESCLUSIVA": "<<[ESCLUSIVA]>>",
"NUMERO PASSEGGERI": "<<[NUMERO PASSEGGERI]>>",
"ADULTI": "<<[ADULTI]>>",
"BAMBINI": "<<[BAMBINI]>>",
"FREE": "<<[FREE]>>",
"NAZIONALITA": "<<[NAZIONALITA]>>",
"MAIL": "<<[MAIL]>>",
"ANAGRAFICA (NOME E COGNOME)": "<<[ANAGRAFICA (NOME E COGNOME)]>>",
"TELEFONO": "<<[TELEFONO]>>",
"AGENZIA":"<<[AGENZIA]>>"
}
]
}

WillowMobileSys

@Giuseppe88 wrote:

"ID PRENOTAZIONE": "<<Any(Select(ESCURSIONI[ID PRENOTAZIONE],[_THISROW].[ID PRENOTAZIONE]=[ID PRENOTAZIONE]))>>",
"ID PRENOTAZIONE": "<<[ID PRENOTAZIONE]>>",

You have the [ID PRENOTAZIONE] column listed twice. You wouldn't want to list the first one. The Bot itself should perform the SELECT in the Condition section of the Event component and then that row (or rows) is/are sent to this API Task.

Your current implementation shown doesn't make sense for an edit because you are simply re-assigning values to itself. Nothing new is being updated.

SO...If you are trying to accomplish something more than selecting a single row and updating it, then please elaborate and we can help with the proper implementation.

Giuseppe88

HI

the tables of the two apps are identical, for this reason [booking id] is always the same

.

WillowMobileSys

Two apps!! I see!! I missed that part.

Are you aware that you can connect both apps to the SAME datasource and therefore wouldn't need to copy rows between them?

If for some reason you feel you do still need to copy rows, my previous comments still hold. You cannot specify, nor do you need to, the [ID PRENOTAZIONE] column twice. Remove the occurrence with the SELECT and try again.

Giuseppe88

Are you aware that you can connect both apps to the SAME datasource and therefore wouldn't need to copy rows between them?

my datasource is Make similar to Zapier no I don't know how the two apps connect to the same source.

I tried this other code for the "delete" function it works perfect, but for "edit" the result doesn't change, it always returns the same error

Failed: Webhook HTTP request failed with exception The remote server returned an error: (404) Not Found.

{
"Action": "Edit",
"Properties": {
"Locale": "en-GB",
"Timezone": "W. Europe Standard Time",
},
"Rows": [
{
"ID PRENOTAZIONE": "<<[ID PRENOTAZIONE]>>",
"DATA ESCURSIONE": "<<[DATA ESCURSIONE]>>",
"ESCURSIONE": "<<[ESCURSIONE]>>",
"ESCLUSIVA": "<<[ESCLUSIVA]>>",
"NUMERO PASSEGGERI": "<<[NUMERO PASSEGGERI]>>",
"ADULTI": "<<[ADULTI]>>",
"BAMBINI": "<<[BAMBINI]>>",
"FREE": "<<[FREE]>>",
"NAZIONALITA": "<<[NAZIONALITA]>>",
"MAIL": "<<[MAIL]>>",
"ANAGRAFICA (NOME E COGNOME)": "<<[ANAGRAFICA (NOME E COGNOME)]>>",
"TELEFONO": "<<[TELEFONO]>>",
"AGENZIA":"<<[AGENZIA]>>",
}
]
}

Delete functio

{
"Action": "Delete",
"Properties": {
"Locale": "en-GB",
"Timezone": "W. Europe Standard Time",
},
"Rows": [
{
"ID PRENOTAZIONE": "<<[ID PRENOTAZIONE]>>"
}
]
}

WillowMobileSys

@Giuseppe88 wrote:

my datasource is Make similar to Zapier no I don't know how the two apps connect to the same source.

I don't understand how Make fits into the picture regarding the issue you are having.

I don't see any syntax issues and the error is implying that that row you are trying to update can't be found. Are you certain the row you are attempting to edit pre-exists in that other app?

NOTE: you cannot create a single Bot with several API steps and then within that Bot add a row in one of those steps and then update that row with another step in the same Bot. Each API call operates independently of each other and has the dataset with the state at the start of the Bot. Subsequent steps won't know about the edits made in previous steps.

Giuseppe88

HI

thanks for the support. I use make to import tour bookings for my business from tripadvisor.

I have 2 apps one where the reservation has taken place Make processes the data and writes them "app booking" after writing API boot sends me the reservation to the second app "CRM app".

when the booking takes place, it is correctly imported into the "booking app" and written in the "crm app", the same if it were deleted.

Only uppdate doesn't work.

WillowMobileSys

@Giuseppe88 wrote:

Only uppdate doesn't work.

Ok. Now that I have been able to re-think it with the complete picture, I now realize the problem. The Update won't work in this way.

The problem is that you cannot select the row to be updated inside of the JSON. The row MUST be selected and passed into the API step. This means the app will need access to the "other" app table. But even if you did add the "other" table I am not certain HOW you can select the proper row in the "other" table to be updated based on a change in "this" row. There would need to be some clever way to identify the row and trigger a Bot for that row. Maybe @Marc_Dillon has a suggestion?

***************

To rewind a bit, I think you may be implementing more than you really need to. The passing of rows between apps is highly inefficient and prone to problems that lead to the apps being out of sync requiring manual updates to correct.

I know that Reservations and Bookings are different things but they are sufficiently similar that you can handle them both within a single table structure. I would encourage you to investigate combining the tables into a single structure - Parent and Child tables if need be - and then have both apps access that same datasource structure. This will make entries in one app immediately available in the other. You can use Security Filters to remove rows from an app once they reach a certain state. This would be a much more efficient design will less areas to cause issues.

Marc_Dillon

I think I would agree that OP should simply be sharing the same table between both apps.

@WillowMobileSys wrote:

The problem is that you cannot select the row to be updated inside of the JSON. The row MUST be selected and passed into the API step. This means the app will need access to the "other" app table.

I'm not sure exactly what you're saying here, or how you came to this conclusion.

Does the 404 error explicitly state "row having key 'xyz' not found"?
As always, I'd suggest using Postman to test and troubleshoot API calls.

In general, I think OP needs to elaborate on their situation here.

And make sure you don't leave trailing commas just hanging around:

Giuseppe88

Thanks for help

unfortunately I don't know and don't know how to use Postman to test and troubleshoot API calls.

I use appsheet in my spare time for my business and I've never worked with APIs I'm self taught.

WillowMobileSys

@Marc_Dillon wrote:

I'm not sure exactly what you're saying here, or how you came to this conclusion.

There are two apps. The task is to copy data from one row in app1 to a corresponding row in the app2. The tables in the two different apps are of the same name with the Key column of the same name.

Mainly I meant he can't pick the row to be updated because he doesn't have direct access to that "other" table in app2 to choose the row in the first place so he would need to add access to that "other" table.

The problem is that you cannot select the row to be updated inside of the JSON. The row MUST be selected and passed into the API step

This part I was just plain wrong and confused myself. There is a row passed in but it would be the row that was changed in app1, which is what is currently happening.

Now comes the dicey part...as far as I know, AppSheet treats Key column values as distinct "data types" when you attempt to match the key columns directly between two tables, e.g. [Key1] = [Key2] - even if they are the same value - they are considered two different keys. This is based on experience. When trying to find a row in table2 using the key column from table1 the result was "DataType mismatch" errors.

It was solved by converting the key value to TEXT and then lookup by the text value instead.

I just don't know how JSON handles this.

So...Returning the to the original JSON, it had

...
"ID PRENOTAZIONE": "<<Any(Select(ESCURSIONI[ID PRENOTAZIONE],[_THISROW].[ID PRENOTAZIONE]=[ID PRENOTAZIONE]))>>",
"ID PRENOTAZIONE": "<<[ID PRENOTAZIONE]>>",
...

which won't work firstly because the Key column is specified twice but also, I believe, the SELECT won't work because it is selecting the Key value from app 1 and not app 2 - resulting in wrong key column used.

...BUT would this work?

{
"Action": "Edit",
"Properties": {
"Locale": "en-GB",
"Timezone": "W. Europe Standard Time",
},
"Rows": [
{
"ID PRENOTAZIONE": "<<TEXT([ID PRENOTAZIONE])>>",
"DATA ESCURSIONE": "<<[DATA ESCURSIONE]>>",
"ESCURSIONE": "<<[ESCURSIONE]>>",
"ESCLUSIVA": "<<[ESCLUSIVA]>>",
"NUMERO PASSEGGERI": "<<[NUMERO PASSEGGERI]>>",
"ADULTI": "<<[ADULTI]>>",
"BAMBINI": "<<[BAMBINI]>>",
"FREE": "<<[FREE]>>",
"NAZIONALITA": "<<[NAZIONALITA]>>",
"MAIL": "<<[MAIL]>>",
"ANAGRAFICA (NOME E COGNOME)": "<<[ANAGRAFICA (NOME E COGNOME)]>>",
"TELEFONO": "<<[TELEFONO]>>",
"AGENZIA":"<<[AGENZIA]>>"
}
]
}

If not then I believe the table from app2 must be added and then the JSON key column lookup adjusted like this:

...
"ID PRENOTAZIONE": "<<Any(Select(ESCURSIONI_App2[ID PRENOTAZIONE],TEXT([_THISROW].[ID PRENOTAZIONE])=[ID PRENOTAZIONE]))>>",
...

Thoughts???

Marc_Dillon

The problem is that you cannot select the row to be updated inside of the JSON. The row MUST be selected and passed into the API step

If you're meaning that the webhook cannot pass an expression into the 2nd app, for the 2nd app to evaluate, then yes, absolutely.

Now comes the dicey part...as far as I know, AppSheet treats Key column values as distinct "data types" when you attempt to match the key columns directly between two tables, e.g. [Key1] = [Key2] - even if they are the same value - they are considered two different keys. This is based on experience. When trying to find a row in table2 using the key column from table1 the result was "DataType mismatch" errors

Even if this were true, any such context is lost when the template is evaluated into a plain-text webhook body. When the 2nd app receives the webhook, it's just text, it has no idea what other tables may be sharing the same keys, or anything of that nature.

And I tested a similar setup just now. I copied a table from an existing app, then loaded it up into a brand new app, then created an on-updates API webhook to edit the record in the 2nd app with the same key value. All worked fine.

I'd be more inclined to suspect that OP hasn't configured their key column in the 2nd app properly. Since he says it works fine with deletes, perhaps there is a computedKey VC that combines more than 1 column?

Giuseppe88

thanks for the reply

i tried both solutions with TEXT but none works. any other ideas?

Giuseppe88

but I don't understand is why "delete" finds the row with the same id and instead "edit" doesn't work?

{
"Action": "Delete",
"Properties": {
"Locale": "en-GB",
"Timezone": "W. Europe Standard Time",
},
"Rows": [
{
"ID PRENOTAZIONE": "<<[ID PRENOTAZIONE]>>"
}
]
}

WillowMobileSys

@Marc_Dillon wrote:

Even if this were true, any such context is lost when the template is evaluated into a plain-text webhook body. When the 2nd app receives the webhook, it's just text, it has no idea what other tables may be sharing the same keys, or anything of that nature.

A very good point but I guess the question is when does the validation occur that confirms the JSON is being built with the correct data/ "types". And as @Giuseppe88 indicated, he tried sending it as straight text as well and it didn't work.

@Giuseppe88 wrote:

but I don't understand is why "delete" finds the row with the same id and instead "edit" doesn't work?

This is also a very good point which I realize you said from the beginning! I too would think Edit behaves exactly the same when it comes to this key field.

@Giuseppe88 have you reconfirmed that the [ID PRENOTAZIONE] column is the key column in both tables? Also @Marc_Dillon asked this question below:

@Marc_Dillon wrote:

Does the 404 error explicitly state "row having key 'xyz' not found"?

If you are not getting this detailed of a message, then likely something else is going on and you need to contact AppSheet Support if you haven't already.

Giuseppe88

HI

I confirm that the key for the 2 tables is the same [BOOKING ID]. I can't figure out why it doesn't work.

WillowMobileSys

@WillowMobileSys wrote:

I confirm that the key for the 2 tables is the same [BOOKING ID]. I can't figure out why it doesn't work.

I assume [ID PRENOTAZIONE] column is the "Booking ID" you are referring to?

@Giuseppe88 wrote:

Failed: Webhook HTTP request failed with exception The remote server returned an error: (404) Not Found.

Originally, this above is what you reported as the error. By chance, are there any other details along with it?

Also, after trying to run the Bot and getting the error, have you checked the Audit Log and Monitor for any other information that may be helpful?

Giuseppe88

@WillowMobileSys

I assume [ID PRENOTAZIONE] column is the "Booking ID" you are referring to?

yes sorry

Also, after trying to run the Bot and getting the error, have you checked the Audit Log and Monitor for any other information that may be helpful?

yes but I just ran it and it doesn't show, I don't understand and then everything seems to work in the monitor.