Creating webhook to update the columns from one app to another app

[Jack_Bates]

I need to update the the columns from one app to another app.

In the annualloadbook app I need to update the, Table {Trucks} Column [Last Inspection]

From the inspection app Table {Equipment} Column [Last Inspection]

{
"Action": "Add",
"Properties": {
"Locale": "en-US",
},
"Rows": {
[
"id_truck": "<<[ID]>>",
"Last Inspection": "<<[Last Inspection]>>"}
]
}

I’m getting an error and I’m unable to see what’s wrong if anyone can spot it please

[Aurelien]

Hi @Jack_Bates 

One bracket was misplaced 🙂

Can you try:

{
  "Action": "Add",
  "Properties": {
  "Locale": "en-US",
  },
  "Rows": {
    [
      "id_truck": "<<[ID]>>",
      "Last Inspection": "<<[Last Inspection]>>"
    ]
  }
}

[Jack_Bates]

I'll try that and let you know
Thank you
Jack

[Jack_Bates]

I’m still getting the error I don’t quite understand 

I tried to make it come from the Inspection table Virtual column [Vehicle Inspection Date] that is where the table Equipment column [Last Inspection] gets the date from

I just need to update the date in table Trucks [Last Inspection]

Am I missing something?

• Error 1 : Failed to parse JSON due to Invalid property identifier character: [. Path 'Rows', line 1, position 61.. Invalid JSON value starts with: ["id_truck": "e594058b","Last Inspection": "f093af0a"]}}

{
"Action": "Add",
"Properties": {
"Locale": "en-US",
},
"Rows": {
[
"id_truck": "<<[ID]>>",
"Last Inspection": "<<[Last Inspection]>>"
]
}
}

[Aurelien]

---

@Jack_Bates wrote:

I just need to update the date

---

but:

---

@Jack_Bates wrote:

{
"Action": "Add",
"Properties": {

---

What about using "Edit" instead?

Update records in a table - AppSheet Help

EDIT:

At looking at the documentation, brackets are being reversed, and one comma must be removed after the "locale" part.

Can you try this:

{
"Action": "Edit",
"Properties": {
"Locale": "en-US"
},
"Rows": [
{
"id_truck": "<<[ID]>>",
"Last Inspection": "<<[Last Inspection]>>"
}
]
}

[Jack_Bates]

I changed the Action to Edit and still have the same error

I noticed the ApplicationAccessKey was missing is disappeared after I edited the action and saved it

HTTP Headers

Optional HTTP headers. (Expressions that yield HTTP header names or values are supported.) 

Every time I edit it here and put the access key back in and save it disappears 

[Jack_Bates]

In the error that is the right [ID] for the Truck and that is the right date for the Inspection date

[Jack_Bates]

Replaced brackets and removed the coma after “Locale”

It tested OK 

Thank you Ill test it to see if the data is moving

Thank you for the help

[Jack_Bates]

After testing it says in the monitor that it was a success but the data in the destination [Last Inspection] didn’t change

The Column [Last Inspection] should have updated to 10/28/2024

[Jack_Bates]

As of the last few tests I’m not getting any errors and on execute it says success

But its still not changing the Last Inspection Date in the destination column

It looks correct you can see the Truck [ID] matches and the [Last Inspection] date is correct 10/28/2024 

But it did not change the date in the destination column [Last Inspection] Type Date

[scott192]

Are there any bots in your destination app that will trigger on the record update you are sending?  Could they be resetting the data?

[Jack_Bates]

The bot sends an email when the days since Last Inspection is greater than
the inspection interval.

[scott192]

If the API returns success that means  the API request was sent...that's all.
We need to see the audit log of the App the request was sent to.  It is that App where the proper error messages for why it couldn't carry out the request will be displayed...

[Jack_Bates]

Scott Thank you I didn’t know to look there.

It looks to me that it is only looking at the Key column [id_truck] which doesn’t need to be edited as the id_truck in the destination app is the same ID in the host app. 

It’s the [Last Inspection] column that needs to update and I don’t see it included her in the log

Log for destination app

[scott192]

Are you the owner of both apps though?  If not, do you have edit rights (through the app) to the table you are trying to update?  Does that field on the table have any constraints on it?  For instance, no edits if there is already a value stored etc.

[Jack_Bates]

I am the Owner of both apps Under the annualloadbook email

I am also a user under the jacknrene email and I do have edit rights  on both apps for the table and the columns in those tables

No constraints that my user privilege would stop

security filter on destination app Table trucks

LOOKUP(USEREMAIL(), App User, “EMAIL", “Type”) = "SOLO", [CreationEmail] = USEREMAIL()

Security filter on Host app table Equipment

OR(USEREMAIL()=[Created By],USEREMAIL()=(PII Removed by Staff))

Question, Why would it send request by user jacknrene and receive request using the annualloadbook email when both apps are logged in as jacknrene 

The equipment inspection date that it’s sending belongs to jacknrene email

[scott192]

I'm going off on a tangent here because I don't understand the data from one of your screen shots..  You have said that Last Inspection should be a Date and you show the screenshot of the sheet with a bunch of dates in that column.  However, when you look at the Automation Monitor ouput, you can see that Last Inspection seems to have a UNIQUEID() assigned to it?

[Jack_Bates]

Yes sir it does. In the maintenance app for the column [Last Inspection] is a type ref to table inspections virtual column [Vehicle inspection date] it puts the short date in the column [Last Inspection] in table Equipment.

However I changed the API to this and it pulls the creation date which is the same as I need to change the Table trucks in annualloadbook app column[Last Inspection]

{
"Action": "Edit",
"Properties": {
"Locale": "en-US"
},
"Rows": [
{
"id_truck": "<<[ID]>>",
"Last Inspection": "<<[Created Date]>>"
}
]
}

It is still sending the API request from host app in email account jacknrene

But is receiving the request in destination app annualloadbook email

When the equipment belongs to jacknrene on both apps?

Please look at the audit logs below

[Jack_Bates]

Host log for current APII do see something wrong. I reran the API by doing another inspection in host app I’m signed in both app using the same email.

The API was a success and it does now include both the [id_truck] Key column and the edit column [Last Inspection]

What I am noticing and i’m not sure but the host app sends API under the jacknrene email

The destination app receives under the annualloadbook email

Look at the image for the latest API callAPI SUCCESSWrong email

[Jack_Bates]

It looks like its sending under one email account and receiving under the owner email account

[Aurelien]

I'm afraid I don't see how to help you further.

You may want to consider contacting Support Team here: Contact Us - AppSheet Help

[Jack_Bates]

I posted the audit log for the destination app

it is show only the id_truck key column not the [Last Inspection] date

[scott192 · Accepted solution]

This is a long one!

From all the screenshots and auditing logs you've provided I've been trying to work out the structure of your apps and how they pass data around between them.

• I have guessed that you have a table of Equipment, EquipmentInspections and a table of EquipmentServices
• For each Item of Equipment, there will be MANY EquipmentInspections and MANY EquipmentServices
• Each EquipmentInspection and EquipmentService record is automatically added once the Inspection/Service Interval has elapsed and the correct Inspector is informed to do the task.
• The responsible person is using a different APP, possibly on a mobile device, to perform the inspection/service.
• That person marks the inspection/service as completed...whereupon a BOT tries to inform the PARENT Equipment that a NEW Inspection/Service has been completed and it should use the ID of this new record in the [Last Inspection]/[Last Service] column.
• The BOT informs the PARENT Equipment record using the Appsheet API thus simplifying the Inspector's App
• The correct JSON Body has been provided to do a record UPDATE (Edit) and the KEY of the Parent being UPDATED and the KEY of the Inspection/Service record to be stored in the PARENT record is passed correctly.
• The RECEIVING APP's audit history suggests that an API request was received, but the KEY of the inspection/Service record is missing.  Consequently, no update of the parent record's [Last Inspection] column is made.

Now you have said that it confused you about the email addresses of the api request being different for the originating app and the receiving app, but I don't think that matters as all BOTS run as the APP OWNER.  Yes, the data that is available to a BOT can depend on the user who is logged on at the time, but there is a toggle switch on every bot that allows the bot to ignore any security filters etc when running.

Also, your API request to the MAIN app would not care about the USER who sent the request.  It's job is purely to perform the record update and then to execute any bots in your main app that are triggered by that record update (if any).  The only way to make those bots DEPEND on a specific user is if you also sent the userid in your record update and we can see from your JSON that you are not doing this.

So...what the hell is going on?  Everything looks normal apart from the spectre of another TABLE called Trucks?  How does this table fit into the mix?  Are you sure that the record update is being sent to the correct table?  A screenshot of your bots TASK settings would be helpful here.  In fact screenshots of your data and sheets would be much improved if you HIDE all the columns we don't need to see and show only the record KEY and the Columns that are important.  Also your screenshots don't give the TABLE names?  What is DATA supposed to represent...is this maybe just something from mobile Sheets...would be better to give us shots from web sheets 😁

Hope you can see there is a determination to help you fix this problem from the community 😃

[Jack_Bates]

Thank you for the information much appreciated.

I will post everything you said here

[Jack_Bates]

Thank you Scott I reread what you wrote a couple of times and it dawned on me what the issue was. 

The Key column values were not the same in the Inspection app table equipment [ID]

And the annualloadbook app Table Trucks [id_truck]

So i changed the Key columns so both values match.

The Bot Is working and changing the Dates of last inspection so the match. Now I can make the rest of the data work also.

I will now make a Bot hook loop to make a new row when equipment is added in the annualloadbook app. The data always match in the inspection app

[scott192]

So you have a working workflow now? 😀

[Jack_Bates]

1. Yes sir and thank you for the rant it helped after I read it a few time I realized what was wrong.
2. This was the first time I had ever done an API hook flow, I now know some of what to look for. I would like to learn more on how to make more complicated work flows.

[Jack_Bates]

Thank you everyone for the help on making my first API work flow a success. Much appreciated😀